Jpa query sub filter with hibernate search


#1

I need to combine a JPA query with hibernate search, for example: JPA Query: " SELECT p FROM Product p WHERE p.cat = 'cars' "
Then over the results of the JPA Query I need sub filter with the hibernate search query:

queryBuilder
  .keyword()
  .onFields("productName", "description")
  .matching(text)
  .createQuery();
fullTextEntityManager.getResultList(); 

(list of results must be only on the result of the JPA query)


#2

I’d strongly recommend rather indexing “cat” and doing the whole query in Hibernate Search. Any other solution is bound to bring significant problems.

That being said, if you really need to execute one query on the JPA side and one on the Hibernate Search side, you can always do that manually and combine the results. For example you could select the entity IDs in the JPA query, then add a predicate to your HSearch query:

List<Long> idsFromJpaQuery = entityManager.createQuery(  "SELECT p.id FROM Product p WHERE p.cat = 'cars' ", Long.class ).getResultList();

Query textQuery = queryBuilder.keyword()
  .onFields("productName", "description")
  .matching(text)
  .createQuery();

BooleanJunction idJunction = queryBuilder.bool();
for ( Long id : idsFromJpaQuery ) {
  idJunction.should( queryBuilder.keyword().onField( "id" ).matching( id ).createQuery() );
}
Query idQuery = idJunction.createQuery();

Query combinedQuery = queryBuilder.bool()
  .must( textQuery )
  .must( idQuery )
  .createQuery();

FullTextQuery query = fullTextEntityManager.createFullTextQuery( combinedQuery, Product..class );

List<Product.> results = query.getResultList();

As to why this feature is not available in Hibernate Search directly:

The problem with combining queries from different data sources is that it performs very badly, in particular if you need to paginate the result. Basically, if you need to reach page 30, you will have to fetch the results of more than 30 pages from both queries, then compute the intersection of the result sets inside your application, possibly fetching some more pages from each query because the intersection didn’t produce enough results to reach the 30th page. Really, really bad performance.
More often than not, you’re better off indexing some more fields.


#3

Thanks a lot!
I just wonder if it was possible, again thank you very much!