I’m working on an application where every database table has an isDeleted column, and need to ensure its value is heeded.
For operations like EntityService.get / EntityService.countWhere / etc it’s all fine - I’ve done a CFC which extends coldbox.system.orm.hibernate.VirtualEntityService and changes behaviour as appropriate, and that part all works fine.
However, when doing Entity.getProperty() it doesn’t go via the VES, so it doesn’t remove rows marked as deleted.
Trying to find a good way to solve this - in terms of keeping acceptable performance and tidy code - the wall of text below describes what I’ve tried / where I’m at - hoping someone might have solved this issue already and have a better solution?
I attempted to use the OrmPostLoad interceptors, but whilst these are fired on the getProperty calls, they don’t appear to allow the results to be changed - i.e. it seems I can’t do if InterceptData.Entity.isDeleted() return null, presumably because in this case the interceptor is only a notification of the event.
Also tried using the ORM Event Handler postLoad itself, but again that doesn’t appear to allow modification.
I found that there is a WHERE attribute for cfproperty, and adding where=“isDeleted=0” appears to work for some relationships - one-to-many arrays return the expected data.
For many-to-one (and probably one-to-one) the clause seems to be ignored, most likely due to an annoying design of Hibernate not to allow filters to change the -one side to a -zero-or-one, (even when the field is nullable). [HHH-4026] one-to-one non-lazy loading but Filters Not Applied. - Hibernate JIRA
I guess this one is solvable by updating existing isNull(Entity.getProperty()) checks to include an OR Entity.getProperty().isDeleted() which is a bit annoying but not the end of the world.
For many-to-many there are also problems - the isDeleted of the related column cannot be referred to, because the generated SQL is looking in the join table for it, not the related table. Using the entity names doesn’t work - Hibernate complains - and it is necessary to use the generated aliases.
When doing getProperty, the aliases appear to always be the first ten characters of the entity name suffixed with 1_, so using where=“entity1_.isdeleted=0” works for that… until elsewhere in the code a HQL query is used, resulting in the aliases being generated differently, and it fails again.
The HQL calls can probably be switched to standard SQL and do the filtering itself, but obviously relying on Hibernate’s alias name generation is still flaky, and I’ve no idea if there are other situations where it will change (or if there are/will be entities where the first ten characters are not unique).
So yeah, open to any suggestions on how this might be done better?