[FIXED] How to find specific roles in many-to-many spring relationship

Issue

I have following entities

@Builder
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
@Entity
public class Role extends AbstractEntity {

  private String name;

  @ManyToMany(cascade = {CascadeType.PERSIST, CascadeType.MERGE},
  fetch = FetchType.EAGER)
  @JoinTable(
      name = "privilege_roles",
      joinColumns = {@JoinColumn(name = "role_id")},
      inverseJoinColumns = {@JoinColumn(name = "privilege_id")})
  @Exclude
  private Set<Privilege> privileges;

  @ManyToMany(
      cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType},
      fetch = FetchType.LAZY)
  @JoinTable(
      name = "user_roles",
      joinColumns = {@JoinColumn(name = "role_id")},
      inverseJoinColumns = {@JoinColumn(name = "user_id")})
  private Set<User> users;
}

@Builder
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
@Entity
public class Privilege extends AbstractEntity {

  private String name;

  @ManyToMany(cascade = {CascadeType.PERSIST, CascadeType.MERGE}, fetch = FetchType.EAGER)
  @JoinTable(
      name = "privilege_roles",
      joinColumns = {@JoinColumn(name = "privilege_id")},
      inverseJoinColumns = {@JoinColumn(name = "role_id")})
  private Set<Role> roles;
}

/** Model użytkownika */
@Builder
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
@Entity
public class User extends AbstractEntity {

  private String login;
  private String firstName;
  private String lastName;

  @ManyToMany(cascade = {CascadeType.PERSIST, CascadeType.MERGE}, fetch = FetchType.EAGER)
  @JoinTable(
      name = "user_roles",
      joinColumns = {@JoinColumn(name = "user_id")},
      inverseJoinColumns = {@JoinColumn(name = "role_id")})
 
  private Set<Role> roles;
}

Abstract entity consist of ID's and so on. My problem is to get priviliges from a specific user, can it be done with one query? At this point, I've divided it down into two steps

  Set<Role> findByUsers_Login(String login);

  Set<Privilege> getAllByRolesIdIn(Collection<@Nonnull Integer> roles_id);

How can I achieve my goal, if notice anything related to clean-code or a performance improvement, I would be grateful

HashSet<PrivilegeDto> authorities = new HashSet<>();
UserDto userDto = userService.findByLogin(login)
Set<Long> rolesId = roleService(findByUserId(userDto.getId().stream()
        .map(AbstractDto::getId)
        .collect(Collectors::toSet());
for (Long id : rolesId) {
    Set<PrivilegeDto> privilegesById = privilegeService.getPrivilegesById(id);
    authorities.addAll(privilegesById);
}

@Query(value = "SELECT P.NAME FROM USER_ROLES UR LEFT JOIN PRIVILEGE_ROLES PR ON PR.ROLE_ID = UR.ROLE_ID LEFT JOIN PRIVILEGE P ON PR.PRIVILEGE_ID = P.ID LEFT JOIN USER U ON U.ID = UR.USER_ID WHERE U.LOGIN = :login",nativeQuery = true)
    Set<Privilege> getAllMyPriviliges(@Param("login")String login);

Solution

I am having a hard time understanding why you need the second query. With the first one, you would get a set of Roles. The Role object already contains a set of Privileges which you can simply retrieve using the getter method as follows:

findByUsers_Login(login).stream()
    .flatMap(role -> role.getPrivileges().stream()).collect(Collectors.toSet());

Answered By - João Dias