| How to use outer joins in named query to calculate aggregate values? [message #729538] |
Mon, 26 September 2011 13:00  |
 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
|
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.02069 seconds
] 
Search
Help
Register
Login
Home
