JPQL get most recent rows

Question

Let's say I have the following tables

my_profile_data
-------------
integer: my_profile_data_id
integer: profile_id
integer: profile_data_type_id
date: date_changed
string: value

my_profile
-------------
integer: profile_id
string: name

profile_data_type
-------------
integer: profile_data_type_id
string: name

I want to get the most recent profile information for each profile data type. In plain SQL this would look something like:

select mpd.profile_id, mpd.profile_data_type_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_data_type_id

I've tried different variants of the following JPQL query, but can't 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.profileDataTypeId) 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

Answer 1

Assuming DATE is actually a timestamp that you're not worried about colliding, it seems like your query could be as simple as

select mpd 
from MyProfileData mpd
where mpd.profile.name = :name
and mpd.date = (select max(mpd1.date) from MyProfileData mpd1 where mpd1.profile.name = :name)

Are you using a DBMS like an older MySQL that hates subselects?

I am also thinking that perhaps your problem is you haven't mapped the object relationship from MyProfileData to ProfileData, and all you have is the actual integer value of the field. This will make writing JPQL queries quite hard in general.

Edit:

Continuing on the assumption that the dates don't collide for any given profile + profile data type combo, (so date uniquely identifies a row within the subset of a particular profile + profile type combination) you can just grab all the dates:

    select mpd from MyProfileData
    where mpd.profile.name = :name
    and mpd.date in (select max(mpd1.date) 
                     from MyProfileData mpd1 
                     where mpd1.profile = mpd.profile group by mpd.profileDataType)

Your original SQL example isn't actually legal, so it's tough to come up with a way to reproduce what it looks like it's trying to do without having a way to uniquely identify the rows while excluding the value.

Answer 2

I gave up on trying to create this query in JPA and wrote a native query instead