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

[bht@xxxxxxxxxxxxx]

Hi Tom,

to use "EclipseLink native API and wait for spec-direction before
changing our JPQL parser" as you write is exactly what I would
suggest.

My offer is to provide a testcase that works out of the box, that
already includes an outer join query and all the data to make it work.
Then you could enhance it with an EclipseLink native API structure. 

Because you have the knowledge in the native EclipseLink API area. I
wanted to make it a little easier for you.

Plus this approach allows us to produce the same query result as what
we would be expecting in the hypothetical case where we already had a
JPA change in place which of course we don't

I am not suggesting any changes in EclipseLink at all.

Please let me know what you think.

Best regards

Bernard




On Fri, 07 May 2010 08:33:09 -0400, you wrote:

>Hi Bernard,
>
>   I suspect that a JPQL update would require a fairly big JPQL grammar change. 
>  We are hesitant to make big changes to our JPQL implementation because the 
>likelihood of maintaining backward compatibility when the next JPA specification 
>comes out is low since although we contribute the the spec, we are only one of 
>the groups that is involved and inevitably there will be some changes in what we 
>have come up with.  Additionally, large changes are at a risk of affecting our 
>TCK compatility.
>
>   For issues like this, we tend to try to address them with EclipseLink native 
>API and wait for spec-direction before changing our JPQL parser.  If you are 
>interested in using EclipseLink-native API we can work through an example and 
>see if we can produce what you are looking for with the current code, or if 
>there are some enhancement requests that need to be filed.
>
>Let me know,
>Tom
>
>bht@xxxxxxxxxxxxx wrote:
>> Tom,
>> 
>> Since I asked you for support to approach the spec group I ow you a
>> big Thank You for your initiative. It has been a pleasure to see this
>> unfold to its current stage.
>> 
>> I am offering to provide a standalone testcase that works with a
>> genuine JPA query that returns a DTO list, using a left join without
>> the sub-condition.
>> 
>> You would then be able to experiment with the testcase, effectively
>> trying to plug in the sub-condition into the join.
>> 
>> Ideally, the result would still be the same DTO list format, except
>> the number of rows would be different. The aim of all this is to get a
>> really good picture of the result that the JPA enhancement should
>> finally produce. The enhanced JPA could then replace your plug-in
>> solution seamlessly.
>> 
>> How does this sound to you?
>> 
>> 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
>> 
>> _______________________________________________
>> 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