我有這個查詢
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 ;
當我嘗試將其轉換為 JPQL 時，我會這樣做，但我會遇到一些問題
@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);
uj5u.com熱心網友回復：
查詢應該是：
SELECT p, count(k) as likes
FROM TopicJpa p
LEFT JOIN p.topicLikeJpa k
GROUP BY p
ORDER BY likes
您可以將結果回傳為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];
}
或者，您可以創建一個包含兩個欄位的界面：
interface TopicWithCounter {
TopicJpa getTopic();
Long getLikes();
}
并將其用作頁面型別：
@Query("...")
Page<TopicWithCounter> getAllTopicsWithLikeCountsSort(Pageable pageable);
如果您不關心每個主題的計數，則可以從 select 子句中省略它并回傳Page<TopicJpa>：
@Query("SELECT p FROM TopicJpa p LEFT JOIN p.topicLikeJpa k GROUP BY p ORDER BY COUNT(k)")
Page<TopicJpa> getAllTopicsWithLikeCountsSort(Pageable pageable);
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/495695.html