CODEWITHSUNDEEP
hibernatejpajpa-2.0jpql
Ivan Sopov
Ivan Sopov

Reputation: 2370

Use only subset of JPQL select columns for objects

I have entities Team, Game and TeamInGame. To select teams ordered by their last 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 query does not 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();

Upvotes: 3

Views: 1392

Answers (3)

sharakan
sharakan

Reputation: 6911

Short answer is no. You can't order by in JPQL without having the sort key be defined in the select clause, and you can't convince the TypedQuery to not return the elements in the select clause, because that's what a query does!

Personally, I think the Transform approach you've got is the cleanest, code-wise. Next best would be something like what @SJuan76 suggested using JPQL Constructor Expressions. Perhaps you could even do it with a copy constructor on Team itself, so that you don't have to create an intermediate TeamResult class.

Upvotes: 2

user1972796
user1972796

Reputation: 384

"This query doesn't work" - Are you getting an exception? Also, is it possible for you to add an attribute for maxDate in your Team entity? Not sure if this works in JPQL, but you may try the following:

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 a

Upvotes: 0

SJuan76
SJuan76

Reputation: 24895

You can create your own object that holds the data and populate it from JPQL. For example:

  ...("select new myPackage.TeamResult(tg.team, max(tg.game.gameDate)) from ..."

TeamResult has to both:

a) have a constructor that matches the parameters.

b) be fully qualified.

Upvotes: 1

Related Questions