Re: [eclipselink-users] Join with additional Where condition

[James Sutherland <jamesssss@xxxxxxxxx>]

So you want your OneToMany to include the regionCode in the query to filter
the courses.  JPA only allows relationships to be on foreign keys
(JoinColumns), so does not support this.  EclipseLink does however, you can
use a DescriptorCustomizer to customize the mapping.  You can use
EclipseLink Expressions to set the OneToManyMapping's selectionCriteria to
an Expression that include the foreign key and the filter.

Ideally EclipseLink would have annotation/xml support for doing this, so
feel free to log a bug for that.

You could also just filter the result in your object model, as you seem to
be doing.


Joe Mc. wrote:
> 
> Hi together,
> 
> I have the following use case with a fictive example:
> - ManyToMany relation
> - Table User(id, name, city, ...)
> - Table Course(id, name, regionCode, ...)
> - Some courses are only for special regions (> 0, if regionCode = 0 is the
> course everywhere available)
> - FetchType.Eager
> - configuration through annotations
> 
> So, the select on User delivers all the users, with all the corresponding
> Courses. However, only the courses with the regionCode = 0 are wanted.
> Question:
> How is it possible to add additional where condition for the Joins? The
> wanted additional condition is
> c.regionCode = 0
> 
> The following query is unfortunately not successful
> entityManager.createQuery("SELECT u FROM User u, Course c 
>                                        WHERE c.regionCode = 0 AND ((u.id =
> 1) OR (u.id = 2) OR ...)");
> 
> EclipseLink modifies the Query with the Outer-Joins and the conditon
> "c.regionCode = 0" does not fit any more (result is not as excepted)
> 
> My current solution is:
> - Use @PostLoad to remove the Courses from the User.courses property,
> which have course.regionCode != 0. But that is not really nice!
> 
> How is it possible to constrain the resulting set, ideally with the
> annotations.
> 
> Thanks in advance!
> 
> Best Regards
> 
> Mc.Joe
> 
> 
>  
> 


-----
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://www.nabble.com/Join-with-additional-Where-condition-tp23583002p23637279.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.