• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Jpql - order by sum?

 
ayi bogan
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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


Hope someone can help.
 
James Sutherland
Ranch Hand
Posts: 553
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You cannot sum an Entity, you must sum one of its basic attributes.
 
ayi bogan
Greenhorn
Posts: 7
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
vrr ist not a entity its = videoEntity.videoRatingEntity.rating

But i "improved" my solution:





The Problem is that i dont get an lit auf Entities, bec. i have not a unique Select object attribut. But dont know how i can Order By sum and get a list of objects

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic