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

[James Sutherland <jamesssss@xxxxxxxxx>]

Please log a bug for having support for IN on object relationships.

Some other solutions are,
- use a outer join on the "claims" relationship
- define a duplicate basic mapping for the foreign key, and do the IN on
this



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 
-- 
View this message in context: http://old.nabble.com/Potential-Bug-in--EL-2.0--CriteriaQuery-with-in-operator-tp27797926p27837464.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.