Need Help Converting a SQL Query to JPQL

So far, Spring’s JPA repositories have sufficed for me, but now I need a custom query. I have struggled with this for a day trying to learn more about SQL, joins, etc and came up with a native MySQL query which (I think) does what I want: I want to find all the latest of each “product” with a unique url. Example:

PRODUCT
id | url                       | created_date
---------------------------------------------
 1 | http://foo.bar/products/a | 2020-04-01
 2 | http://foo.bar/products/a | 2020-04-02
 3 | http://foo.bar/products/b | 2020-04-02
 4 | http://foo.bar/products/a | 2020-04-03
 5 | http://foo.bar/products/b | 2020-04-03

RESULT
id | url                       | created_date
---------------------------------------------
 4 | http://foo.bar/products/a | 2020-04-03
 5 | http://foo.bar/products/b | 2020-04-03

This is the native MySQL query I came up with:

SELECT *
FROM product t
INNER JOIN
    (SELECT url, MAX(created_date) AS Latest
    FROM product
    GROUP BY url) grouped_url 
ON t.url = grouped_url.url 
AND t.created_date = grouped_url.Latest

My attempts to transform this to JPQL in order to use them an @Query annotation seem rather embarrassing so I won’t even bother posting them here 😀 Can someone help me?

Answer

Convert your SQL query to the form:

SELECT *
FROM product p1
WHERE NOT EXISTS ( SELECT 1
                   FROM product p2
                   WHERE p1.url = p2.url
                     AND p1.created_date < p2.created_date )

It is JPQL-compatible – see Subqueries – EXISTS Expressions.

Or you may use the same idea in >= ALL (..) form.

Advertisement

Answer