Re: [eclipselink-users] Potential Bug in [EL 2.0] CriteriaQuery with in-

[Michael Simons <michael.simons@xxxxxxxxxxx>]

Hello James,

James Sutherland schrieb:
> Please log a bug for having support for IN on object relationships.
> 
> Some other solutions are,
> - use a outer join on the "claims" relationship
That's no good idea, because it would blow up the result set even more. I have to admit that
there are already some joins that I ommit here for better readability.

> - define a duplicate basic mapping for the foreign key, and do the IN on
> this
Sorry, I don't understand what you mean by this, could you post an example, please?

Thanks, Michael

> 
> 
> 
> Michael Simons wrote:
>> Hello,
>>
>> EclipseLink 2.0 generates wrong SQL from a CriteriaQuery that contains an
>> in-operator.
>>
>> The model in use:
>> Scenario <1:*> Vehicle <*:1> Claim
>>
>> A Scenario contains one or more Vehicles. A Vehicle has assigned no or one
>> Claim.
>>
>> I need a query to return the vehicles within a given scenario having
>> either no claim assigned or
>> on that is in a given list of claims.
>>
>> The first CriteriaQuery I wrote was
>>   CriteriaBuilder cfab = em.getCriteriaBuilder ();
>>   CriteriaQuery<Vehicle> cq = cfab.createQuery (Vehicle.class);
>>   Root<Vehicle> vehicle = cq.from (Vehicle.class);
>>   cq.where (cfab.and (cfab.equal (vehicle.get("scenario"), scenario),
>>     cfab.or (vehicle.get ("claim").in (claims),
>> vehicle.get("claim").isNull ())));
>>   List<Vehicle> result = em.createQuery(cq).getResultList();
>>
>> That did not work, I got the following exception:
>> Exception [EclipseLink-6075] (Eclipse Persistence Services -
>> 2.0.0.v20091127-r5931):
>> org.eclipse.persistence.exceptions.QueryException
>> Exception Description: Object comparisons can only use the equal() or
>> notEqual() operators.
>> Other comparisons must be done through query keys or direct attribute
>> level comparisons.
>>
>> So I changed the "where"-line to:
>> cq.where (cfab.and (cfab.equal (vehicle.get("scenario"), scenario),
>>     cfab.or (vehicle.get ("claim").get("id").in (claimkees),
>> vehicle.get("claim").isNull ())));
>> // claimkkees contains only the integer keys of the claims, now
>>
>> This time it works but it's not correct, because the join of the claim
>> table does not take into
>> account.
>> SELECT t1.vehicle_id, ...
>> FROM claim t0, vehicle t1
>> WHERE (((t1.scenario_id = ?)
>>   AND ((t0.claim_id IN (?, ...))
>>    OR (t1.claim_id IS NULL)))
>>   AND (t0.claim_id = t1.claim_id))
>>
>> So either I'm not correctly building the condition or EL does not map it
>> correctly.
>> Can anybody tell me what's the matter, please?
>>
>>
>> Kind Regards, Michael
>>
> 
> 
> -----
> http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland 
> http://www.eclipse.org/eclipselink/
>  EclipseLink ,  http://www.oracle.com/technology/products/ias/toplink/
> TopLink 
> Wiki:  http://wiki.eclipse.org/EclipseLink EclipseLink , 
> http://wiki.oracle.com/page/TopLink TopLink 
> Forums:  http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink , 
> http://www.nabble.com/EclipseLink-f26430.html EclipseLink 
> Book:  http://en.wikibooks.org/wiki/Java_Persistence Java Persistence