CODEWITHSUNDEEP
javasqlhibernatejpacriteria-api
Hank McCoy
Hank McCoy

Reputation: 23

JPA Criteria query : writing query with array operators

I have the following entities

Book {
  private String title;
  
  @ManyToMany
  @JoinTable(
     name = "book_should_read_user",
     joinColumns = @JoinColumn(name = "book_id"),
     inverseJoinColumns = @JoinColumn(name = "user_id")
  )
  private Set<User> shouldRead; // the users who should read the book

  @OneToMany(mappedBy = "book")
  private Set<BookReadAction> actions; // all the read actions performed on the book
}

BookReadAction {
  @ManyToOne
  private Book book;

  @ManyToOne
  private User user;
}

Now I want to query all books that have been read by all the users in the should read collection. The following sql query in postgres does the trick :

select * 
from book 
where id in (
   select distinct id 
   from (
      select book.id id, 
             array_agg(book_should_read_user.user_id) suggested_readers, 
             array_agg(distinct book_read_action.user_id) read_by 
     from book b
         inner join book_should_read_user on book.id = book_should_read_user.book_id
         inner join book_read_action on book.id = book_read_action.book_id
     group by book.id) subquery
   where suggested_readers <@ read_by)

I however want to add this clause programmatically so i'd rather use JPA criteria API. Despite some attempts i struggled. Is it possible to build a Predicate from this query in JPA criteria API?

Upvotes: 2

Views: 2112

Answers (2)

Christian Beikov
Christian Beikov

Reputation: 16400

You can't exactly write the query you wrote as HQL but an equivalent one:

select * 
from book b
where exists (
     select 1
     from book b
         inner join book_should_read_user on book.id = book_should_read_user.book_id
         inner join book_read_action on book.id = book_read_action.book_id
     where b.id = book.id
     group by book.id
     having array_agg(book_should_read_user.user_id) <@ array_agg(distinct book_read_action.user_id)
)

To write this query with HQL or the JPA Criteria API you will need to provide a custom implemention of the <@ operator or the overall predicate through a SQLFunction that you can register in your Hibernate dialect. Something like this:

public class ArrayContainsFunction implements SQLFunction {
        
    @Override
    public boolean hasArguments() {
        return true;
    }
    
    @Override
    public boolean hasParenthesesIfNoArguments() {
        return true;
    }
    
    @Override
    public Type getReturnType(Type firstArgumentType, Mapping mapping) throws QueryException {
        SessionFactoryImplementor sfi = (SessionFactoryImplementor) mapping;
        return sfi.getTypeHelper().basic(Integer.class);
    }
    
    @Override
    public String render(Type firstArgumentType, List args, SessionFactoryImplementor factory) throws QueryException {
        return "array_agg(" + args.get(0) + ") <@ array_agg(" + args.get(1) + ") and 1=";
    }
}

When registering it, you should be able to use like this in HQL ... HAVING array_contains(shouldRead.id, readAction.id)

Upvotes: 2

JLazar0
JLazar0

Reputation: 1292

With criteria API you cannot implement a subquery as from clause.

https://www.objectdb.com/java/jpa/query/jpql/from#FROM_and_JOIN_in_Criteria_Queries

You would have to restructure your query

Upvotes: 0

Related Questions