Jpql - order by sum ???

ayibogan · **Joined:** Mon Aug 30, 2010 12:34 pm **Posts:** 4

MYSQL
JAVA EE 6
JPA 2

Hello,
i want get a list of "Video Objects" from DB order by Desc how much(good) they are rated.

VideoRatingEntity
@ManyToOne()
@JoinColumn(name = "VIDEO_ID")
videoEntity

ID | VIDEO_ID| USER_ID| RATING
--------------------------------

Video Entity
@OneToMany
videoRatingEntities

ID | ... | ... |
-------------

My Try:
"SELECT v, sum(vrr) "
+ " FROM VideoEntity AS v"
+ " JOIN v.videoRatingEntities AS vr"
+ " JOIN vr.rating AS vrr"
+ " GROUP BY v"
+ " ORDER BY sum(vrr) DESC";

EXCEPTION:
SCHWERWIEGEND: java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager:
Exception Description: Syntax error parsing the query [SELECT v, sum(vrr) FROM VideoEntity AS v JOIN v.videoRatingEntities AS vr JOIN vr.rating AS vrr GROUP BY v ORDER BY sum(vrr) DESC].
Internal Exception: org.eclipse.persistence.internal.libraries.antlr.runtime.Ear lyExitException

saltnlight5 · **Posted:** Mon Aug 30, 2010 1:40 pm

Have you read http://docs.jboss.org/hibernate/stable/ ... yhql-joins ?

Careful with your query string. You probably want this:

Code:

select v, sum(videoRatings.rating) 
from VideoEntity v
inner join v.videoRatingEntities as videoRatings
group by v
order by sum(videoRatings.rating) desc

You probably want to use a explicit field name in the "group by v.<yourField>", other wise it will use the mapped id property.

ayibogan · **Joined:** Mon Aug 30, 2010 12:34 pm **Posts:** 4

i am reading http://docs.jboss.org/hibernate/stable/ ... yhql-joins, think i muss get more know how about Joins.

I try your example but get this exception:

Exception Description: Syntax error parsing the query [ select v, sum(videoRatings.rating) from VideoEntity v inner join v.videoRatingEntities as videoRatings order by sum(videoRatings.rating) desc], line 1, column 113: unexpected token [sum].

saltnlight5 · **Posted:** Mon Aug 30, 2010 5:23 pm

You might have problem with your entities mapping. Can you show the two full class source code and the mapping annotations? Which version of Hibernate are you running?

ayibogan · **Joined:** Mon Aug 30, 2010 12:34 pm **Posts:** 4

Soo after some hours of investigation i have found, that i must put an Alias in the ORDER BY SUM(...ALIAS...).

My Statment is now:

Code:

SELECT video, sum(videoRatings.rating) as sumRating
FROM VideoEntity video
INNER JOIN video.videoRatingEntities as videoRatings
GROUP BY video
ORDER BY sumRating desc

Problem:
I dont get a list of "Video Objects", think i get the list of addend.
Does Anyone know how i can see in NetBeans DB issues ?