Select top N rows, first max row from each group distinct by one column (Spring,Hibernate,JPQL)

Question

Entities:

• Score [id, user_id, value]

• User [id, name]

I want to eager load top 10 scores distinct(by user_id) joined to User

Native query works but hibernate cant map user to score (again lazy loading),

I Tried to write JPQL query but it generates wrong query

Working natie query:

select * 
from scores 
join users 
    on scores.user_id = users.id and 
    user_id in (
        select distinct user_id 
        from scores order by value desc
    ) 
 order by value desc limit 10

Answer 1

Your query selects Scores and only joins some Users but does not really JOIN FETCH the Score.users or the user.score associations. If you want to eager load both Score and the associated Users, you need to something like this:

You can use Window Functions, which are supported since MySQL 8 and run the following SQL query:

List<Score> scores = entityManager.createNativeQuery(
    "select u_s_r.* " +
    "from (   " +
    "    select *, dense_rank() OVER (ORDER BY value DESC) rank " +
    "    from (   " +
    "        select s.*, u.* " +
    "        from scores s  " +
    "        join users u s.user_id = u.id  " +
    "        order by u.id " +
    "    ) u_s " +
    ") u_s_r " +
    "where u_s_r.rank <= :rank", Score.class)
.setParameter("rank", 10)
.unwrap( NativeQuery.class )
.addEntity( "s", Score.class )
.addEntity( "u", User.class )
.setResultTransformer( DistinctScoreResultTransformer.INSTANCE )
.getResultList();

The DistinctScoreResultTransformer can look as follows:

public class DistinctScoreResultTransformer 
        extends BasicTransformerAdapter {
 
    private static final DistinctScoreResultTransformer INSTANCE  = 
            new DistinctScoreResultTransformer();
 
    @Override
    public List transformList(List list) {
        Map<Serializable, Identifiable> identifiableMap = 
                new LinkedHashMap<>( list.size() );
 
        for ( Object entityArray : list ) {
            if ( Object[].class.isAssignableFrom( 
                    entityArray.getClass() ) ) {
                Score score = null;
                User user = null;
 
                Object[] tuples = (Object[]) entityArray;
 
                for ( Object tuple : tuples ) {
                    if(tuple instanceof Score) {
                        score = (Score) tuple;
                    }
                    else if(tuple instanceof User) {
                        user = (User) tuple;
                    }
                    else {
                        throw new UnsupportedOperationException(
                            "Tuple " + tuple.getClass() + " is not supported!"
                        );
                    }
                }
                Objects.requireNonNull(score);
                Objects.requireNonNull(user);
 
                if ( !identifiableMap.containsKey( score.getId() ) ) {
                    identifiableMap.put( score.getId(), score );
                    score.setUsers( new ArrayList<>() );
                }
                score.addUser( user );
            }
        }
        return new ArrayList<>( identifiableMap.values() );
    }
}

If you are wondering if this can be done via JPQL, then you should know that this is not possible. However, you don't have to run every query via JPQL. SQL is there to help you.