SoftDelete: wrong condition in query on related table fetch

Forum for posting problems using QxOrm library

SoftDelete: wrong condition in query on related table fetch

Postby gianks » Sat Sep 03, 2016 8:07 am

HI all,
i think i have probably found a bug related to softdelete.
I've just enabled such functionality but after a softdelete operation, restarting the application i've found a strange unexpected error from the DB since a simple fetch by ID fails with the following:

[QxOrm] 'qx::dao' internal error <fetch by id with relation>: ...
[QxOrm] sql query returns no data

I've found the problem which is related in the position of the conditions. After the softdelete enable the "IS NOT NULL OR deleted_at='' " conditions i've found after the where is the cause of the trouble.
The check has to be moved, in this case, just after the LEFT OUTER JOIN xxx ON ... AND whatever_creates=the_join.
Setting in the where clause causes the entire row to be dropped in the end of the JOIN ops which we don't want here.

I've tested it successfully this way.
Let me know.
Regards
gianks
 
Posts: 45
Joined: Mon Jul 04, 2016 1:04 pm

Re: SoftDelete: wrong condition in query on related table fe

Postby qxorm » Mon Sep 05, 2016 7:07 am

Hello,

Yes, it seems that this is a bug.
Could you please :
1- give the actual SQL query generated by QxOrm library ;
2- give a fixed SQL query where you put the condition at the right place.
qxorm
Site Admin
 
Posts: 481
Joined: Mon Apr 12, 2010 7:45 am

Re: SoftDelete: wrong condition in query on related table fe

Postby qxorm » Mon Sep 05, 2016 2:53 pm

FYI, here is a quick test I did with the qxBlog sample project : for the 4 persistent classes of this project (author, blog, comment and category), I enabled the soft delete behavior adding this line of code in the QxOrm registration :
Code: Select all
t.setSoftDelete(qx::QxSoftDelete("deleted_at"));


Then, I just executed the project : there is no error or assertion ==> so it seems ok for me.

There are SQL queries with relations, like this :
Code: Select all
SELECT blog.blog_id AS blog_blog_id_0, blog.blog_text AS blog_blog_text_0, blog.date_creation AS blog_date_creation_0, blog.deleted_at, blog.author_id AS blog_author_id_0,
author_1.author_id AS author_1_author_id_0, author_1.name AS author_1_name_0, author_1.birthdate AS author_1_birthdate_0, author_1.sex AS author_1_sex_0, author_1.deleted_at,
comment_2.comment_id AS comment_2_comment_id_0, comment_2.blog_id AS comment_2_blog_id_0, comment_2.comment_text AS comment_2_comment_text_0, comment_2.date_creation AS comment_2_date_creation_0, comment_2.blog_id AS comment_2_blog_id_0_2, comment_2.deleted_at,
category_3.category_id AS category_3_category_id_0, category_3.name AS category_3_name_0, category_3.description AS category_3_description_0, category_3.deleted_at
FROM blog
LEFT OUTER JOIN author author_1 ON author_1.author_id = blog.author_id
LEFT OUTER JOIN comment comment_2 ON comment_2.blog_id = blog.blog_id
LEFT OUTER JOIN category_blog category_blog_3 ON blog.blog_id = category_blog_3.blog_id
LEFT OUTER JOIN category category_3 ON category_blog_3.category_id = category_3.category_id
WHERE (blog.deleted_at IS NULL OR blog.deleted_at = '')
AND (author_1.deleted_at IS NULL OR author_1.deleted_at = '')
AND (comment_2.deleted_at IS NULL OR comment_2.deleted_at = '')
AND (category_3.deleted_at IS NULL OR category_3.deleted_at = '')
AND blog.blog_id = :blog_id
qxorm
Site Admin
 
Posts: 481
Joined: Mon Apr 12, 2010 7:45 am


Return to QxOrm - Help

Who is online

Users browsing this forum: No registered users and 1 guest

cron