Hi,
I am using hibernate 3.1.3 with Oracle version 9.2


This is my basic query...

Criteria criteria = session.createCriteria(className).add(Example.create(obj).excludeZeroes());
List ret = new ArrayList();
ret.addAll(criteria.list());

...where 'obj' is a class with mappings

this works, but if I have multiple versions of 'obj' stored as new rows in the db table , I get them all back in my response ( I only want the latest version).

I changed my criteria to ...

ProjectionList p = Projections.projectionList();
p.add(Projections.groupProperty(UNIQUE_IDENTIFIER));
p.add(Projections.groupProperty(DCMID));
p.add(Projections.max(VERSION_ID));
Criteria criteria = session.createCriteria(className).add(Example.create(obj).excludeZeroes()).setProjection(p);
List ret = new ArrayList();
ret.addAll(criteria.list());


...but what is returned now is a List of Object[] rather than a list of fully populated instances of the 'obj' class.

i.e. the 'group by' is working, but the 'select *...' is transformed to select uniqueIdentifier,dcmid, max(versionId) ...' by the projection

What I am after is a list of matching 'obj' objects rather than the Projection Object[].

Any help is appreciated.
I dont think I can use HQL as this would require my code to know about the 'obj' fields/ relationships. I want my code to be generic and unaware of the specific fields / object associations of the object being passed in.
thanks

ag

Does adding the appropriate ResultTransformer (DISTINCT_ROOT_ENTITY, probably) fix this?

hi,

tried the suggestion, but it did not fix my problem unfortunately

If I do ...

Criteria criteria = session.createCriteria(className).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).add(Example.create(obj).excludeZeroes()).setProjection(p);

... I get Object[3] arrays

if I do ...

Criteria criteria = session.createCriteria(className).add(Example.create(obj).excludeZeroes()).setProjection(p).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

...I get Integers returned ( max versionId ?)

the sql generated is :-
select this_.uniqueIdentifier as y0_, this_.DCMid as y1_, max(this_.versionId) as y2_ from myorder this_ where (1=1) group by this_.uniqueIdentifier, this_.DCMid

fetch joins with all other tables seem to be ignored in the sql

Thinking further about this, I've realized that projections aren't the way to go. This query requires subselects... at least one, possibly two. I'm still working on the SQL version of this query, but I can't see anything that doesn't require at least N+1 tablescans.. I'm hoping to find a two-subselect option that results in three indexed lookups, which would be much faster than a index + tablescan (assuming that your table is potentially large).

I think I've got it. The SQL query comes out as this shape:I'm using "objid" as the group-by column, because I don't know what your various column names are. If there are two versions of object "obj", they'll both have the same objid, but different versionids (and optionally, rowids). If you stick indices on version and objid columns, this query should be fairly efficient.

Anyway, that SQL did what I expected it to do, and I hope it's what you want. I converted it to criteria, and this is the resulting code:I haven't compiled that code, but once you work out any typos, etc., hopefully it'll do what you need.

Initial result looks very good. Need to test it out fully, but I think you've cracked it. Nice one!

Will send a follow up to confirm that it does work (or not)

ag

that did work.
thanks

Hi,

I would like to define following query as a Criteria:
[code]
from EscalationStateVO where validTo in (select max(state.validTo) from EscalationStateVO state group by state.escalation.id)
[/code]

The problem is, that the subquery returns 2 result rows and it seems to be impossible to pick [code]validTo[/code] only. Here is my code:
[code]
DetachedCriteria dc = DetachedCriteria.forClass(EscalationStateVO.class);
ProjectionList projList = Projections.projectionList();
projList.add(Projections.max("validTo"));
projList.add(Projections.groupProperty("escalation.id[/code][/code]"));
dc.setProjection(projList);

Criteria crit = session.createCriteria(EscalationStateVO.class);
crit.add(Property.forName("validTo").in(dc));
List l = crit.list();
[/code]

This does not function because the generated SQL of the subselect contains [code]escalation.id[/code] also (grouping is needed there).

Do you have any clues on that?

Thanks,

Gabor

I've raised an issue and submitted a patch against trunk to address this. Have a look and see if it meets your needs.

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: