Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » JPA QL Question
JPA QL Question [message #381691] Fri, 10 October 2008 03:10 Go to next message
Markus KARG is currently offline Markus KARG
Messages: 44
Registered: July 2009
Member
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
Re: JPA QL Question [message #381693 is a reply to message #381691] Fri, 10 October 2008 03:38 Go to previous messageGo to next message
Markus KARG is currently offline Markus KARG
Messages: 44
Registered: July 2009
Member
I just got an idea. In SQL the solution would be:

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
>
Re: JPA QL Question [message #382515 is a reply to message #381693] Mon, 13 October 2008 10:57 Go to previous messageGo to next message
Frank Schwarz is currently offline Frank Schwarz
Messages: 25
Registered: July 2009
Location: Dresden
Junior Member
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(...))
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
Re: JPA QL Question [message #382518 is a reply to message #382515] Mon, 13 October 2008 12:37 Go to previous messageGo to next message
Markus KARG is currently offline Markus KARG
Messages: 44
Registered: July 2009
Member
"Frank Schwarz" <fs5@gmx.net> schrieb im Newsbeitrag
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
>
Re: JPA QL Question [message #382521 is a reply to message #382518] Tue, 14 October 2008 12:37 Go to previous messageGo to next message
Frank Schwarz is currently offline Frank Schwarz
Messages: 25
Registered: July 2009
Location: Dresden
Junior Member
Markus KARG wrote:

>> 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
Re: JPA QL Question [message #382523 is a reply to message #382521] Tue, 14 October 2008 13:43 Go to previous message
Markus KARG is currently offline Markus KARG
Messages: 44
Registered: July 2009
Member
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
Previous Topic:Fields of type java.io.InputStream
Next Topic:Cannot persist newly created object?
Goto Forum:
  


Current Time: Tue Jul 22 07:37:46 EDT 2014

Powered by FUDForum. Page generated in 0.02577 seconds