Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » How to use outer joins in named query to calculate aggregate values?
How to use outer joins in named query to calculate aggregate values? [message #729538] Mon, 26 September 2011 13:00 Go to next message
Wolfgang  is currently offline Wolfgang
Messages: 10
Registered: July 2009
Junior Member
I am curious whether I can make eclipselink (2.3) use outer joins in named-queries to generate SQL-statements involving aggregation. My example: I want to display the number of children and a max-value for a given set of parents in a hierarchical structure
rds (1:n) rdsresult (1:n) sio

I use the named query
<named-query name="findRDSCountAndMax"><query>
select sio.rdsResult.rds.id, count(sio.id), max(sio.rds.resultCode) from sio where sio.rdsResult.rds.id in :theIds group by sio.rdsResult.rds.id
</query></named-query>

At runtime this is translated into the following SQL:
a)
SELECT t0.ID, COUNT(t2.ID), MAX(t1.RESULTCODE) FROM TRDS t0, TSIO t2, TRDSResult t1 WHERE ((t1.RDS_ID IN (?)) AND ((t1.ID = t2.RDS_RESULT_ID) AND (t0.ID = t1.RDS_ID))) GROUP BY t0.ID
bind => [81]

this is close but not equivalent to the query that really should be used
b)
SELECT t0.ID, COUNT(t2.ID), MAX(t1.RESULTCODE) FROM TRDS t0 outer join TRDSResult t1 on (t0.ID = t1.rds_ID))) outer join TSIO t2 on t1.ID = t2.rds_RESULT_ID WHERE (t0.ID IN (?)) GROUP BY t0.ID
bind => [81]

Where the result of a) is "empty" b) returns (81, 0, null)

Additionally it would be nice to include a conversion from "null" to -1 for the max field. For mysql this can be done with colasce, such that the sql-statement becomes
c) SELECT t0.ID, COUNT(t2.ID), coalesce(MAX(t1.RESULTCODE),-1)...

I tried to add "<join-fetch>outer</join-fetch>" to the (1:n) mappings but that did not help.

Now I can create a native-query with the correct code, but if I can make eclipselink use outer joins then I would prefer that - any hints?

regards
Wolfgang
Re: How to use outer joins in named query to calculate aggregate values? [message #729705 is a reply to message #729538] Mon, 26 September 2011 19:47 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Outer joins are supported in JPQL using "left join".

select rds.id, count(sio.id), max(rds.resultCode) from sio sio left join sio.rds rds where rds.id in :theIds group by rds.id


James : Wiki : Book : Blog : Twitter
Re: How to use outer joins in named query to calculate aggregate values? [message #730052 is a reply to message #729705] Tue, 27 September 2011 14:44 Go to previous message
Wolfgang  is currently offline Wolfgang
Messages: 10
Registered: July 2009
Junior Member
thanks for pointing me in the right direction - in addition to using "left join" I had to navigate the relationship from parent to children instead of from child to parent and then the result will be as wanted. Case closed Smile

correct result:
select rds.id, count(sio.id), max(rdsResult.resultCode) from RDS rds left join rds.rdsResults rdsResult left join rdsResult.sios sio where rds.id in :theIds group by rds.id

wrong result (obviously):
select rds.id, count(sio.id), max(rdsResult.resultCode) from SIO sio left join sio.rdsResult rdsResult left join redsResult.rds rds where rds.id in :theIds group by rds.id
Re: How to use outer joins in named query to calculate aggregate values? [message #730062 is a reply to message #729705] Tue, 27 September 2011 14:44 Go to previous message
Wolfgang is currently offline Wolfgang
Messages: 12
Registered: July 2009
Junior Member
thanks for pointing me in the right direction - in addition to using "left join" I had to navigate the relationship from parent to children instead of from child to parent and then the result will be as wanted. Case closed :)

correct result:
select rds.id, count(sio.id), max(rdsResult.resultCode) from RDS rds left join rds.rdsResults rdsResult left join rdsResult.sios sio where rds.id in :theIds group by rds.id

wrong result (obviously):
select rds.id, count(sio.id), max(rdsResult.resultCode) from SIO sio left join sio.rdsResult rdsResult left join redsResult.rds rds where rds.id in :theIds group by rds.id
Previous Topic:Re: sync error with eclipselink
Next Topic:Adding a mapped-superclass via orm.xml fails
Goto Forum:
  


Current Time: Thu Sep 18 06:06:50 GMT 2014

Powered by FUDForum. Page generated in 0.10995 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software