subquery 2 levels

kmike · **Posted:** Wed Aug 29, 2007 7:01 am

Hi!
This is probably a trivial question...

I have a Ticket object which hold Price objects (one-to-Many), Price objects hold PriceLevel objects (one-to-Many).

Trying to figure out how to select all tickets with a specific PriceLevel.pk.

Selecting prices from ticket is no problem.
crit.createAlias("prices", "pricesAlias").
setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).
add( Restrictions.between("pricesAlias.price", fromPrice, toPrice));

But have struggled with the priceLevels for quite some time.
Would like to do something like this....

crit.createAlias("prices", "pricesAlias").
setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).
add( Restrictions.between("pricesAlias.price", fromPrice, toPrice));
crit.createAlias("priceAlias", "priceLevelAlias").
setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).
add( Restrictions.eq("priceLevelAlias.id", pk);

Any pointers?

xerces8 · **Joined:** Thu Oct 19, 2006 12:07 pm **Posts:** 75

crit.createAlias("prices", "pricesAlias").
setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).
add( Restrictions.between("pricesAlias.price", fromPrice, toPrice));
crit.createAlias("priceAlias.priceLevels", "priceLevelAlias").
setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY). // this line is superfluous
add( Restrictions.eq("priceLevelAlias.id", pk);

kmike · **Posted:** Wed Aug 29, 2007 7:41 am

Nope that won't work, that suggests that there would be a property called priceAlias on ticket.

Base my criteria from a Ticket entity.
Criteria crit = getSession().createCriteria(Ticket.class);
....
....

xerces8 · **Joined:** Thu Oct 19, 2006 12:07 pm **Posts:** 75

Are you sure ? Did you try ?

Then maybe this would work :

Code:

crit.createAlias("price.priceLevels", "priceLevelAlias").

(this or the first idea work 99%)

And this works 99,999 % ;-)

Code:

crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).
createCriteria("prices").
add( Restrictions.between("price", fromPrice, toPrice));
crit.createCriteria("priceLevels").
add( Restrictions.eq("id", pk);

xerces8 · **Joined:** Thu Oct 19, 2006 12:07 pm **Posts:** 75

I just tried both possibilities with setAlias() and they both work (with hibernate version 3.2.0.cr2).

kmike · **Posted:** Thu Aug 30, 2007 9:24 am

sweet, thanks for leading me in the right direction.

This worked !!

Criteria crit = getSession().createCriteria(Ticket.class);
crit.createAlias("prices", "pricesAlias").
setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).
add( Restrictions.gt("pricesAlias.price", 0f));
crit.createAlias("pricesAlias.priceLevels", "priceLevelsAlias").
add( Restrictions.eq("priceLevelsAlias.id", searchCriteria.getPriceLevel().getId()));

xerces8 · **Joined:** Thu Oct 19, 2006 12:07 pm **Posts:** 75

Don't forget to rate my post ;-)