Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Jpql - order by sum ???
Jpql - order by sum ??? [message #556047] Mon, 30 August 2010 12:29 Go to next message
No real name is currently offline No real name
Messages: 3
Registered: August 2010
Junior Member
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.

[Updated on: Mon, 30 August 2010 12:37]

Report message to a moderator

Re: Jpql - order by sum ??? [message #556079 is a reply to message #556047] Mon, 30 August 2010 13:54 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

>> sum(vrr)

does not make sense, you cannot sum an Entity, it must be a basic value. .i.e sum(vrr.some_value).

What version of EclipseLink are you using? JPQL has been improved in 2.1, but you still cannot sum an Entity.



James : Wiki : Book : Blog : Twitter
Re: Jpql - order by sum ??? [message #556098 is a reply to message #556047] Mon, 30 August 2010 15:00 Go to previous messageGo to next message
No real name is currently offline No real name
Messages: 3
Registered: August 2010
Junior Member
I use jpa 2.0

-------------------

vr.rating AS vrr"

rating is a value of the VideoRatingEntity Table, maybe bad definition Smile
Re: Jpql - order by sum ??? [message #556363 is a reply to message #556047] Tue, 31 August 2010 20:48 Go to previous messageGo to next message
No real name is currently offline No real name
Messages: 3
Registered: August 2010
Junior Member
But i "improved" my solution:



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


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

Re: Jpql - order by sum ??? [message #556724 is a reply to message #556047] Thu, 02 September 2010 09:03 Go to previous message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

What error do you get?


James : Wiki : Book : Blog : Twitter
Previous Topic:ObjectChangeSet always null
Next Topic:SQLServer pagination
Goto Forum:
  


Current Time: Wed Apr 16 23:52:20 EDT 2014

Powered by FUDForum. Page generated in 0.01655 seconds