Master-detail query problem.

Say you have master-detail (users - cars) domain, and you want to get users filtered by some criteria specific for users and some criteria specific to cars. Cars is the collection that is stored in user instance (thanks to appropriate mapping). However you desire not only that users will be filtered, but also every cars collection (inside every single user) should be filtered/narrowed (according to cars criteria). Tricky, huh?

I achieved that by getting results via HQL using standard 'join':
from User user join user.cars car where...
Query results in a list of arrays of Objects. Each array holds a user and a car.
The ORM fails totally. I have to iterate through list of arrays and build a list of user objects which hold a cars collections (I have to overwrite this collection in iteration process).

Is it possible to get filtered users (with filtered cars inside every one) from single query execution?

According to documentation/my tests NOT A CHANCE

Do you have any ideas?

Read the manual about fetch keyword in HQL Queries and use a "select" clause ;)

Thanks(!) Max for a quick answer... The issue is trickier, though:

I already have tried using join fetch before:
select user from User user join fetch user.cars car where...

But it gives im even worse results. Now I have too many records. Say I have 3 users in DB and 2 cars for every one. Above-mentioned query will return 6 users. That is correct if you get closer to the generated sql, but I expect hibernate to be smarter and result in returning 3 user objects, everyone with properly filtered cars collection.

The "select" clause you are mentioning makes complete no difference. Similarly, setting distinct is useless since the generated query is somewhat like:
select distinct user.field1, user.field2, ... , car.field1, car.field2, ... from ...
Since we have different cars, we have many duplicated users (obvious join).

Can you advise something? I think I am more than just familiarized with Hibernate and this problem is just seemingly easy.

Thanks again!

I think that might be problem with the implementation of equals and hashcode.

If your key doesn't implement those to methods correctly, Hibernate will consider "same" keys as different. If you have a composite key and you don't have a special class for your key, then your object itself is the key.

The user key is an int

BTW, multiple users object references point the same object and hashcode/equals is not overriden. The reason of multiplication of users lies in the way of getting results by HQL.

The problem is that Hibernate does not support the feasibility I put in first post.

Maybe one of hibernate gurus can help (Max, any volunteers?)

Thanks

distinctResults = new HashSet( q.list() );
I'm pretty sure a forum search would have given the same answer.

Its also in the FAQ.
http://www.hibernate.org/117.html#A11

This is not about distinct.

I think the most ellegant way to get distinct entities is using ResultTransformer - DISTINCT_ROOT_ENTITY.

See http://www.hibernate.org/hib_docs/v3/re ... sociations
chapter 16.4, example with returnMaps() - the deprecated apperance of ResultTransformer.

The problem is that I need something like a combination of two ResultTransformers:

1. DISTINCT_ROOT_ENTITY - distinct root entities
2. ALIAS_TO_ENTITY_MAP - childs should be also filtered

But I wish to have "nice" results, not list of maps, that I still have to
iterate in order to get list of root entities with properly filled (and filtered) childs.

Can hibernate provide such feasibility (maybe there is other path, not using ResultTransformers)???

Thanks all very much for participation in the post.