SoftDelete: wrong condition in query on related table fetch
Posted: 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
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