CODEWITHSUNDEEP
springhibernatejpaspring-data-jpajpql
S3D
S3D

Reputation: 139

Pagebale & sort by COUNT() with other table - JPA

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);

Upvotes: 1

Views: 691

Answers (1)

Davide D&#39;Alto
Davide D&#39;Alto

Reputation: 8206

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);

Upvotes: 2

Related Questions