title: Spring Data JPA - Using @Query with Many to Many Relationships / Join Table
body: <p>I have a User entity with a many-to-many relationship with a Role entity.</p>
  
  <pre><code>    @Entity
  @Table(name = "auth_user")
  public class OAuthUser {
  
      // @Autowired
      // @Transient
      // private PasswordEncoder passwordEncoder;
      //
      @Id
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      private Integer id;
  
      @Column(name = "username")
      private String userName;
  
      @Column(name = "password")
      @JsonIgnore
      private String password;
  
      @Column(name = "first_name")
      private String firstName;
  
      @Column(name = "last_name")
      private String lastName;
  
      @Column(name = "email")
      private String email;
  
      @Column(name = "is_enabled")
      private boolean isEnabled;
  
      /**
       * Reference:
       * https://github.com/nydiarra/springboot-jwt/blob/master/src/main/java/com/nouhoun/springboot/jwt/integration/domain/User.java
       * Roles are being eagerly loaded here because they are a fairly small
       * collection of items for this example.
       */
      @ManyToMany(fetch = FetchType.EAGER)
      @Fetch(value = FetchMode.SUBSELECT)
      @JoinTable(name = "user_role", joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "role_id", referencedColumnName = "id"))
      private List&lt;Role&gt; roles;
  
      @ManyToMany(fetch = FetchType.EAGER)
      @Fetch(value = FetchMode.SUBSELECT)
      @JoinTable(name = "user_properties", joinColumns = @JoinColumn(name = "AuthID", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "PropertyID", referencedColumnName = "id"))
      private List&lt;Property&gt; properties;
  </code></pre>
  
  <p>I am using the Spring Data JPA repositories, and would to be able to create a custom <code>@Query</code> that returns a list of users based upon a particular role id. </p>
  
  <pre><code>    @Query("SELECT u FROM auth_user as u WHERE u.isEnabled AND u.id IN"
          + " (SELECT r.user_id FROM user_role as r WHERE r.role_id = ?1)")
  public List&lt;OAuthUser&gt; findByRole(int roleID);
  </code></pre>
  
  <p>The code above results in the error <code>auth_user is not mapped</code>. I do understand why I am getting the error; the framework is using the entity names (OAuthUser) rather than the table (auth_user) to perform the query. This would ordinarily not be a problem, except for there is no entity for <code>user_role</code>; it is simply a join table with two columns: 'user_id' and 'role_id'. </p>
  
  <p>What is the appropriate way to achieve this?</p>
  
  <p>Thanks.</p>