Re: [eclipselink-users] Conditional LEFT JOIN in JPQL

[bht@xxxxxxxxxxxxx]

Hi Vide,

Re 1)

It should be possible.

However there are major limitations with conditional left join in JPQL
and this is a BIG problem.

For example, in a product list display, alongside with each list item,
we would want an indicator showing whether the item exists in the
user's list of favorite items. An outer join clause with such a
condition defining the join is the only practical way to do this. This
is industry standard, except it is NOT supported in JPQL.

In JPQL, You cannot code outer join "ON" something other than what is
already obvious from relationships. In fact "ON" is not supported.

In SQL you can say:

SELECT d.name, count( v.id ) FROM dealer d LEFT OUTER JOIN vehicle v
ON v.dealer_id = d.dealer_id AND v.type = 'New' GROUP BY d.name

In JPQL, you only have the WHERE clause which limits your results,
making the outer join useless:

SELECT d.name, count( v.id ) FROM dealer d LEFT OUTER JOIN
d.vehicleList v WHERE v.type = 'New' GROUP BY d.name


PLease refer to:

http://old.nabble.com/LEFT-OUTER-JOIN---ON-clause-equivalent--td12638986.html#a12641034

One can work around this with a native query, but this is a really
lame suggestion because there are a number of problems with them -
they are not best practice. I found them very difficult to work with
in this scenario because of unexpected case sensitivity issues in the
query itself (not the data) when using @SqlResultSetMapping. You will
discover the whole glory when you get there:

http://forums.java.net/jive/thread.jspa?messageID=190092
    Request for input - TLE case sensitivity
    Outlines workarounds
https://glassfish.dev.java.net/issues/show_bug.cgi?id=3698
    Details workaround
https://glassfish.dev.java.net/issues/show_bug.cgi?id=600
https://glassfish.dev.java.net/issues/show_bug.cgi?id=1402
https://glassfish.dev.java.net/issues/show_bug.cgi?id=1442
https://glassfish.dev.java.net/issues/show_bug.cgi?id=1507
https://glassfish.dev.java.net/issues/show_bug.cgi?id=2046
https://glassfish.dev.java.net/issues/show_bug.cgi?id=887
https://glassfish.dev.java.net/issues/show_bug.cgi?id=832
https://glassfish.dev.java.net/issues/show_bug.cgi?id=1027

Unless fields are wrapped in quotes,
 Oracle returns fields in uppercase,
 Sybase returns them as they were defined,
 and Postgres returns them in lowercase.


 http://sql-info.de/en/postgresql/postgres-gotchas.html#1_2
     Unquoted object names fold to lower case (folding behavior)



Regards

Bernard



On Mon, 3 May 2010 01:39:31 -0700 (PDT), you wrote:

>
>Hello,
>
>  I have two questions about JOIN's in JPQL:
>1. Is it possible to write a conditional LEFT JOIN with JPQL, which in SQL
>looks like:
>
>SELECT a.*, b.*, c.*
>  FROM entityA a
>  LEFT JOIN entityB b ON a.id=b.a_id AND b.some_field="some_value"
>  LEFT JOIN entityC c ON a.id=c.a_id AND c.some_field="some_value"
>  WHERE some_other_post_conditions
>
>2. Can I use LEFT JOIN's in JPQL on entities, which don't have relationship
>annotations between each other?
>
>Thanks