JPQL Select the Latest record date for each User and Correct Use of LEFT OUTER JOIN

Removed

Using JPQL to write a complex query, I was checking for the latest record added for each user.
In fact, let's say that the user borrow books form library and we want to know the latest book taken for each user.

My SQL request should be like this :

select user_id, user_name, mobile_phone, g.group_name,  borrowing_date
from books_record b 
inner join user u on b.user_id = u.user_id
left outer join group g on b.group_id = g.group_id
where (user_id, borrowing_time) in (
    select br.user_id, max(br.borrowing_time)
    from books_record br
    group by br.user_id
)

I am using a LEFT OUTER JOIN as reference on group can be NULL.

Using criteria builder, I wasn't able to generate this query, as I didn't now how to put two columns for the IN.

query.where(builder.in(root.get("user").get("userId"), root.get("borrowingTime")).value(subQuery));

So I have decided to use JPQL, where I construct a new bean in the result by giving my columns.


So the JPQL query shoul look like this (I declared it as a NamedQuery) :

  SELECT new org.project.test.MyPerson(b.user.personId, b.user.userName, b.user.mobilePhone, g.groupName)

  FROM BooksRecord b 

  LEFT OUTER JOIN b.group g

  WHERE ( b.user.userId,  b.borrowingTime)

  IN (

SELECT mxb.user.userId, MAX(mxb.borrowingTime)

FROM BooksRecord mxb

GROUP BY mxb.user.userId

)