@Queryin through list to find teh max

Question

( Im not sure if this is possible )
I have a One to Many relationship between A and B ( Multiple Bs for each A modelled using @OneToMany).
In my JpaRepository for B, I want to write a custom Query so as to return a single B which has the highest value for a specific attribute.
Something like:

@Query( value="SELECT a.list_of_Bs.//max(b.version)//  FROM A a WHERE a.id = :a_param" )
B findLargestB(A a_param) ;

How will I do max(b.version). ( How will I extract the single B from the list which has the highest version attribute )

Answer 1

METHOD 1:

Use @Query with nativeQuery = true in the JPA repository class of B

Here, I will use ORDER BY b.version DESC with LIMIT 1 in order to get the max version of B.

But unfortunately, currently, there is no way to limit records in JPQL. So that, if you want to limit it to 1 record only, you must add nativeQuery = true to @Query annotation.

The native query (for readability):

SELECT 
    b.* 
FROM 
    A a 
INNER JOIN B b ON b.a_id = a.id 
WHERE a.id = :a_id 
ORDER BY b.version DESC 
LIMIT 1

Solution with the above query:

@Query(value = "SELECT b.* FROM A a INNER JOIN B b ON b.a_id = a.id WHERE a.id = :a_id ORDER BY b.version DESC LIMIT 1", nativeQuery = true)
B findLargestB(Integer a_id) ;

METHOD 2:

Use "Spring Data Query Method"

You can use findFirst or findTop in combination with order by ... desc

Solution:

B findTopByAOrderByVersionDesc(A a);
B findFirstByAOrderByVersionDesc(A a);