0

I spent almost all morning searching, but couldn't find any answer.

I have scenario where I have to JOIN with ManyToMany relationship with an couple of extra conditions (on parent class) to limit the rows for joins.

I'm looking for the association to generate the query on below lines.

Select * 
from User u
left outer join user_roles ur
      on u.user_id = ur.user_id
     and u.department in ('Sales', 'Services')
     and u.city in ('New york', 'london')
left outer join roles r
      on ur.role_id = r.role_id
where
// conditions

The department, city fields are in the parent class (User).

 ----------------
User.java

@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(name = "user_roles", 
    joinColumns = {
        @JoinColumn(name = "user_id", referencedColumnName = "user_id", nullable = false, updatable = false) }, 
    inverseJoinColumns = {
        @JoinColumn(name = "role_id", referencedColumnName = "role_id", nullable = false, updatable = false) })
 private Set<Roles> roles;

 @Id
 @Column(name = "user_id")
 private Integer userId;

 @Column(name = "department")
 private String department;

 // other fields
 // setters and getters
 ---------------------------------

 Roles.java

 @ManyToMany(mappedBy = "", fetch = FetchType.LAZY)
 private Set<User> users = new HashSet<>();

 //other fields
 //setters and getters

 --------------------------------

Also, the department names ('Sales', 'Services') along with city ('New York', 'London') need to parameterized. i.e., these will change based on request.

I tried using (1) @FilterDef (2) @WhereJoinTable (3) @Where None of them seem to work.

Srinivas Lakshman
  • 469
  • 2
  • 4
  • 21

0 Answers0