CODEWITHSUNDEEP
javamysqljpa
Drunken Daddy
Drunken Daddy

Reputation: 7991

JPA: ORDER BY before GROUP BY in JPARepository

This is the mysql query that I've:

SELECT * FROM (SELECT vev.* FROM vital_entry ve 
INNER JOIN vital_entry_values vev ON ve.vital_entry_id=vev.vital_entry_id 
WHERE ve.account_id=146 
ORDER BY date_entered DESC) t1
GROUP BY vital_id ;

How do I do this in JPA?? I tried:

@Query("SELECT t1 FROM (SELECT ve FROM VitalEntry ve " +
            "INNER JOIN ve.vitalEntryValues vev " +
            "WHERE ve.accountId=:accountId " +
            "ORDER BY ve.dateEntered DESC) t1")
List<VitalEntry> getRecentVitalValues(@Param("accountId") int accountId);

But IntelIj shows error expected identifer, got '('

Upvotes: 0

Views: 620

Answers (2)

Drunken Daddy
Drunken Daddy

Reputation: 7991

This gave me the expected result

@Query("SELECT new com.v2.model.VitalValue(vev.vitalId, vev.value, max(ve.dateEntered), ve.vitalEntryType, vev.type) FROM VitalEntry ve " +
            "INNER JOIN ve.vitalEntryValues vev " +
            "WHERE ve.accountId=:accountId " +
            "GROUP BY vev.vitalId " +
            "ORDER BY max(ve.dateEntered) DESC")
List<VitalValue> getRecentVitalValues(@Param("accountId") int accountId);

Upvotes: 2

Dave Pateral
Dave Pateral

Reputation: 1475

Use native query instead:

@Query("SELECT * FROM (SELECT ve.* FROM vital_entry ve INNER JOIN vital_entry_values vev ON ve.vital_entry_id=vev.vital_entry_id WHERE ve.account_id=:accountId ORDER BY date_entered DESC) t1 GROUP BY vital_id", nativeQuery=true)
List<VitalEntry> getRecentVitalValues(@Param("accountId") int accountId);

Upvotes: 1

Related Questions