Hi,

I read some place that hibernate filters are not supported for single relationships.

Im retrieving a large object graph, which is displayed in its completeness and actually has self referential relationships (i.e A person in Person is related to another person in the same Person table etc)

Since it is effectively recursive and arbitrarily deep, getting the graph in one hit using hibernate relationships is much better than recursively descending the tree myself getting child nodes, and child->child nodes etc.

This all works fine and hibernate appears to be able to detect potential loops in this too. So no problem there (I assume loops are avoided using the first level cache).

I now need to retrieve the graph (which has more than one table relation BTW, not just Person), but filtered on an effective date column which appears in all tables. (or nearly all tables).

Apart from the fact applying filters can cause second level cache issues for some reason, I now find I cant apply a filter to a many-to-one realtionship. I AM setting the filter on the child class itself of a many-to-one but it doesnt even appear in the generated SQL and I cant set a filter on the many-to-one association mapping.

So what if any, (assuming filters cannot be used in this case) would be the usual solution to such a problem that doesnt involve removing the hibernate mapping for the association and generating the HQL myself to retrieve only effective rows and setting them in the parent manually.

Hi,

Been thinking about this some more and need to provide more details to this specific issue.

I decided I understand why filters dont make any sense for one-to-one or many-to-one associations. Since in a 1:1 association a filter has nothing to do - there is only a single child, unless the child is filtered out completely. However....

In my case there is a many-to-many table between the Parent-Child tables. I actually map the many-to-many table to a class because I do need some attributes from it that form part of the association (sortOrderNumber is one, specific Parent->Child associations have different ordering for example)

So I end up with:

PARENT_TABLE -> one-to-many -> MANYTOMANY_TABLE -> man-to-one -> CHILD_TABLE

Problem is that the Set of records in the MANYTOMANY_TABLE that I need are valid based on an EFFECTIVE_DATE attribute on the CHILD_TABLE.

i.e the collection inside the Parent class (which contains a collection of objects of type Reference - my many-to-many table mapping) should be filtered based on the date property in the Child class, but the collection mapping is to the MANYYOMANY table mapped to Reference class.

Parent parent = session.get(..)
Set<Reference> referenceSet = parent.getReferences();
Reference reference = referenceSet.get(0); // would iterate through
Child child = reference.getChild();

Im thinking of doing the date filtering as a post processing task in code in the DAO after calling hibernate, then injecting ehcache (third level cache)on the call to the DAO using spring, but doing it that way kina sucks.

This is a real world problem that I think hibernate struggle's to support. Also after using filters for a while, I found adding a filter to the Set collection mapping in the parent screws the 2LC..

<deleted>

Well, that's a very special case you got there...
Is it not possible to move EFFECTIVE_DATE to your join-table?

If not, have you tried to use some more complex filter-expressions, like:
"(select ct.effectiveDate from child_table ct where ct.id = child_id) = :someCondition"

These complex filters will work, even though the statements produced are not very nice.