CODEWITHSUNDEEP
mysqlspring-bootjpa
Pramod Dhananjaya
Pramod Dhananjaya

Reputation: 43

Spring Boot JPA query for retrieve data from many to many Relationship Table

I need to retrieve users from the users table where role_id =2. This is the relationship between entities. enter image description here

Below MySQL query works perfectly.And Retrive users that role_id = 2.

select * from users INNER JOIN user_roles ON users.id = user_roles.user_id INNER JOIN roles ON roles.id = user_roles.role_id WHERE role_id='2';

But I need to do it in the spring boot application and this is the repository that implemented.

@Query("select usr from User usr INNER JOIN user_roles ur "+
        "ON usr.id = ur.user_id INNER JOIN Role roles " +
        "ON roles.id = ur.role_id WHERE ur.role_id='2' AND usr.department=(:depId)")
List<User> findAll(@Param("depId") Long depId);

But user_roles is not an entity and the above query does not work. Is there a solution or any other way to achieve my goal?

Upvotes: 1

Views: 962

Answers (1)

O. Jones
O. Jones

Reputation: 108641

This is the query you try to use from inside your program.

    SELECT usr
      FROM User usr 
INNER JOIN user_roles ur ON usr.id = ur.user_id 
INNER JOIN Role roles ON roles.id = ur.role_id 
     WHERE ur.role_id='2' 
      AND usr.department=(:depId)

usr, which you mention in your SELECT clause, is not a column in any of your tables. Maybe you should try usr.* instead to get all columns from the matching User table.

And, you don't need to join the Role table to get the role_id; it's in the user_roles table. So, try this.

    SELECT usr.*
      FROM User usr 
INNER JOIN user_roles ur ON usr.id = ur.user_id 
     WHERE ur.role_id='2' 
      AND usr.department=(:depId)

Pro tip It's very important to be able to look at queries embedded in programs and reason about them. It's worth your trouble to format them carefully in your source code.

Upvotes: 1

Related Questions