I have three entities building a "tree structure" of "parent A", "child B"

and "grand child C", using bidirectional 1:N relations.

So, every A can have multiple B. Each B can have multiple C.

Now I must run a JPA QL query in the form of:

Build a SUM over a field from the parent A, for each GROUP. The GROUPs are

built from a field in the child B. Only consider rows of A that have a

grandchild C with a specific field content.

What I tried is:

SELECT SUM(a.ax) FROM parent a LEFT JOIN a.children b LEFT JOIN b.children c

WHERE c.cx = ? GROUP BY b.bx

But the resulting sum is wrong: Due to the joining, the parent row is

multiplicated. So the resulting sum is way too big.

Then I tried using "SELECT DISTINCT" or "SELECT SUM(DISTINCT a.ax)", but

obviously the result then is as false as before -- since this usage of

DISTINCT is just wrong (I do neither want to reduce the number of GROUPs nor

ignore duplicate a.ax values coming from different a instances in the same

group).

I am totally desparate, since I do not find the solution since days. ;-(

Thanks a lot!

Markus]]>

SELECT BX, SUM(AX) FROM (SELECT DISTINCT AID, AX, BX FROM A LEFT JOIN B LEFT

JOIN C WHERE CX = ?) X GROUP BY BZ

But I am not sure how to translate this to JPA QL... Any ideas?

Thanks

Markus

"Markus KARG" <karg@quipsy.de> schrieb im Newsbeitrag

news:gcmv5v$b3v$1@build.eclipse.org...

>I am getting nuts with a simple JPA QL query. So I hope that somebody here

>in the forum can give me a simple answer. :-)

>

> I have three entities building a "tree structure" of "parent A", "child B"

> and "grand child C", using bidirectional 1:N relations.

>

> So, every A can have multiple B. Each B can have multiple C.

>

> Now I must run a JPA QL query in the form of:

>

> Build a SUM over a field from the parent A, for each GROUP. The GROUPs are

> built from a field in the child B. Only consider rows of A that have a

> grandchild C with a specific field content.

>

> What I tried is:

>

> SELECT SUM(a.ax) FROM parent a LEFT JOIN a.children b LEFT JOIN b.children

> c WHERE c.cx = ? GROUP BY b.bx

>

> But the resulting sum is wrong: Due to the joining, the parent row is

> multiplicated. So the resulting sum is way too big.

>

> Then I tried using "SELECT DISTINCT" or "SELECT SUM(DISTINCT a.ax)", but

> obviously the result then is as false as before -- since this usage of

> DISTINCT is just wrong (I do neither want to reduce the number of GROUPs

> nor ignore duplicate a.ax values coming from different a instances in the

> same group).

>

> I am totally desparate, since I do not find the solution since days. ;-(

>

> Thanks a lot!

> Markus

>]]>

> SELECT BX, SUM(AX) FROM (SELECT DISTINCT AID, AX, BX FROM A LEFT JOIN B LEFT

> JOIN C WHERE CX = ?) X GROUP BY BZ

"... FROM SELECT ..." is not allowed by the JPQL grammar. IMHO you have

three options (not tested!):

1.) a native SQL query (EntityManager#createNativeQuery(...))

2.) Two distinct queries: the first one to select all parents that meet

the condition and the second one to calculate the sum - more or less like:

SELECT a, SUM(a.AX) FROM Parent a WHERE a IN (:parentsCollection)

3.) Rewrite the original JPQL query so that the subselect moves into the

WHERE part: ... FROM Parent a WHERE a in (SELECT ax FROM Parent ax,

IN(ax.child) b, IN(b.child) c WHERE c.xyz = :...))

-- Frank]]>

news:35482b29a40f724bd07d07e99a9abe94$1@www.eclipse.org...

> Markus KARG wrote:

>

>> SELECT BX, SUM(AX) FROM (SELECT DISTINCT AID, AX, BX FROM A LEFT JOIN B

>> LEFT JOIN C WHERE CX = ?) X GROUP BY BZ

>

> "... FROM SELECT ..." is not allowed by the JPQL grammar. IMHO you have

> three options (not tested!):

> 1.) a native SQL query (EntityManager#createNativeQuery(...))

This is what I am currently doing but it breaks the transparency: now my

Java code must know the column names, while it was hidden by the JPA

provider before. Obviously the most easy solution providing the highest

performance, but at the same time the least transparent one clearly breaking

one of the core ideas of JPA: Transparency of the backend.

> 2.) Two distinct queries: the first one to select all parents that meet

> the condition and the second one to calculate the sum - more or less like:

