SQL to JPQL join request

Question

Hopefully someone here can help me.

I have found the request I want to use on sqlDev :

SELECT t1.*
  FROM table1 t1 INNER JOIN
(
  SELECT ev.ID_AGENCE, MAX(ev.DATE_CREATION) DATE_CREATION
    FROM table1 ev
    WHERE ev.ID_AGENCE IN (326,324)
    GROUP BY ev.ID_AGENCE
) t2 ON t1.ID_AGENCE = t2.ID_AGENCE
    AND t1.DATE_CREATION = t2.DATE_CREATION
    order by t1.id_agence;

to keep only the closest date in a list, and only one per id (324 and 326 here in my exemple) :

324 22/10/18
324 21/10/18
324 20/10/18
326 10/08/18
326 09/08/18
326 07/08/18
326 06/08/18
326 05/08/18
326 04/08/18
326 03/08/18
326 02/08/18
326 01/08/18

I tried to translate to JPA (JPQL) :

final String requete = "SELECT e FROM ClasseJava JOIN " + 
                "( " + 
                    "SELECT f.id, MAX(f.dateCreation) dateCreation " + 
                    "FROM ClasseJava f " + 
                    "WHERE f.idAgence IN (326,324) " + 
                    "GROUP BY f.idAgence " + 
                ") "                        + 
                "t ON e.idAgence = t.idAgence " + 
                "AND e.dateCreation = t.dateCreation " + 
                "GROUP BY idAgence ";

final TypedQuery<ClasseJava> query = entityManager.createQuery(requete, ClasseJava.class);
query.setParameter("listIdAgence", listIdAgence);
return query.getResultList();

And I get the following error : "The join association path is not a valid expression."

Does anyone have an idea on how to fix the issue ?

I think you need to provide the `ClasseJava` and describe what you actually want to do instead of just pasting some queries — evandongen, Oct 25 '18 at 13:49
Possible duplicate of [JPQL JOINS with nested SELECT](https://stackoverflow.com/questions/10185542/jpql-joins-with-nested-select) — K.Nicholas, Oct 25 '18 at 15:21

score 0 · Answer 1 · answered Oct 25 '18 at 15:31

0

According to this answer it's impossible to use a subquery in a join clause. Same can be said about Criteria API and HQL

Maybe you can rewrite your query using a HAVING clause or by moving the subquery to a WHERE clause?

answered Oct 25 '18 at 15:31

Yaroslav

118
1
8

I made it work like this : final String requete = "SELECT e FROM classeJava e, " + "( " + "SELECT f.idAgence, MAX(f.dateCreation) dateCreation " + "FROM classeJava f " + "WHERE f.idAgence IN :listIdAgence " + "GROUP BY f.idAgence " + ") " + "t WHERE e.idAgence = t.idAgence " + "AND e.dateCreation = t.dateCreation " + "ORDER BY e.idAgence "; – Poncelin Juriaan Oct 26 '18 at 07:14

score 0 · Answer 2 · answered Oct 26 '18 at 13:06

0

I made it work by removing the Join clause.

like this :

 final String requete = "SELECT e FROM classeJava e, " + 
"( " + 
"SELECT f.idAgence, MAX(f.dateCreation) dateCreation " + 
"FROM classeJava f " + 
"WHERE f.idAgence IN :listIdAgence " + 
"GROUP BY f.idAgence " + 
") " + 
"t WHERE e.idAgence = t.idAgence " + 
"AND e.dateCreation = t.dateCreation " + 
"ORDER BY e.idAgence ";

Hope this helps someone.

answered Oct 26 '18 at 13:06

Poncelin Juriaan

11
3

Are you using EclipseLink? Only EclipseLink 2.4 or superior could support subquery on `FROM` clause using JPQL. – Dherik Dec 20 '18 at 15:34

SQL to JPQL join request

2 Answers2

Linked