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?

CodePudding user response：

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);
}