-
1. Re: TableJoin with aggredate MAX
trunikov.dmitry.trunikov.zoral.com.ua Sep 15, 2009 10:08 AM (in response to mechtatel)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, chapter7.4. The select clause
). -
2. Re: TableJoin with aggredate MAX
mechtatel Sep 15, 2009 10:06 PM (in response to 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.
-
3. Re: TableJoin with aggredate MAX
trunikov.dmitry.trunikov.zoral.com.ua Sep 16, 2009 9:00 AM (in response to mechtatel)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();
-
4. Re: TableJoin with aggredate MAX
mechtatel Sep 23, 2009 5:20 AM (in response to 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?