6

"bonjour à tous" (hi to everyone)

I'm coming from this thread : How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

This guy is doing almost what I need in my project. In fact, I'm trying to do something like Git : retrieve all instances in their last version. Currently, I have a table "UM", with those attributes:

  • int id (primary key, auto generated)
  • String name (can be duplicated)
  • int version (1, 2, 3, [...])
  • String fmr
  • [others attributes, not used in this case]

I have also this request in SQL (that working well and retrieves the wanted result):

SELECT * FROM agrid.um AS u INNER JOIN
        (SELECT u2.name, max(u2.version) AS MaxVersion, u2.fmr
         FROM agrid.um AS u2 WHERE u2.fmr = 'CZ2ABVIMG0000' GROUP BY u2.name, u2.fmr) umVersion
         ON u.name = umVersion.name AND u.version = umVersion.MaxVersion
         WHERE u.fmr = 'ABCDE';

But when I tried to do the same in JPQL, I have some "JOIN" errors:

TypedQuery<UmEty> q = getManager().getEntityManager().createQuery(
            "SELECT u FROM UmEty u INNER JOIN"
            + " (SELECT u2.name, max(u2.version) AS MaxVersion, u2.fmr "
            + " FROM UmEty u2 WHERE u2.fmr = :fmr GROUP BY u2.name, u2.fmr) umVersion"
            + " ON u.name = umVersion.name AND u.version = umVersion.MaxVersion WHERE u.fmr = :fmr", UmEty.class);
    q.setParameter("fmr", fmr);
    return q.getResultList();

With the following exception:

Exception Description: Syntax error parsing
[SELECT u FROM UmEty u INNER JOIN (SELECT u2.name, max(u2.version)
AS MaxVersion, u2.fmr 
FROM UmEty u2 WHERE u2.fmr = :fmr GROUP BY u2.name, u2.fmr) umVersion
ON u.name = umVersion.name
AND u.version = umVersion.MaxVersion WHERE u.fmr = :fmr].
[33, 147] The join association path is not a valid expression.

I've also tried to simply use "JOIN" instead of "INNER JOIN", and some other tests (remove the "WHERE" clause, ...), but I always have this exception throwed. I'm not so familiar with JPQL and I've already read the "INNER JOIN" topic on Objectdb (==> https://www.objectdb.com/java/jpa/query/jpql/from ). But even with those information, I'm not able to have a successful request

Can someone tell me why my SQL request translation into a JPQL one is not working ?

Thanks in advance :)

Antoine
  • 181
  • 1
  • 8
  • Wasn't a question, just like this one, asked earlier – soufrk Jun 26 '18 at 12:02
  • The question is: how can I translate this SQL request to JPQL one ? Why do I have "join" issues ? – Antoine Jun 26 '18 at 12:09
  • Check [JPA: JOIN in JPQL](https://stackoverflow.com/a/3730899/3831557) – Rajen Raiyarela Jun 26 '18 at 12:39
  • Thanks for your help. But I've already try to use only "JOIN" and everything listed in ObjectDB page. But still no working :/ – Antoine Jun 26 '18 at 12:45
  • SQL is much more powerful than JPQL - only a very small subset of the SQL language can be translated directly to an obfuscation layer like JPQL . –  Jun 26 '18 at 13:20

1 Answers1

0

For a join to work in JPQL you must have a direct dependency in your object graph to the entity you wish to join.

This example comes from the spec itself :

SELECT c FROM Customer c JOIN c.orders o WHERE c.status = 1

where Customer is an entity with a relationship to an Order entity through customer.orders list object.

You can read more on joins in chapter 4.4.5 of Java Persistence Spec

EDIT:

You may wish to rewrite your query to comply with the following format:

select * from agrid.um u where u.fmr = 'ABCDE' and not exists (select * from agrid.um u2 where u.name = u2.name and u.version < u2.version) ;

João Rebelo
  • 79
  • 14
  • Ok I see, but it's not my "case". I just want to work on the same table, and make the JOIN with a pre-selection of the table (the second SELECT clause). Is it possible ? – Antoine Jun 26 '18 at 13:20
  • afaik you cannot do that query without an explicit relationship declared in your entity model. But you can translate your query to use an **EXISTS** condition. – João Rebelo Jun 26 '18 at 13:29
  • Well, sorry if i am wrong but with jpql, you can not create sub select query (except using where and having clause) – kidnan1991 Jun 26 '18 at 14:12
  • Hi. Someone already said it to me, but I have already did it in another (easier) case. So it's not the problem here :/ – Antoine Jun 26 '18 at 14:24
  • I've edited my answer as to make my comment on using **EXISTS** clearer. If all you want is the maximum version of the entries in that table. – João Rebelo Jun 26 '18 at 14:34
  • Hi and thanks again for your help. But it's still not working, because the clause "u.name = u2.name" force the request to retrieve only one UM (always the same, always one name and not the others) – Antoine Jun 27 '18 at 09:11