Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

CREATE TABLE user (ID int AUTO_INCREMENT,PRIMARY KEY (ID));
CREATE TABLE points (ID int AUTO_INCREMENT, user_id int, points int,PRIMARY KEY (ID));
How can I use spring-boot jpa to request user and max points like this?
select u.ID,max(p.points) from user u, points p where u.id=p.user_id
Or any alternatives to solve this kind of problems?
I haven't tested, but you should be able to do:
User findFirstByIdOrderByPointPointsDesc(int userId)
Similar to example 18 in the docs.
The only problem you have, regardless of the query or Spring Data, is if you have two users with the same point values. If you need more logic around tie-breaking, it might be more worth it to write a @Query (with your query, plus the extra tie-breaking logic) or a @NativeQuery.
And write a custom query in the repository to fetch the data
@Query(value = "SELECT new com.package.Result (u, MAX (p.points) ) 
FROM user u
JOIN points p
ON u.id = p.user_id 
GROUP BY u")
List<Result> getPointsPerUser();    
Replace com.package.Result with appropriate path to the Result class.
                Thanks for your answer. But what if I need some other information for points record? For example if I need maximum points and when user got that points?
– passer
                Jan 13, 2019 at 14:27
                If you need more columns from points table, you can fetch the points object rather than using aggregate function. You can replace the select with u, p and remove the GROUP BY clause. Also modify the Result class to accept points. This way you have the all the data in the both the table. The drawback is you now have to find the maximum from the List.
– Sujit
                Jan 13, 2019 at 22:25
Below method can be written in Repo and used as Transaction as in dao layer, which will be accessible from service layer.
@Query(value = "SELECT max(transactionId) FROM TransactionPayloadInfo")
int getMaxTransactionId();
And write your query like this for getting model of Data.
 @Query(select packagename.Data(u.ID,max(p.points) ) from user u, points p where   u.id=p.user_id)
 Data findUserWithMaxVots();
                Thanks for your answer. But what if I need some other information for points record? For example if I need maximum points and when user got that points?
– passer
                Jan 13, 2019 at 14:39
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.