JPQL get most recent rows
- by Vinnie
Let's say I have the following tables
my_profile_data
-------------
integer: profile_id
date: date_changed
string: value
my_profile
-------------
integer: profile_id
string: name
I want to get the most recent profile information. In plain SQL this would look something like:
select mpd.profile_id, mpd.value, max(mpd.date_changed)
from my_profile_data mpd, my_profile mp
where mpd.profile_id = mp.profile_id and mp.name='The Profile I Want'
group by mpd.profile_id
I've tried different variants of the following JPQL query, but cant get it to work.
SELECT mpd FROM MyProfileData mpd LEFT JOIN
(SELECT mpd.profileId profileId, MAX(mpd.dateChanged) FROM MyProfileData mpd
LEFT JOIN mp.profile
WHERE mp.name = :name
GROUP BY mpd.profileId) recent
ON (rp.profileid = recent.profileId)
Is this query doable in JPA?
I'm using EclipseLink as my JPA provider.
The innermost exception I get when I try to run this is
Caused by: NoViableAltException(81!=[506:7: (n= joinAssociationPathExpression ( AS )? i= IDENT | t= FETCH n= joinAssociationPathExpression )])
at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.join(JPQLParser.java:3669)
... 73 more
