Hibernate. Predicate with two joins

Question

I have next relations:

CREATE TABLE IF NOT EXISTS users
(
    id                      BIGSERIAL PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS groups
(
    id         BIGSERIAL PRIMARY KEY
);

CREATE TABLE users_groups
(
    id         BIGSERIAL PRIMARY KEY,
    user_id    BIGINT REFERENCES users (id),
    group_id   BIGINT REFERENCES groups (id)
);

CREATE TABLE IF NOT EXISTS attempts
(
    id         BIGSERIAL PRIMARY KEY,
    user_id    BIGINT REFERENCES users (id)
);


@Entity
@Getter
@Setter
@Builder
@ToString
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToMany(mappedBy = "users", fetch = FetchType.LAZY)
    @ToString.Exclude
    private Set<Group> groups;

    @ToString.Exclude
    @OneToMany(mappedBy = "user", fetch = FetchType.LAZY)
    private List<Attempt> attempts;
}

@Entity
@Getter
@Setter
@Builder
@ToString
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "groups")
public class Group {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "users_groups",
            joinColumns = @JoinColumn(name = "group_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"))
    @ToString.Exclude
    private List<User> users;
}

@Getter
@Setter
@Entity
@Builder
@ToString
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "attempts")
public class Attempt {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ToString.Exclude
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id")
    private User user;
}

And AttemptSpecification which target of my question:

record AttemptSpecification(@NotNull Group group) implements Specification<Attempt> {

    @NotNull
    @Override
    public Predicate toPredicate(@NotNull Root<Attempt> root,
                                 @NotNull CriteriaQuery<?> query,
                                 @NotNull CriteriaBuilder builder) {

        Predicate predicate = builder.conjunction();
        List<Expression<Boolean>> exps = predicate.getExpressions();

        ....?
        
        return predicate;
    }
}

And I need to fetch data from attempts table by groups with JpaSpecificationExecutor::Page<T> findAll(@Nullable Specification<T> spec, Pageable pageable).

How to build Predicate with CriteriaBuilder for select attempts related to users which included in some group? How to join groups to users and users to attempts?

Answer 1

1. join - probably requires distinct, thus could be unusable due to pagination:

(root, cq, cb) -> {
    Root<Group> groups = cq.from(Group.class);
    Join<Group, User> userJoin = groups.join(Group_.users);
    return cb.and(
            cb.equal(root.get(Attempt_.user), userJoin),
            groups.in(grList)
    );
}

2. semi join:

(root, cq, cb) -> {
    Subquery<Long> groupSubQ = cq.subquery(Long.class);
    Root<Group> groups = groupSubQ.from(Group.class);
    Join<Group, User> userJoin = groups.join(Group_.users);
    groupSubQ.select(cb.literal(1L)).where(
            groups.in(grList),
            cb.equal(root.get(Attempt_.user), userJoin)
    );
    return cb.exists(groupSubQ);
}