[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
Re: [eclipselink-users] Conditional LEFT JOIN in JPQL
|
I appologize for my lack of understanding but...
How will the results of the following queries be different (assuming a mapped
relationship between dealer and vehicle using v.dealer_id = d.dealer_id as the
foreign key relationship)?
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 d.name, count(v.id) FROM dealer d LEFT OUTER JOIN d.vehicles v where
v.type = 'New' or v.type isnull GROUP BY d.name
-Tom
bht@xxxxxxxxxxxxx wrote:
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
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
