Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] Conditional LEFT JOIN in JPQL 
Community Members,
Please vote on this enhancement ASAP if it is important to you. We are in the middle of planning our next major release and votes will be an important criteria in choosing the features to implement. At the moment, this feature enhancement does not have enough votes to guarantee it will be investigated for that release. 

-Tom

bht@xxxxxxxxxxxxx wrote:

Hi Tom,

Would you be able to provide us with an update on this? How is this
trackable?

Meanwhile the cases where we need this enhancement keep piling up.

It would be encouraging to see that Eclipselink leads the development
in this area. We already have an issue filed for an EL specific
solution:

https://bugs.eclipse.org/bugs/show_bug.cgi?id=312146
Implement conditional LEFT JOIN in JPQL

Best regards,

Bernard






On Thu, 06 May 2010 15:58:32 -0400, you wrote:


Hi Vidas,

  Thanks for the lesson.
With your help and Bernards', I have managed to get this feature on an initial list for consideration for the next JPA specification. The spec is still in the very early stages, and I do not have any idea where it fits in terms of priority compared to the other items being considered.
If you are interested in an EclispeLink-native-api solution, I can do some experimentation to see what EclipseLink can provide. The basic idea is we would define something called a OneToOneQueryKey using the fields you want to join ON and then query across that query key. 

Let me know if this kind of solution is of interest to you,
Tom

Vidas wrote:

Hi, Tom,

2010/5/5 Tom Ware <tom.ware@xxxxxxxxxx <mailto:tom.ware@xxxxxxxxxx>>

    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 <http://d.name>, count(v.id <http://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
    <http://d.name>

    2. SELECT d.name <http://d.name>, count(v.id <http://v.id>) FROM
    dealer d LEFT OUTER JOIN d.vehicles v where v.type = 'New' or v.type
    isnull GROUP BY d.name <http://d.name>


Second query will be missing dealers, who only have old vehicles.

Taking other Bernards example:
1. SELECT p.name <http://p.name/>, f.id <http://f.id/> FROM product p LEFT OUTER JOIN product_favorite f 
ON (p.id <http://p.id/> = f.product_id AND f.user_id = :userId)
2. SELECT p.name <http://p.name/>, f.id <http://f.id/> FROM product p LEFT OUTER JOIN p.product_favorite f 
WHERE f.user_id = :userId OR f.user_id isnull
Second query will be missing products, which are favorites only to other users. 

And my case:
SELECT c.name <http://c.name/>, e1.fieldValue AS language, e2.fieldValue AS nationality 
  FROM Country c
LEFT JOIN ExtCharFields e1 ON c.id <http://c.id/>=e1.country_id AND e1.fieldName = "language" LEFT JOIN ExtCharFields e2 ON c.id <http://c.id/>=e2.country_id AND e2.fieldName = "nationality" 

It isn't possible to write JPQL query at all


--
Sincerely,
Vidas


------------------------------------------------------------------------

_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

Back to the top