CODEWITHSUNDEEP
sqlhibernatejpajpql
poussma
poussma

Reputation: 7301

JPQL and sub-query in JPQL constructor expressions

I have a table ROOT and a table CHILDREN that contains rows related to ROOT.

I'd like to retrieve for each row of ROOT, an object that contains the ROOT object and the number of rows related to the ROOT.id in the CHILDREN table.

This can be achieved quite easily in SQL, but I am wondering how to write that in JPQL

In SQL: 

SELECT 
    r.*, 
    (SELECT COUNT(c.i_id) 
     FROM 
        children c 
    WHERE 
        c.rootId = r.id)
FROM root r;

I tried to rewrite it in JPA but it keeps failing with the following error 

Caused by: org.hibernate.HibernateException: Errors in named queries: xxx
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Unable to locate 
           appropriate constructor on class RootExtended...

Java class:

package plop;

public class RootExtended{

    private Root root;

    private Long count;

    public RootExtended(final Root root, final Long count) {
        this.root= root;
        this.count= count;
    }

    // getters and setters to follow
}

JPQL: 

SELECT 
    new plop.RootExtended(r, 
                    (SELECT 
                         count(c.id) 
                     FROM 
                         Child as c 
                     WHERE 
                         c.rootId = r.id
                     )
                    ) 
FROM 
    Root as r

Any idea ?

Upvotes: 1

Views: 3978

Answers (2)

V G
V G

Reputation: 19002

I think you are not allowed to use subqueries in the SELECT part in JPA (at least version<=2), as they are restricted to the WHERE and HAVING clauses. See this for details.

As solution you could make two queries, in the second making the COUNT() and loading it in the code to your ExtendedRoot instances.

Upvotes: 0

vels4j
vels4j

Reputation: 11298

Class :

public class RootExtended{

private Root root;

private Long count;

public RootExtended(final Root root, final Long count) {
    this.root= root;
    this.count= count;
}

// getters and setters to follow
}

Query : 

SELECT NEW package.RootExtended ( rt.r, rt.cnt) from ( select r from Root r, 
      (SELECT count(c.id) as cnt FROM Child c  WHERE  c.rootId = r.id ) rt )

Upvotes: 0

Related Questions