Hibernate version:
3.2

Hi! I'm trying to query out some root object, using restrictions based on a subquery. I want to get some items (PAH), which have a 1-N relationship with some other items (PA) that have a date associated with them. I want to get the latest PA's, then their parent PAHs.

The SQL query looks like this:

select distinct pah.*
from pheno_attribute_history pah, pheno_attribute pa,
(select max(pa.date_effective) as max_date, pa.attribute_id as id
from pheno_attribute pa
group by pa.attribute_id) latestPAs
where pah.attribute_history_id = pa.attribute_history_id
and pa.attribute_id = latestPAs.id

One approach that almost works is:

Criteria latestPAQuery = hsession.createCriteria(PhenoAttribute.class)
.setProjection(Projections.projectionList()
.add(Projections.max("dateEffective"))
.add(Projections.groupProperty("attributeId")));

List o = latestPAQuery.list();


final Criteria criteria = hsession
.createCriteria(PhenoAttributeHistory.class);

criteria.createAlias("phenoAttributes", "pa")
.add(Restrictions.in("pa.attributeId", o));

The only problem here is that the "latestPAQuery" really returns 2 objects - I could probably use ResultTransformer to get to just the field I want out of the projection.

However, this means I could be populating an item list with 1000's of items (and Oracle doesn't like >1000 items when using an 'in' clause.)

Another suggestion based on reading on the forum would be a Detached Criteria query to fetch PA, then use Subqueries to join that to PAH:

DetachedCriteria latestPAs = DetachedCriteria.forClass(PhenoAttribute.class, "pa")
.setProjection(Projections.projectionList()
.add(Projections.max("dateEffective"))
.add(Projections.groupProperty("historyId")));

Criteria latestPAHs =
hsession.createCriteria(PhenoAttributeHistory.class, "pah")
.add(Property.forName("attributeHistoryId").eq(latestPAs));

The generated SQL is *close*....
(snipped to avoid the huge select/from clause, because this object is tuned with a lot of fetch style joins already)

where this_.attribute_history_id = (select max(this0__.date_effective) as y0_, this0__.attribute_history_id as y1_ from pheno_attribute this0__ group by this0__.attribute_history_id)

If I could wrap that inner select with one more select to just project out the y1_ field, I'd be set, I think.

Is there any way to limit the projectionList to return just one element without a ResultTransformer?

Am I doing these both horribly wrong, and missing some easier way to run this query?

Thanks in advance!

Even if someone could tell me how to access a single field out of a projectionList to do an .equals() operation against another criteria query, that would be a huge help.

Anyone? I would think this would be a pretty easy thing to do...

Have a look guys if you're still interested. I've raised an issue and submitted a patch against trunk to address this.

Criteria api does not allow a groupProperty to _NOT_ be included in the selected columns
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2893

Basically it allows you to specify a groupProperty like this:

Javadoc: