(Spring Data JPA) combine @EntityGraph and Specification in to generate a single JOIN

Question

I would like to combine @EntityGraph and Specification in Spring Data JPA to generate a single JOIN clause instead of separate ones.

@Entity
public class LoginUser {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(nullable = false)
    private String name;

    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(
        name = "user_role",
        joinColumns = @JoinColumn(name = "user_id"),
        inverseJoinColumns = @JoinColumn(name = "role_id")
    )
    private List<Role> roleList;
}

@Entity
public class Role {
    @Id
    private Integer id;

    @Column(nullable = false)
    private String name;
}

public interface LoginUserRepository extends JpaRepository<LoginUser, String>,
        JpaSpecificationExecutor<LoginUser> {

    @EntityGraph(attributePaths = {"roleList"})
    public Page<LoginUser> findAll(Specification<LoginUser> spec, Pageable pageable);
}

public class LoginUserSpecification {
   static public Specification<LoginUser> equalsRole(String role) {
        return (root, query, builder) -> builder.equal(
                    root.join("roleList", JoinType.LEFT).get("name"), role);
    }
}

public class LoginUserDetailsService {
    public Page<LoginUser> getAccounts(Pageable pageable, UserSearchForm form) {
        return loginUserRepository.findAll(
                Specification.where(LoginUserSpecification.equalsRole(form.getRole())),
                pageable);
    }
}

Generated SQL:

select
    l1_0.id,
    l1_0.name,
    r2_0.user_id,
    r2_1.id,
    r2_1.name
from
    login_user l1_0
left
    join ( /* JOIN clause generated by Specification */
        user_role r1_0
        join roles r1_1 on r1_1.id = r1_0.role_id
    )
    on l1_0.id = r1_0.user_id
left
    join ( /* JOIN clause generated by @EntityGraph */
        user_role r2_0
        join roles r2_1 on r2_1.id = r2_0.role_id
    )
    on l1_0.id = r2_0.user_id
where
    r1_1.name = 'ROLE_ADMIN'

Expected SQL:

select
    l1_0.id,
    l1_0.name,
    r2_0.user_id,
    r2_1.id,
    r2_1.name
from
    login_user l1_0
left
    join ( /* JOIN clause generated by @EntityGraph */
        user_role r2_0
        join roles r2_1 on r2_1.id = r2_0.role_id
    )
    on l1_0.id = r2_0.user_id
where
    r2_1.name = 'ROLE_ADMIN'

Is it possible to merge the duplicated JOIN clauses into a single one as described above?

root.join("roleList", JoinType.LFET).get("name"), role);

Ideally, JoinType.INNER is appropriate when specifying search conditions. However, I chose LEFT in the hope that it might optimize the query.

Answer 1

I couldn't find a way to do it, so I changed the SQL and dealt with it. Specifically, I changed the specification to the exists clause.

select
    l1_0.id,
    l1_0.name,
    r2_0.user_id,
    r2_1.id,
    r2_1.name
from
    login_user l1_0
left
    join ( /* JOIN clause generated by @EntityGraph */
        user_role r2_0
        join roles r2_1 on r2_1.id = r2_0.role_id
    )
    on l1_0.id = r2_0.user_id
where
    exists (
        select
            1
        from
            login_user l2_0
            join (
                user_role r2_0
                join roles r2_1 on r2_1.id = r2_0.role_id
            )
            on l2_0.id = r2_0.user_id
        where
            l1_0.id = l2_0.id
            and
            r2_1.name in (
                'ROLE_ADMIN'
            )
    )