control select joins with EJBQL

[Andrus Adamchik <an...@objectstyle.org>]

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

Re: control select joins with EJBQL

[Andrus Adamchik <an...@objectstyle.org>]

On Jun 12, 2007, at 8:35 AM, Andrus Adamchik wrote:

>  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']

Umm... looking at the SQL, there's still a bug - the condition must  
be "(t0.ARTIST_ID = t2.ARTIST_ID)" in the second join. Coincidentally  
this particular query still works, but I need to fix the translator.  
Still the point remains - we now have a great deal of control over  
the joins.

Andrus

Re: control select joins with EJBQL

[Mike Kienenberger <mk...@gmail.com>]

Andrus,

There are some unit tests for outer joins that I created while I was
doing my own patch.
Maybe these can be reused.   I'm pretty sure they're included in the
outer join jira issue.

On 6/12/07, Andrus Adamchik <an...@objectstyle.org> wrote:
> 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
>
>
>
>
>
>