One thing that helped me (I'm not exactly sure if it applies here because I can't tell if this is meant to be a one-to-one or one-to-many type relations between your entities) is that I had to quit thinking of my data in terms of tables (sets). In your case, do you really need to retrieve all three fields? Or, can you rework your exercise to think of it more like this -
SELECT a FROM A a WHERE a.b.c.id = ?1 The key difference is that with JPA/JPQL, often times, expressing joins in your query are unnecessary... In fact, if you are trying to express joins, it is usually a good sign that you are thinking of the problem in terms of SQL rather than JPA/JPQL. Just my two cents. -Wes On Fri, Sep 11, 2009 at 2:00 PM, Daryl Stultz <[email protected]> wrote: > Hey, I'm getting crazy on this. I'm trying to convert SQL to JPQL. Suppose I > have this: > select a.p1, b.p1, c.p1 > from a > join b on b.aId = a.id > left join c on c.bId = b.id > and c.aId = a.id > > maybe it converts to something like this: > > select a.p1, b.p1, c.p1 > from A as a > join a.b as b > left join b.c > > but I'm left without the second join criteria (and I get too much). I > thought this might work: > > select a.p1, b.p1, c.p1 > from A as a > join a.b as b > left join b.c > where (c.id is null or c.aId = a.id) > > but I get too little (no rows with null c) > > Is is possible to pull this off in JPQL? (Actually I can't get it to work in > SQL either, I'm using PostgreSQL). This isn't an exact representation of > what I'm trying to do but hopefully you see the issue (where/how to put > additional criteria on left join). > > OpenJPA 1.2.1 > > -- > Daryl Stultz > _____________________________________ > 6 Degrees Software and Consulting, Inc. > http://www.6degrees.com > mailto:[email protected] > -- Wes Wannemacher Head Engineer, WanTii, Inc. Need Training? Struts, Spring, Maven, Tomcat... Ask me for a quote!
