CODEWITHSUNDEEP
jpajpql
user2997695
user2997695

Reputation: 143

JPA Query with GROUP BY, HAVING and COUNT

So the query below is probably not the most efficient, buy still, I am wondering why it is returning no result, even though the SQL counterpart does. There is no error, I am just getting no result. Is it maybe not the correct equivalent for the query I wrote in MySQL?

This is the JPA JPQL.

Query query = em.createQuery("SELECT sub FROM Subscription sub WHERE "
                + "sub.isSuspended = 0 AND "
                + "(SELECT i FROM Invoice i WHERE i.dateDue < CURRENT_DATE AND i.datePaid IS NULL "
                + "GROUP BY i HAVING COUNT(i.idInvoice) > 2) MEMBER OF sub.invoices");

And this is the SQL from MySQL.

SELECT * from subscription 
WHERE subscription.is_suspended = 0 AND id_subscription IN 
    (SELECT id_subscription FROM invoice 
    WHERE date_due < CURDATE() AND date_paid IS NULL
    GROUP BY id_subscription
    HAVING COUNT(*) > 2)

Upvotes: 0

Views: 5395

Answers (1)

Tomas
Tomas

Reputation: 138

The two queries are not the same. To use the actual query use the NativeQuery createNativeQuery() instead of Query.

In your case the JPA version seems to have syntax errors.

  1. After the AND you are missing the IN operator.
  2. In the nested query you are selecting i instead of something like i.idInvoice

The JPA query should look like

SELECT sub FROM Subscription sub
WHERE sub.isSuspended = 0
      AND sub.idSubscription IN
          (SELECT i.idInvoice
           FROM Invoice i
           WHERE i.dateDue < CURRENT_DATE AND i.datePaid IS NULL
           GROUP BY i.idInvoice 
           HAVING COUNT(i.idInvoice) > 2);

Upvotes: 1

Related Questions