control select joins with EJBQL

From: Andrus Adamchik (andru..bjectstyle.org)
Date: Tue Jun 12 2007 - 01:35:21 EDT

  • Next message: Andrus Adamchik: "Re: control select joins with EJBQL"

    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



    This archive was generated by hypermail 2.0.0 : Tue Jun 12 2007 - 01:35:48 EDT