Will the following be possible with Projections?

VampBoy · **Posted:** Tue Feb 22, 2005 2:59 pm

In pure SQL I can write:

select max(addr_id) from address group by type

However, if I try to build this up with projections via.

Code:

DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Location.class);
        detachedCriteria.setProjection(Projections.projectionList().add(Projections.max(addrId)).add(Projections.groupProperty(type)));

I end up with something like the following:

select max(this_.ADDR_ID) as y0_, this_.TYPE as y1_ from ADDRESS this_ group by this_.TYPE

Which includes the TYPE in select. I'm thinking since I end up with 2 values in my tuple then I wouldn't be able to use this snippet in a subquery.

Is there a way to accomplish this or am I out to lunch?

astubbs · **Posted:** Wed Oct 17, 2007 8:18 am

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:

Code:

Projections.groupProperty("id.variableId",true)

Javadoc:

Quote:

@param excludeFromSelect
indicates not to include this parameter in the select clause,
and _only_ in the GROUP BY clause.

VampBoy · **Posted:** Thu Oct 18, 2007 9:28 pm

Wow, blast from the past. I can't remember what I wanted this for, must not have been that important :D

astubbs · **Posted:** Fri Oct 19, 2007 8:01 pm

VampBoy wrote:

Wow, blast from the past. I can't remember what I wanted this for, must not have been that important :D

Haha, yeah - didn't notice the date on this post - 2004!

I was searching the forum for my problem and found a number of posts about it, so i replied to each one with that post.

I've hit the problem because I'm using a subquery which needs to return only one column (it's an = condition), but the column returned needs to be the 'id' sort of thing, but the aggregation column is actually the 'date'. Soo - I needed the 'date' column to not be in the select clause - otherwise of course Oracle complains "too many values".

Btw - out of curiosity - do you still use Hibernate? - I'm just about to have a look at your post history ;)

astubbs · **Posted:** Fri Oct 19, 2007 8:06 pm

oh yeah - don't forget to rate! ;)