All geek questions in one place

JPA - MAX of COUNT or SELECT FROM SELECT

I wrote the following query for MySQL:

SELECT subquery.t1_column1, subquery.t2_id, MAX(subquery.val) FROM ( SELECT t1.column1 as t1_column1, t1.id_t2 AS t2_id, count(1) AS val FROM table1 t1 INNER JOIN table2 t2 ON t2.id = t1.id_t2 GROUP BY t1.id_t2 ) subquery GROUP BY t1_column1

And I would like to translate it into JPA (JPQL query or criteria).

I don't know how to do this maximal (countable) thing, and JPA doesn't seem to like SELECT FROM SELECT ...

If someone has an idea other than their own requests (I will do it now), it would be great.

+6
source share
2 answers

I have not tested the JPA specification, but given that the Hibernate documentation says

Please note that HQL subqueries can only occur in select or where articles.

I very much doubt that your query can be converted to a valid JPQL query.

You will have to use this native SQL query.

+3
source

JPA 2.0 JPQL does not support subselects in the from clause. You can try to rewrite your query or use your own SQL query.

EclipseLink 2.4 will support subsamples in the FROM clause.

cm,

http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL#Sub-selects_in_FROM_clause

+2
source

Source: https://habr.com/ru/post/916338/

More articles:


All Articles