Hello,

I have the following situation: I have a parent table (say, OWNER), and a child table (say, CAR). I'd like to select all owners who have red honda civic, and only those children that describe a red honda civic entry.

Here's my Hibernate hierarchy:

Owner { String name; Set<Car> cars; } //Car class has many-to-one link to Owner

Car { String model; String make; String color; }

When I try to do something like this:

Criteria c=session.createCriteria(Owner.class);
c.add(Expression.eq("name","John"));
c.createAlias("cars","crs");
c.add(Expression.eq("crs.model","Civic"));
c.add(Expression.eq("crs.make","Honda"));
c.add(Expression.eq("crs.color","red"));

This query returns Owner objects for owners that have red honda civic cars, but each Owner object contains more than just the red Honda Civic cars.
I'm already using DISTINCT_ROOT_ENTITY result transformer to get unique Owners. Is there something I can turn on to get only those children in cars collection where Car objects match 'red honda civic' criteria?

I was reading about filters, but it looks like those have to be defined in mapping files, and they are pretty much static. I'm looking for more generic approach that would work for Owner/Car tables, and for other ones. Is there some setting in Hibernate that I could turn on to get only those child records that match the criteria?

It seems a bit unnatural that collections of child objects (like Owner.cars in my case) contain objects that don't match the search criteria, when search criteria were specified for the child table (CAR).

Anyway, I'd appreciate any help on the subject.
Thank you,

Bratek

in the log files, does this return the same number of rows as there are number of red honda civics belonging to john?

John has only one red Honda civic, but the collection in Owner object (cars) for the provided criteria returns more that just that car (John has 3 cars total, two of them being not Honda civics, but all three cars are part of the collection).

Bratek

if you want to use filters try below code

Criteria c=session.createCriteria( Owner.class );
c.add( Expression.eq( "name", "John" ) );
List owners = c.list();

for ( int i=0; i<owners.size(); i++ ) {
Car civicCar = session.createFilter( ownerObject.getCars(), " where this.model=? and this.make=? and this.color=? " )
.setParameter( 0, "honda" )
.setParameter( 1, "civic" )
.setParameter( 2, "red" )
.uniqueResult();
}

Thank you for your reply. I agree, I could use filters to get only the child records that match the filters. But that would mean that I would have to create a separate set of filters for each result: If I'd like to match car color only, that would be one filter. Make only, another. Make and color, yet another. And so on.

Of course, I could define all those filters in mapping files and then enable them for each particular call, but that would be still the same thing.

I'm just trying to find out if there is a generic solution for this. Something like "always return only those children that match the provided criteria", no matter what criteria I'm using at the moment.

Bratek

I've always had trouble getting criteria to perform this type of function, and I'm pretty sure alot of other people have as well.

I recommend that filter above, or using a simple hql query.

Or, for the criteria, just search the cars class instead of going through the owner class.

createCriteria(Cars.class)
.add(Expression.eq(<foreignkey>, "John")
.add(Expression.eq("model","Civic"))
.add(Expression.eq("make","Honda"));
.add(Expression.eq("color","red"));