I want to make a request to the endpoint /users?numberOfBooksGreatherThan=5, i want the result to be a list of users with more than 5 books each. I use the specification API to implements it.

This is what i tried:

public static Specification<User> userNumberOfBooksGreaterThan(Long number){
    return ((root, query, criteriaBuilder) -> {
        Join<Book, User> userBooks = root.join("userBooksList");
        return criteriaBuilder.greaterThan(criteriaBuilder.count(userBooks.get("owner")), number);
    });
}

But i got this error

org.postgresql.util.PSQLException: ERROR: aggregate functions are not allowed in WHERE

This is the SQL query executed behind (from logs):

select b1_0.user_id,b1_0.name from users b1_0 join books b2_0 on b1_0.user_id=b2_0.owner_id where count(b2_0.owner_id)>5

i saw some answer to a problem like this but it was just in SQL, saying that i should change where by having but i don't know how to do that with JPA Specification.

when i use the query below (left outer join group by having), it returns the correct result, but i don't know how to transform this query into a jpa specification.

select b1_0.user_id,b1_0.name, from users b1_0 left outer join books b2_0 on b1_0.user_id=b2_0.owner_id group by user_id having count(b2_0.owner_id)>=5;

This is the User entity definition

@Entity(name = "users")
public class User {
    @Id
    private Long userId;
    private String name;
    @JsonManagedReference
    @OneToMany(mappedBy = "owner")
    private List<Book> userBooksList;
}

This is the Book entity definition

@Entity(name = "books")
public class Book {
    @Id
    private Long bookId;
    @ManyToOne
    @JoinColumn(name = "ownerId",referencedColumnName = "userId",nullable = false)
    private User owner;
    private Float price;
}

Thank you for your help

CodePudding user response：

I think you have a typo (Should be greaterThan instead of greatherThan).

EDIT: You should reverse the query, instead of Join<Book,User> it should be Join<User,Book>. owner is a field of User entity, not reachable through Book.

public static Specification<User> userNumberOfBooksGreaterThan(Long number) {
return (root, query, criteriaBuilder) -> {
    Join<User, Book> userBooks = root.join("userBooksList");
    return criteriaBuilder.greaterThan(criteriaBuilder.count(userBooks), number);
};}

Changing the comparison in the criteriaBuilder will give you the correct count of books for each user.

Greetings!

CodePudding user response：

I'm not sure the SQL statement you are trying to get:

select b1_0.user_id, b1_0.name from users b1_0 
 left outer join books b2_0 on b1_0.user_id=b2_0.owner_id 
group by user_id 
having count(b2_0.owner_id)>=5;

is syntactically correct, at least for Oracle it is not. However there are following options:

1. Having/group by

(root, cq, cb) -> {
   Join<User, Book> books = root.join("userBooksList");
   cq.having(cb.greaterThanOrEqualTo(cb.count(books), number));
   cq.groupBy(root);
   return cb.and();
}

or

(root, cq, cb) -> {
   Root<Book> bookRoot = cq.from(Book.class);
   cq.having(cb.greaterThanOrEqualTo(cb.count(bookRoot), number));
   cq.groupBy(root);
   return cb.equal(bookRoot.get("owner"), root);
}

2. correlated subquery (this one produces syntactically correct SQL statement)

(root, cq, cb) -> {
   Subquery<Long> subq = cq.subquery(Long.class);
   Root<Book> bookRoot = subq.from(Book.class);
   subq.where(cb.equal(bookRoot.get("owner"), subq.correlate(root)));
   return cb.greaterThanOrEqualTo(subq.select(cb.count(bookRoot)), number);
}