title: how to construct spring JPA/JPQL query in JpaRepository
body: <p>I've got a DB structure like this:</p>
  
  <pre><code>topics 1:n posts  1:n comments and users n:n roles.
  </code></pre>
  
  <p>I want to get all comments that user have an access. </p>
  
  <p>Giving access for me means (when I create post object I automaticly create role object called PostName with prefix role_comment_ e.g. post called abc have role called: role_comment_abc)</p>
  
  <p>Now I try to create jpa/jpql query like below: </p>
  
  <pre><code>find all comments by User where user_id is =:1 and role_name contaings =:2
  findByUserIdAndRoleNameContaining(Integer userId, String roleName);
  </code></pre>
  
  <p>This is how my User, Role and comment tables looks like:</p>
  
  <p>Roles table:</p>
  
  <pre><code>@Entity
  @Table(name = "roles")
  public class Role {
  
      @Id
      private Integer id;
      private String name;
  
      @ManyToMany(mappedBy = "roles")
      private Set&lt;User&gt; users = new HashSet&lt;&gt;();  
  </code></pre>
  
  <p>Users and user_role tables:</p>
  
  <pre><code>@Entity
  @Table(name = "users")
  public class User {
  
  @Id
  private Integer id;
  private String name;
  
  @ManyToMany(fetch = FetchType.EAGER, cascade = { CascadeType.ALL })
  @JoinTable(
          name = "user_role",
          joinColumns = { @JoinColumn(name = "user_id") },
          inverseJoinColumns = { @JoinColumn(name = "role_id") }
  )
  private Set&lt;Role&gt; roles = new HashSet&lt;&gt;();
  </code></pre>
  
  <p>And this is comments table:</p>
  
  <pre><code>@Entity
  @Table(name = "comments")
  public class Comments{
  
  @Id
  private Integer id;
  private String name;
  private String description;
  
  @ManyToOne(optional = false)
  @JoinColumn(nullable = false, name = "user_id")
  private User user
  </code></pre>
  
  <p>Unfortunetly if I create query in JpaRepository called:</p>
  
  <pre><code>List&lt;Comments&gt; findByUserId(Integer id); 
  </code></pre>
  
  <p>If i'm not wrong it will print comments created by that specific user.</p>
  
  <p>So what I really want to achive? Let me show you this on example data:</p>
  
  <p>roles:</p>
  
  <pre><code>100;"role_comment_ab"
  101;"role_comment_cd"
  102;"role_comment_ef"
  103;"something_else"
  </code></pre>
  
  <p>Comments in post with name ab:</p>
  
  <pre><code>1;"Test1";"Test description";10
  2;"Test2";"Test description";10
  </code></pre>
  
  <p>comments in post with name cd:</p>
  
  <pre><code>3;"Test3";"Test description";10
  4;"Test4";"Test description";10
  </code></pre>
  
  <p>comments in post with name ef:</p>
  
  <pre><code>5;"Test5";"Test description";10
  6;"Test6";"Test description";10
  </code></pre>
  
  <p>users:</p>
  
  <pre><code>10;"Thomas" (logged user)
  11;"John"
  </code></pre>
  
  <p>users_roles:</p>
  
  <pre><code>10;100
  11;101
  10;102
  10;103
  </code></pre>
  
  <p>input:</p>
  
  <pre><code>findByUserIdAndRoleNameContaining(10, "role_comment_");
  </code></pre>
  
  <p>output:</p>
  
  <pre><code>   1;"Test1";"Test description";10
     2;"Test2";"Test description";10
     5;"Test5";"Test description";10
     6;"Test6";"Test description";10
  </code></pre>
  
  <p>I'm really out of clue how my query should look like. Pleast atleast give me a small hint.</p>
  
  <p>UPDATE:
  After adding @Bohdan Petrenko solution:</p>
  
  <pre><code>@Query("select c from Comment c join c.user u join u.roles r where u.id = :userId and lower(r.name) like lower(:roleName)")
  List&lt;Comment&gt;  findByUserIdAndRoleNameContaining(@Param("userId") Integer userId, @Param("roleName") String roleName);
  
  roleName = "%" + roleName.trim() + "%";
  </code></pre>
  
  <p>I noticed that this solution prints all comments if @Param roleName contains "roleName" String. </p>
  
  <p>So if I have role_postName1 and role_postName2
  it prints:</p>
  
  <pre><code>comment1FromPost1
  comment2FromPost1
  comment1FromPost2
  comment2FromPost2
  comment1FromPost1
  comment2FromPost1
  comment1FromPost2
  comment2FromPost2
  </code></pre>
  
  <p>It would've be great to find solution to print comments from posts only if user have role called role_postName. </p>