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

[bht@xxxxxxxxxxxxx]

Hi Tom,

Thanks very much for your offer to help. I will have to get back to
you on the EclipseLink options later. Meanwhile I am trying to answer
your question regarding your spec group query.

>   Is there a compelling reason to have a foreign key relationship between two 
>entities and no mapping for that relationship defined?  If so, what is it?  In 
>order to get much traction in the JPA group, a good argument will have to be 
>made in that regard.

My answer is no, or I don't know, because Conditional LEFT OUTER JOIN
does not require this per se.

The issues that users are having with LEFT OUTER JOIN is that the "ON"
keyword is missing from JPQL. "ON" accomodates conditions in addition
to the natural join.

While JPQL can execute a query without "ON" by following the mapped
relationships, there are other general use cases of the "ON" keyword
that JPA cannot express. Even a correlated sub query could be
atttached to the "ON" clause.

I hope that the following simple cases make it clear what kind of
dilemma we are facing.

1)
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

2)
SELECT p.name, f.id FROM product p LEFT OUTER JOIN product_favorite f
ON (p.id = f.product_id AND f.user_id = :userId)

The aim is to execute such simple queries in JPQL which is impossible
with JPA 2.0.

Thanks again for your help.

Regards,

Bernard