Pagebale & sort by COUNT() with other table

I've got this query

SELECT t.id,t.title,count(k.id) as likes
FROM topics t
LEFT JOIN topic_like k on t.id = k.topic_id
group by t.id, t.title
ORDER BY likes desc ;

When I try to convert it to the JPQL, I do it like this but I gut some issues

@Query("SELECT p,count(k.id) as likes FROM TopicJpa p "  
            "LEFT JOIN TopicLikeJpa k "  
            "GROUP BY p ORDER BY COUNT(likes)")
    Page<TopicJpa> getAllTopicsWithLikeCountsSort(Pageable pageable);

CodePudding user response：

The query should be:

SELECT p, count(k) as likes
FROM TopicJpa p
         LEFT JOIN p.topicLikeJpa k
GROUP BY p
ORDER BY likes

You can return the result as a Page<Object[]>:

@Query("...")
Page<Object[]> getAllTopicsWithLikeCountsSort(Pageable pageable);

Page<Object[]> page = repository.getAllTopicsWithLikeCountsSort(...);
List<Object[]> resultList = page.getContent();
for(Object[] row : resultList) {
   TopicJpa topic = (TopicJpa) row[0];
   Long counter = (Long) row[1];
}

Or, you can create an interface with two fields:

interface TopicWithCounter {
    TopicJpa getTopic();
    Long getLikes();
}

and use it as the page type:

@Query("...")
Page<TopicWithCounter> getAllTopicsWithLikeCountsSort(Pageable pageable);

If you don't care about the count for each topic, you can omit it from the select clause and return Page<TopicJpa> instead:

@Query("SELECT p FROM TopicJpa p LEFT JOIN p.topicLikeJpa k GROUP BY p ORDER BY COUNT(k)")
Page<TopicJpa> getAllTopicsWithLikeCountsSort(Pageable pageable);