how to construct spring JPA/JPQL query in JpaRepository

Question

I've got a DB structure like this:

topics 1:n posts  1:n comments and users n:n roles.

I want to get all comments that user have an access.

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)

Now I try to create jpa/jpql query like below:

find all comments by User where user_id is =:1 and role_name contaings =:2
findByUserIdAndRoleNameContaining(Integer userId, String roleName);

This is how my User, Role and comment tables looks like:

Roles table:

@Entity
@Table(name = "roles")
public class Role {

    @Id
    private Integer id;
    private String name;

    @ManyToMany(mappedBy = "roles")
    private Set<User> users = new HashSet<>();  

Users and user_role tables:

@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<Role> roles = new HashSet<>();

And this is comments table:

@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

Unfortunetly if I create query in JpaRepository called:

List<Comments> findByUserId(Integer id); 

If i'm not wrong it will print comments created by that specific user.

So what I really want to achive? Let me show you this on example data:

roles:

100;"role_comment_ab"
101;"role_comment_cd"
102;"role_comment_ef"
103;"something_else"

Comments in post with name ab:

1;"Test1";"Test description";10
2;"Test2";"Test description";10

comments in post with name cd:

3;"Test3";"Test description";10
4;"Test4";"Test description";10

comments in post with name ef:

5;"Test5";"Test description";10
6;"Test6";"Test description";10

users:

10;"Thomas" (logged user)
11;"John"

users_roles:

10;100
11;101
10;102
10;103

input:

findByUserIdAndRoleNameContaining(10, "role_comment_");

output:

   1;"Test1";"Test description";10
   2;"Test2";"Test description";10
   5;"Test5";"Test description";10
   6;"Test6";"Test description";10

I'm really out of clue how my query should look like. Pleast atleast give me a small hint.

UPDATE: After adding @Bohdan Petrenko solution:

@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<Comment>  findByUserIdAndRoleNameContaining(@Param("userId") Integer userId, @Param("roleName") String roleName);

roleName = "%" + roleName.trim() + "%";

I noticed that this solution prints all comments if @Param roleName contains "roleName" String.

So if I have role_postName1 and role_postName2 it prints:

comment1FromPost1
comment2FromPost1
comment1FromPost2
comment2FromPost2
comment1FromPost1
comment2FromPost1
comment1FromPost2
comment2FromPost2

It would've be great to find solution to print comments from posts only if user have role called role_postName.

Answer 1

@Query("select t from Topic t 
        join t.user u 
        where u.id = :userId 
        and u.roles in :roleNames")
List<Topic>  findByUserIdAndRoleNameContainedIn(@Param("userId") Integer userId, @Param("roleNames") List<String> roleNames);

Answer 2

Please be more specific in your questions.

If I understood you correctly - You want to select Topics by user id and Role name. If yes you may try something like this:

@Query("select t from Topic t join t.user u join u.roles r where u.id = :userId and lower(r.name) like lower(:roleName)")
List<Topic>  findByUserIdAndRoleNameContaining(@Param("userId") Integer userId, @Param("roleName") roleName);

But you'll also need to change role name before passing it to the repository method findByUserIdAndRoleNameContaining:

roleName = "%" + roleName.trim() + "%";

UPDATE

This will work without any custom SQL and roleName modifications

List<Comment> findByUser_IdAndUser_Roles_NameContainingIgnoreCase(Integer userId, String roleName);

And also I don't understand the trouble you are faced with now. So, provide us some examples with a test data (from the data base), correct and incorrect expected results of the query. As I don't understand the reason of your problem I can't help you to solve it.