Java Persistence Entitymanager - Select from another query's result set

Question

I currently have the query stated below. I know this works as a SQL query, but because I am using the EntityManager, from javax.persistence, it requires JPQL. And I don't know JPQL. If there is a way to rewrite this in JPQL that would be nice.

Query q = entityManager.createNativeQuery("
    WITH original AS (SELECT *, COUNT(ref) as c FROM Tri WHERE triH IN :list GROUP BY ref
    SELECT ref FROM original WHERE c = :amtTri");
q.setParameter("list", posTri);
q.setParameter("amtTri", posTri.size());

Actual query:

WITH original AS (SELECT *, COUNT(ref) as c FROM Tri WHERE triH IN :list GROUP BY ref
SELECT ref FROM original WHERE c = :amtTri

I am trying to do this in a Quarkus project using the Repository method, if there is a way to use that, that would also be fine

Thanks in advance!

Answer 1

I believe that using the EntityManager don't obligates you to use JPQL, you can also use Native Queries.

As your query looks not so simple (for me at least), I would do it using Native Queries and not JPQL. You can run Native Queries using the EntityManager from javax.persistence. This tutorial explains how you can do this.