0

I want to transform the SQL statement

SELECT * FROM (SELECT * FROM `votes` ORDER BY `date` DESC) AS sub GROUP BY `username`

into JPQL. The idea is to get the latest vote (ORDER BY date DESC) for each user (GROUP BY username).

I understand that JPQL does not support subqueries in FROM, so I tried "IN"

# does not work!
SELECT v FROM Vote v WHERE v.id IN (SELECT tmp.id FROM Vote tmp ORDER BY date DESC) GROUP BY v.username

but that gives me the first entries. Then I tried "MAX"

# does not work!
SELECT v FROM Vote v WHERE v.date IN (SELECT MAX(tmp.date) FROM Vote tmp GROUP BY tmp.username)

as well, but this gives me multiple entries of the same user as date is not unique...

EDIT:

I thought of creating a VIEW from the SQL syntax as a workaround, but as the JPQL query refers to the class (not the table) this didn't get me far ;-)

EDIT:

Then I thought of JOINs and that lead me to Native Queries. Turns out I can use the above SQL with nativeQuery = true:

@Query(value = "SELECT * FROM (SELECT * FROM `votes` ORDER BY `date` DESC) AS sub GROUP BY `username`", nativeQuery = true)

And that works!

Community
  • 1
  • 1
crusy
  • 1,424
  • 2
  • 25
  • 54
  • JPQL is translated into SQL. So what is the SQL for your JPQL? the log of whatever JPA implementation you use would tell you. If something is not unique, then adding UNIQUE may help ... – Neil Stockton Nov 19 '14 at 10:38
  • The SQL for my JPQL is the first code block in my question. – I do not want to add UNIQUE to date, as date is DATETIME, not TIMESTAMP – crusy Nov 19 '14 at 10:43
  • Ah, PS: Even TIMESTAMP is not unique, see http://stackoverflow.com/a/10321407/3890673 – crusy Nov 19 '14 at 10:45
  • I'm not talking about what SQL you *want*, I'm talking about what SQL was executed for that JPQL. So then you know if your JPQL is consistent to what your aim was – Neil Stockton Nov 19 '14 at 10:46
  • Ah! Sorry and thanks. I enabled logging by `spring.jpa.show-sql: true` and the response is `Hibernate: select ... from votes vote0_ where vote0_.id in (select vote1_.id from votes vote1_ order by vote1_.date DESC) group by vote0_.username` - that's pretty much a longer version of what I wrote... question is: What do I have to do _instead_ to get the latest entries – crusy Nov 19 '14 at 11:07

1 Answers1

0

See final update: You can use the initial SQL statement if you use nativeQuery = true - of course the code will not be portable then, as native queries are bound to the (current) database type

Thanks!

crusy
  • 1,424
  • 2
  • 25
  • 54