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

[Vidas <vidas@xxxxxxxxxx>]

Hi, Tom,

  Totally agree with Bernard. There is no reason to have a foreign key relationship between two entities without mapping for that relationship defined (although I have one :-) but that's my special case). There is a need to have the ability to address a subset of related records in LEFT JOIN table, i.e. filter records in joining table before applying a relationship. That's what is doing "ON" keyword in native SQL. Bernard's examples are quite simple and clear, how it works. I have a bit more complex:

  Say application has the ability to dynamicaly extend table structure, i.e. virtualy describe additional columns in a table. For example we have a table:

[Country]

String name

Integer id

Customer wants to extend this table to add some extra fields, say "language" and "nationality". Such extra data is stored in a table:

[ExtCharFields]

Integer country_id

String fieldName

String fieldValue

Integer id

So, to present a table for viewing/editing, native SQL would look like this:

SELECT c.name, e1.fieldValue AS language, e2.fieldValue AS nationality

  FROM Country c

  LEFT JOIN ExtCharFields e1 ON c.id=e1.country_id  AND e1.fieldName = "language"

  LEFT JOIN ExtCharFields e2 ON c.id=e2.country_id  AND e2.fieldName = "nationality"

With this single SQL I get all I need and also can do any kind of sorting, paging, filtering results and etc., but I don't have any object/relational mapping, even can't use @SqlResultSetMapping, because query is dynamic. I could do this in JPQL, if it will support "ON" clause.

Hope I was clear enough to illustrate the problem ;-)

2010/5/4 <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

--
Sincerely,
Vidas