criteria api, collections and outer joins

andyr@goowy.com · **Joined:** Thu Jul 12, 2007 9:11 am **Posts:** 3

I am using the criteria api to perform an outer join to limit the number of objects returned and its associated collections.

For example I want to return all Dog objects where its collection of puppy objects has a colour of black.

Using the criteria api and performing a left outer join this works fine. However when users come to use my Dog objects (in this case via JPA) when they call getPuppies() they are returned only the puppies where their colour is equal to black, when really I want all puppies in the collection to be returned? I have tried forcing a FetchMode of eager however this proves unsuccessful. Once i have limited the list of Dogs i dont care anymore wether its puppies are black or not?

How can I return all of the puppies collection in my Dog class when limiting by a puppy value of black?

Thanks

Andy,

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

I am not very good with HQL expressions, there might be other ways, but:

Code:

select dog from Dog dog where exists(select dog.puppies pup where pup.color='black')

"exists" evaluates to "true" if at least one row is returned; therefore, this returns you all the Dogs, each with its complete litter, provided that at least one puppy in the corresponding litter is black.

andyr@goowy.com · **Joined:** Thu Jul 12, 2007 9:11 am **Posts:** 3

Thanks Gonzalo,

I will take a look at 'exists' as you suggest. My query is obviously not for Dog objects! but part of a rather complex criteria query. I wonder how I can do this using the criteria api? I will investigate.

Thanks,

Andy.

patio · **Joined:** Wed Apr 18, 2007 8:58 pm **Posts:** 1

Just looking through the (NHibernate) documentation and saw this in section 12.4, might point you in the right direction:

Quote:

Note that the kittens collections held by the Cat instances returned by the previous two queries are not pre-filtered by the criteria! If you wish to retrieve just the kittens that match the criteria, you must use SetResultTransformer(CriteriaUtil.AliasToEntityMap).

so, I'm guessing you shouldn't be using SetResultTransformer(CriteriaUtil.AliasToEntityMap).

pat