Home » Eclipse Projects » EclipseLink » JPA QL Question
|
| Re: JPA QL Question [message #381693 is a reply to message #381691] |
Fri, 10 October 2008 07:38   |
 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 #382518 is a reply to message #382515] |
Mon, 13 October 2008 16:37 |
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 16:37 |
Frank Schwarz
Messages: 26
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 17:43 |
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
|
|
|
Goto Forum:
Current Time: Thu Apr 25 15:48:30 GMT 2024
Powered by FUDForum. Page generated in 0.03060 seconds |
] 
Search
Help
Register
Login
Home
