Use only subset of JPQL select columns for objects
I have Team, Game and TeamInGame teams. To select the teams ordered from the latest games, I use:
Query query = em.createQuery("select tg.team, max(tg.game.gameDate) as maxDate" +
" from TeamInGame tg" +
" group by tg.team" +
" order by maxDate desc");
return Lists.transform(query.getResultList(), new Function<Object[], Team>() {
@Override
public Team apply(Object[] input) {
return (Team) input[0];
}
});
Is there a way to get rid of this transformation? This request doesn't work:
return em.createQuery("select tg.team, max(tg.game.gameDate) as maxDate" +
" from TeamInGame tg" +
" group by tg.team" +
" order by maxDate desc", Team.class).getResultList();
source to share
The short answer is no. You can't order by
in JPQL without specifying the sort key in the clause select
, and you can't convince TypedQuery
not to return items in the clause select
, because that's what the query does!
Personally, I think the Transform approach you have is the cleanest, code-behind. The next best would be something like @ SJuan76 suggested using JPQL Constructor Expressions . Perhaps you can even do it with a copy-to constructor Team
, so you don't need to create an intermediate class TeamResult
.
source to share
"This request is not working" - Are you getting an exception? Also, is it possible to add a maxdate attribute to my Team object? Not sure if this works in JPQL, but you can try this:
select a.teamid from (select teamid, max (g.gamedate) as maxdate from teamingame tg, game g where tg.gameId = g.pk group by teamid order by maxdate desc) as
source to share