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

[Tom Ware <tom.ware@xxxxxxxxxx>]

Hi Joe,

  I am a bit surprised the query you list does a join at all.

  I would expect a query more like:

SELECT u FROM User u join u.courses c WHERE c.regionCode = 0 AND ((u.id = 1) OR 
(u.id = 2) OR ...

  Have you tried something like that?

-Tom

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
>
>
>