Have Geek Brain

Most recent lines in JPQL

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 type of profile data. In plain SQL, it looks something like this:

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 have tried different variations of the following JPQL query but cannot 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 request doable in JPA?

I am using EclipseLink as my JPA provider.

The innermost exception that 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
+2


a source to share


2 answers


I gave up trying to create this query in JPA and wrote my own query instead



-1


a source


Assuming DATE is actually a timestamp, you are not worried about a collision, 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 the older MySQL that hates subqueries?

I also think that maybe your problem is that you haven't mapped the relationship of objects from MyProfileData to ProfileData and all you have is the actual integer value of the field. This will make writing JPQL queries quite complex overall.



Edit:

Continuing on the assumption that dates do not collide for any given profile type + profile data type (so date uniquely identifies a string in a subset of a specific profile + profile type combination), you can simply take all 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 is not actually legal, so it is difficult to create a way to reproduce what it looks like it is trying to do without being able to uniquely identify the strings, excluding the value.

0


a source






More articles:


All Articles