QueryDSL Issue: Filtering Nested Collections in Spring Boot with One-to-Many Relationship

Question

I'm working on a Spring Boot project using QueryDSL for database queries. In my application, I have three classes: ClientGroup, Client, and ClientBook. The ClientGroup class has a one-to-many relationship with Client, and Client has a one-to-many relationship with ClientBook.

Here are the simplified class structures:

@Entity

@Getter

@Setter

@NoArgsConstructor

public class ClientGroup {

    @Id

    @GeneratedValue

    private Integer id;

    @Column(unique = true)

    private String groupName;

    @Enumerated(EnumType.STRING)

    private ClientGroupType clientGroupType;

    private Boolean enabled = true;

    @OneToMany(mappedBy = "ClientGroup", cascade = CascadeType.ALL, orphanRemoval = true)

    private List<Client> groupClients = new ArrayList<>();

    @Version

    public Integer optLock;

}

@Entity

@Getter

@Setter

@NoArgsConstructor

public class Client {

    @Id

    @GeneratedValue

    private Integer id;

    @Column

    private String clientName;

    @OneToMany(mappedBy = "client", cascade = CascadeType.ALL, orphanRemoval = true)

    private List<ClientBook> clientBooks = new ArrayList<>();

    @ManyToOne(fetch = FetchType.LAZY)

    @JoinColumn(name = "client_group_id")

    private ClientGroup clientGroup;

    @ManyToOne

    private ClientCategory clientCategory;

    private LocalDate createdDate = LocalDate.now();

    @Version

    private Integer optLock;

    private Boolean enabled = true;

}

@Entity
@Getter
@Setter
@NoArgsConstructor
public class ClientBook {
    @Id
    @GeneratedValue
    private Integer id;
    private String clientBookIdentifier;
    private String bookName;
    @Enumerated(EnumType.STRING)
    private BookType bookType;
    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "client_id", updatable = false, nullable = false)
    private Client client;
    @Version
    private Integer optLock = 0;
    private Boolean enabled = true;
}

I want to fetch Clients from the database, filtering them based on the clientGroupType of ClientGroup, and sort the results by createdDate and isPremiumClient.

Additionally, I need to exclude certain entries of ClientBooks based on bookIdentifier from the clientBooks in returned Clients List

While I can achieve this using SQL directly in the database, QueryDSL or CriteriaAPI doesn't seem to ignore ClientBooks with the specified bookIdentifier in clientBooks array within the Client class. It retrieves all the books instead.

Do you have any suggestions on how I can address this issue?

P.S. Please note that field names and class names have been simplified for clarity.

I tried variations of code below using Criteria API and QueryDSL. But every time got all of clientBooks or InvalidPathException

        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Client> criteriaQuery = criteriaBuilder.createQuery(Client.class);
        Root<Client> root = criteriaQuery.from(Client.class);
        Join<Client, ClientBook> clientBookJoin = root.join("clientBooks");

        Predicate groupTypePredicate = criteriaBuilder.equal(root.get("clientGroup").get("clientGroupType"), ClientGroupType.SOME_TYPE);
        Predicate bookIdentifierPredicate = criteriaBuilder.notEqual(clientBookJoin.get("clientBookIdentifier"), "some_identifier");

        criteriaQuery.select(root)
            .where(criteriaBuilder.and(groupTypePredicate, bookIdentifierPredicate))
            .distinct(true);

        return entityManager.createQuery(criteriaQuery).getResultList();