[coldbox 3.7] ORM relationships, filter based on isDeleted property.

Communities ColdBox HMVC
1

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?

2

Ok, so I can solve both the many-to-many Entity.getProperties() and the HQL by using Hibernate filters, but afaict that involves generating and modifying the HBMXML file, which is of course not a great approach.

Someone suggested Envers as a possible solution, but I have a feeling this isn’t something I can work with via CF’s ORM.

I’m going to keep investigating that, but would be good to hear if anyone has had any experience/success with using Envers alongside Coldbox?

3

I strongly believe that Envers could benefit us in CF, as a native option. I did raise an E/R with Adobe a few years ago.

Having said that, I think it would be a good enhancement for ColdBox, coupled with Wirebox I think it could be done with some work.

4

Yeah, would be handy to have it baked in, either to Coldbox or the engine.

For the time being we’re going with regular deletes and just logging serialized entities.

5

Peter, just a quick question is your only problem deleting a record using the soft delete technique?

6

is your only problem deleting a record using the soft delete technique?

Well we’ve changed the approach so there’s no immediate problem.

The issue was dealing with relationships for HQL/getProperty calls.

The actual deletion side was fine - i.e. overriding ObjectService.delete() to instead set the isdeleted column and save.

Similarly dealing with direct retrievals wasn’t an issue - could override .get()/.findWhere()/.count()/etc to deal with the extra column.

But when it came to stuff like PersonService.get(id).getAddresses() where the relationship was a many-to-many join, the only way to get the column adhered to for the relationship was adding a filter in the HBMXML.

Of course, a similar situation might occur for issues other than soft deletes, so a convenient way to filter many-to-many relationships would be good, but is not an issue at present.