> SELECT a, SUM(a.AX) FROM Parent a WHERE a IN (:parentsCollection)

Can you give a code example how this can be done? I mean, can I just get the

result from the first query as a collection and put it as a parameter for

the second one? That would be a cool solution, but obviously performance

will be a real problem since I have thousands of results in the first

query...

> 3.) Rewrite the original JPQL query so that the subselect moves into the

> WHERE part: ... FROM Parent a WHERE a in (SELECT ax FROM Parent ax,

> IN(ax.child) b, IN(b.child) c WHERE c.xyz = :...))

This is impossible since you cannot do the grouping over b.bx then.

It seems there just is no clean solution. A clear drawback of JPA. :-(

Thanks

Markus

>

> -- Frank

>]]>

>> 2.) Two distinct queries: the first one to select all parents that meet

>> the condition and the second one to calculate the sum - more or less like:

>> SELECT a, SUM(a.AX) FROM Parent a WHERE a IN (:parentsCollection)

> Can you give a code example how this can be done? I mean, can I just get the

> result from the first query as a collection and put it as a parameter for

> the second one? That would be a cool solution, but obviously performance

> will be a real problem since I have thousands of results in the first

> query...

Object model: Person 1<-->* Address

Query query1 = entityManager.createQuery("SELECT p.id FROM Person p");

Collection<Object> personIdVector = (Collection<Object>)

query1.getResultList();

Query query2 = entityManager.createQuery("SELECT p, COUNT(a) FROM Person p

JOIN p.addresses a WHERE p.id IN (:personIdVector) GROUP BY p");

query2.setParameter("personIdVector", personIdVector);

Collection<Object[]> collection2 = (Collection<Object[]>)

query2.getResultList();

Will result in:

select

person0_.id as col_0_0_,

count(addresses1_.id) as col_1_0_,

person0_.id as id1_,

person0_.firstName as firstName1_,

person0_.lastName as lastName1_

from

TEST.Person person0_ inner join TEST.Address addresses1_ on

person0_.id=addresses1_.PERSON_ID

where

person0_.id in (? , ?)

group by

person0_.id

This was Hibernate. Unfortunately, it does not seem to work with

EclipseLink and it does not seem to be covered by the JPA specification

either.

>> 3.) Rewrite the original JPQL query so that the subselect moves into the

>> WHERE part: ... FROM Parent a WHERE a in (SELECT ax FROM Parent ax,

>> IN(ax.child) b, IN(b.child) c WHERE c.xyz = :...))

> This is impossible since you cannot do the grouping over b.bx then.

How about this?

Object model: Parent -->*Child -->*Grandchild

JPQL query:

SELECT p, SUM(c.age)

FROM Parent p, IN(p.children) c

WHERE p.id IN (SELECT p1.id FROM Parent p1, IN(p1.children) c1,

IN(c1.grandchildren) g1 WHERE g1.name = :name)

GROUP BY p

Btw., with Hibernate this is also possible:

SELECT p, SUM(c.age)

FROM Parent p, IN(p.children) c

WHERE p IN (SELECT p1 FROM Parent p1, IN(p1.children) c1,

IN(c1.grandchildren) g1 WHERE g1.name = :name)

GROUP BY p

For me it seems to deliver correct results.

-- Frank]]>

> This was Hibernate. Unfortunately, it does not seem to work with

> EclipseLink and it does not seem to be covered by the JPA specification

> either.

thanks a lot for the code sample, but see... well... as this is the

EclipseLink forum, I obviously am bound to the EclipseLink features... ;-)

Actually even worse, my app must be 100% JPA compliant... :-(

>>> 3.) Rewrite the original JPQL query so that the subselect moves into the

>>> WHERE part: ... FROM Parent a WHERE a in (SELECT ax FROM Parent ax,

>>> IN(ax.child) b, IN(b.child) c WHERE c.xyz = :...))

>

>> This is impossible since you cannot do the grouping over b.bx then.

>

> How about this?

> Object model: Parent -->*Child -->*Grandchild

>

> JPQL query:

> SELECT p, SUM(c.age) FROM Parent p, IN(p.children) c WHERE p.id IN (SELECT

> p1.id FROM Parent p1, IN(p1.children) c1, IN(c1.grandchildren) g1 WHERE

> g1.name = :name) GROUP BY p

"IN" results in a SQL JOIN and such will impose multipications of c.age. The

SUM then is just incorrect.

> For me it seems to deliver correct results.

Actually unfortunately it does not due to the SQL JOIN multiplication...

Thanks, anyways. :-)

Regards

Markus]]>