We had extensive discussions with Mike K. on advanced join semantics
(and its limitations) in Cayenne. Here is a solution to controllable
joins coming from the JPA work. Let's say we are selecting artists
who have both paintings "P1" and "P2". You can't do it with
SelectQuery, as Cayenne would remove joins that it thinks are
"redundant".
String ejbql = "SELECT a "
+ "FROM Artist a JOIN a.paintingArray b JOIN a.paintingArray c "
+ "WHERE b.paintingTitle = 'P1' AND c.paintingTitle = 'P2'";
List artists = context.performQuery(new EJBQLQuery(ejbql));
This generates the following SQL - something you can't do with
SelectQuery:
SELECT t0.ARTIST_NAME AS ARTIST_NAME, t0.ARTIST_ID AS ARTIST_ID,
t0.DATE_OF_BIRTH AS DATE_OF_BIRTH
FROM ARTIST AS t0
INNER JOIN PAINTING AS t1 ON (t0.ARTIST_ID = t1.ARTIST_ID)
INNER JOIN PAINTING AS t2 ON (t1.ARTIST_ID = t2.ARTIST_ID)
WHERE t1.PAINTING_TITLE = ? AND t2.PAINTING_TITLE = ? [bind: 'P1',
'P2']
This is based on the code that I checked in last night. Note that it
works in "Cayenne classic" environment (not just JPA). I am still
working on handling and testing various edge cases, but the syntax
above works already.
Andrus
