TableJoin with aggredate MAX

[mechtatel]

Hi,

I have a Item entity with many columns (all with nullable-false)

How can I get a list with only two columns using an aggregate function MAX and group by the another column?

SELECT i.groupId, MAX(i.price) FROM Item i GROUP BY i.groupId

[trunikov.dmitry.trunikov.zoral.com.ua]

If your SQL is correct you can call

getResultList()

. The result will be a list where each element is array.
If your persistent provider is Hibernate it is possible to return result as list, map or custom class (see reference for Hibernate Entity Manager, chapter 7.4. The select clause).

[mechtatel]

First sorry for the title, I changed the content and forget to change the title. Will be good if can be added edit functionality for member's post.

Thank you for your reply Dmitry.

I'm using the default Entity Manager (configured by seam-gen), so after some reading -- using getDelegate() can be down to the Hibernate session and then implement yours advices or may be Criteria Queries (I'm no sure didn't test if is appropriate fot the case).

But trying your advice I get the next solution:

I create a simple class with two long properties and respective getters and setters. Also add a constructor, receiving two object and casting to Long. So the query change to this:

SELECT new org.somepackage.PlainClass(i.groupId, MAX(i.price)) FROM Item i GROUP BY i.groupId

In this way I don't have to cast to Hibernate session.

[trunikov.dmitry.trunikov.zoral.com.ua]

The default Entity Manager which is configured by seam-gen is Entity Manager based on Hibernate implementation. So you should not use getDelegate method. Just write query as you stated above, it should work:

Query q = entityManager.createQuery("SELECT new org.somepackage.PlainClass(i.groupId, MAX(i.price)) FROM Item i GROUP BY i.groupId");
List<PlainClass> result = q.getResultList();

[mechtatel]

This was working fine at the moment that I decide to join two tables more, is very close approach, the only difference is that in place of price there is location table.

location         route                  groupe
--------         -----                  ------------

id               id                     id
route_id         groupe_id              name
...              ...                    ...

So what I want to acomplish is this represented like normal sql query:

select
        l.route_id,
        MAX(l.time),
        g.name

    from
        drum.location l
    INNER  JOIN route r ON l.route_id = r.id
    INNER  JOIN groupe g ON r.groupe_id = g.id

    where
        r.enabled = 1
        AND g.enabled = 1
        AND l.time between 1253336400000 and 1253629380000
    group by
        l.route_id
    order by
        MAX(l.time) DESC limit 20;

Another way to get the groupe name without usin joins is adding this to the select:

(select rg.name from drum.route_groupe rg where rg.id = (select r.route_groupe_id from drum.route r where r.id = l.rute_id)) as groupename

The field when is retrieved the max value is not part of the location entity.

So like in my initial post I used a plain class to retrieve list from the query, but now what is the way to resolve this with jpa/hibernate?

I tried to use ManyToOne with owner location joining with routes and then another ManyToOne witn route entity owner joining with group, but whe was building the project got:

MappingException: Repeated column in mapping for entity: org.somepackage.entity.Location column: route_id (should be mapped with insert="false" update="false")

Any suggestion?