Hibernate version: Hibernate 3.2.6ga
Name and version of the database you are using: MySQL

Hi,
I have the following relationships:

So, an Object would be something like this:

* There are clients that have entities, and there are some clients that don't have any entity.
* Some clients that have entities have a user assigned to it, and there're some entities that don't have assigned a user.

I need to request an specific client. Also I need to fetch their entities that have assigned a user with an specific role. For example:

Request Client #22, and all entities with users assigned to ROLE1.

Case A) If the client requested doesn't have any entities with the requested role, then it must return a Client object with an empty collection of entities.
Case B) If the client requested does have entities (some may have assigned a user with the requested role and some others with a role different from the requested), then it must return a Clien object with the collection of entities that have the role requested.


The Query I have right now is the following:



It works well for case B... For case A, it returns a null client object.

I also tried filtering the collection in User mapping file:

This filter returns all entities but don't return Roles that are different from the requested rol. So, it doesn't work for me.

Does anyone have any idea about how to filter a collection given this scenario?
Thanks in advance :)

This happens because the condition on the role is part of the where clause. As you said... in case A the client doesn't have any user with the specified role, so the where condition doesn't match any row. To make it match you have to move the condition on the role to make it part of the left join. Try something like:

I already tried what you propose:

but i get the following exception:

So I modify my query as follows (separate e.user.roles into two joins):

... and it worked for case A (now, it returns clients that doesn't have any enitities yeah!), but it doesn't work for case B (now, it returns all entities even those with a role different from "ROLE1" :S) I don't know why!

A work around I've come up is:


This works for me, but I think there should be a better way to do this, with only one query... I feel my solution a little dirty.
Any suggestions?

I guess I misunderstood what you wanted. The problem here is that you need to left join two collections (first Client.entities, and then User.roles) and you want to put a restriction on the first collection based on what is in the second. Hmm... it may be hard to do with just joins... I can't come up with something right now...

But the second query from the working example in your last post can probably be used as a subquery in the first query. Something like:



I am not 100% sure the above will work. It is kind of pushing the limit of what I can come up with without actually trying it out.

Another possibility is maybe to use the collection filter feature. It would be rather similar to the example in your last post, but it may feel a bit "cleaner". See http://www.hibernate.org/hib_docs/v3/re ... -filtering for more information about collection filters.