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

[bht@xxxxxxxxxxxxx]

Hi Chris,

Thanks for that. It is amazing how many incident records the community
had to keep track of with regard to this single issue. I wasn't aware
of this one.

You did not mention the JPQL issue. Where is this tracked? As I wrote,
I prefer to stay clear of bad practices in standard use cases such as
this common outer join application.

This seems to be a bigger one as it is on the specification level,
where EL would not have a compliance issue. Is that why you do not
mention it?

Many thanks.

Regards

Bernard

On Mon, 03 May 2010 15:13:10 -0400, you wrote:

>Hello Bernard,
>
>With respect to case and native queries, you will find that EclipseLink 
>has worked around the problem of databases returning different cases 
>through bug 299926
>https://bugs.eclipse.org/bugs/show_bug.cgi?id=299926
>If using a recent EclipseLink nightly build, you should not see case 
>issues when using native SQL (unless your database is case sensitive). 
>
>Best Regards,
>Chris
>
>On 03/05/2010 3:03 PM, bht@xxxxxxxxxxxxx wrote:
>> 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
>>>     
>>
>> _______________________________________________
>> eclipselink-users mailing list
>> eclipselink-users@xxxxxxxxxxx
>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>>