CODEWITHSUNDEEP
hibernatejpacountgroup-byjpql
Aliaxander
Aliaxander

Reputation: 2617

Order by COUNT in JPQL

I have News entity in one-to-many relation with Comments. My goal is to sort News according to number of comments. The following query works, but it obviously returns only IDs, not all News' fields:

SELECT news.id 
    FROM News news JOIN news.comments comments
    GROUP BY news 
    ORDER BY COUNT(comments) DESC

If i replace news.id with news, an error appears:

SqlExceptionHelper:146 - ORA-01034: ORACLE not available

How to get the entire sorted News entities in a single query, not only the IDs?

(Database: Oracle XE, persistence provider: Hibernate)

Upvotes: 0

Views: 1090

Answers (1)

Szarpul
Szarpul

Reputation: 1571

As the @thanhnguyen said:

If you pass an entity inside the GROUP BY, Hibernate automatically adds its id to the transformed SQL of the underlying DB. In addition, the values in the GROUP BY must exist in the SELECT clause. Thus, instead of select the whole object, you can select its id, then from those ids, you can retrieve the object again.

This could suggest that what you want to do is not possible.

Upvotes: 1

Related Questions