# Jpql -  order by sum ???



## ayibogan (30. Aug 2010)

MYSQL
JAVA EE 6
JPA 2

Hallo,
ich möchte aus einer DB "Video-Objekte" absteigend 
- danach wie oft bzw. wie gut sie gevotet(RATING) worden sind - holen.


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

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


*Video Entity*
@OneToMany
videoRatingEntities 

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


Mein Ansatz:
"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.EarlyExitException


Hoffe jemand kann weiterhelfen.


----------



## Gast2 (30. Aug 2010)

Ohne jetzt Jpql zu kennen sieht die Join Syntax ziemlich krude aus...

Ich würde sowas in die Richtung erwarten:

```
SELECT v.*, vre.*
FROM VideoEntity AS v, VideoRatingEntity AS vre WHERE
v.id = vre.video_id
```

Probier erstmal das - und dann bau das SUM ein.


----------



## ayibogan (30. Aug 2010)

> SELECT v.*, vre.*
> FROM VideoEntity AS v, VideoRatingEntity AS vre WHERE
> v.id = vre.video_id



wäre in JPQL ca. so, was auch funktioniert.


```
SELECT v, vrr
                   FROM VideoEntity AS v, VideoRatingEntity AS vre WHERE
                   v.id = vre.videoEntity.id
```

Doch nachdem ich das OrderBy und Sum einbaue kommt der gleiche Fehler:


```
SELECT v, sum(vrr)
                   FROM VideoEntity AS v, VideoRatingEntity AS vre WHERE
                   v.id = vre.videoEntity.id
                   GROUP BY v
                   ORDER BY sum(vrr) DESC
```


----------



## Gast2 (30. Aug 2010)

```
GROUP BY v
```

Er soll nach der Tabelle v gruppieren? Ich glaube eher du suchst eher


```
GROUP BY v.id
```

Und den identifier vrr wird er auch nicht kennen - ich habe die Relation ja sinnvoller weise vre genannt

Und das SELECT selber erwartet auch Spaltennamen - keine ganze Tabelle:


```
SELECT v.*, SUM(vre)
```

oder 


```
SELECT v.id, SUM(vre)
```


----------



## ayibogan (30. Aug 2010)

Das mit vre/vrr hab ich wohl übersehen, arbeite schon seit einigen vielen Stunden daran 

Das mit v ist schon ok, JPQL sieht das v als ein Objekt bzw eine Zeile in der Tabelle das als Objekt dargestellt wird.

Nach der Anfrage:

*Query query = em.createQuery(...JPQL STATEMENT...);*

soll eine Liste von Video Objekten gespeichert werden:

*List<VideoEntity> videos = (List<VideoEntity>) query.getResultList();*


----------



## Gast2 (30. Aug 2010)

Ah, ok. Also läuft es jetzt?


----------



## ayibogan (30. Aug 2010)

Leider nicht, ich denke ma JPQL hat irgendein Problem mit


```
ORDER BY sum(vre) DESC
```


----------



## Gast2 (30. Aug 2010)

ayibogan hat gesagt.:


> Leider nicht, ich denke ma JPQL hat irgendein Problem mit
> 
> 
> ```
> ...



Schon mal

```
SELECT v, SUM(vre.rating)
...
ORDER BY SUM(vre.rating) DESC
```

probiert?


----------



## ayibogan (30. Aug 2010)

Jup sehe grad auch das ich oben


```
SELECT v, SUM(vre)
...
ORDER BY SUM(vre) DESC
```

Das geht natürlich nicht 

Aber mit

```
SELECT v, SUM(vre.rating)
...
ORDER BY SUM(vre.rating) DESC
```

kommt auch die Exception:
Exception Description: Syntax error parsing the query [ SELECT v, sum(vre.rating) FROM VideoEntity v, VideoRatingEntity vre WHERE v.id = vre.videoEntity.id ORDER BY sum(vre.rating) DESC], line 1, column 110: unexpected token [sum].


----------



## ayibogan (30. Aug 2010)

> Unfortunately, the SUM function used in JPQL does not allow you to pass an arithmetic expression as the argument. What this means in practice is that you won't be able to pass p.price*l.quantity as the argument to the JPQL's SUM





> JPQL is still evolving, and doesn't have many of those important features available in SQL. In the Defining JPQL Joins earlier section, you saw an example of JPQL's incompleteness: you had to do a lot of work on your own because the JPQL's SUM aggregate function cannot take an arithmetic expression as the parameter. In contrast, the SQL's SUM function doesn't have such a limitation. So, this is a good example of where replacing JPQL with native SQL could be efficient. The following code illustrates how you might simplify things in this particular example by choosing native SQL over JPQL:



Querying JPA Entities with JPQL and Native SQL

Mein English ist net besodners gut, aber glaube daran liegts oder?


----------



## Gast2 (30. Aug 2010)

Ne - das heißt eigentlich nur das du in dem SUM nicht noch eine weitere arithmetische Funktionauswerten kannst.

Also sowas wie SUM(13*7+1) würde nicht gehn. Also in SQL eher etwas wie 

```
SELECT SUM(SUM(items)/COUNT(items)) AS durchschnitt
```

Probier es bitte einmal ohne das [c]ORDER BY[/c] evtl erlaubt JPQL nicht im order by Funktionen auszuwerten.


----------



## ayibogan (30. Aug 2010)

So hab nun erfahren das JPQL nur Alias im ORDER BY SUM akzeptiert.

So funktionierts:



```
SELECT v, SUM(vre.rating) AS sumRating
...
ORDER BY SUM(sumRating) DESC
```


Nur ist das Problem, dass ich keine Liste von Video Objekten bekomme. Denke eher, dass ich die Summe der Ratings bekomme.
Weiß leider nicht wie ich Netbeans so einstelle, dass ich die Ausgabe von Querry sehen kann.


----------



## Gast2 (30. Aug 2010)

ayibogan hat gesagt.:


> So hab nun erfahren das JPQL nur Alias im ORDER BY SUM akzeptiert.
> 
> So funktionierts:
> 
> ...



Wenn dann wohl eher:


```
SELECT v, SUM(vre.rating) AS sumRating
...
ORDER BY sumRating DESC
```

Aber das wird wohl ein Tipfehler sein.

Kannst du nicht einfach was du zurück bekommst dir aufs sysout/logger werfen?


----------



## ayibogan (31. Aug 2010)

Ich danke dir schonma vorab für deine Mühe.

Weiß leider nicht ob und wie ich die ResultList ausgeben kann.
Hab auch nun mein Statement sehr verändert. ist stärker auf JPQL ausgelegt:



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


----------

