CODEWITHSUNDEEP
hibernatehibernate-search
gringogordo
gringogordo

Reputation: 2130

Using a subquery in a hibernate FullTextEntityManager query

I am trying to modify some code using a hibernate FullTextEntityManager query. Essentially it works currently but I would like to constrain the results to a subset of records in a table (the most recent of a given type).

So for example the underlying query of data to search might be something along these lines (for demonstration only, I haven't parsed this!)

SELECT name, address 
FROM Persons p
WHERE p.name = sq.name
FROM 
(SELECT name, max(datemodified)
 FROM Persons 
  GROUP BY name) sq

Currently the java code is just selecting from the raw table (essentially this is what the forEntity option does I think)

FullTextEntityManager ftem = Search.getFullTextEntityManager(getEntityManager());

SearchFactory sf= ftem.getSearchFactory();
QueryContextBuilder qcb = sf.buildQueryBuilder();
QueryBuilder qb= qcb.forEntity(entityClass).get();

//processSearchExpression builds a lucene style full text search
org.apache.lucene.search.Query q= processSearchExpression();

FullTextQuery ftq= ftem.createFullTextQuery(q, entityClass);

So essentially I think I've put the essentials in there quite faithfully. What I can't quite work out is how to add a sub query or something producing similar functionality so that I can just query the most recent records of each type ?

Upvotes: 0

Views: 532

Answers (1)

yrodiere
yrodiere

Reputation: 9977

At the moment the easiest solution to combine ORM queries with Search queries is to add a clause to you search query to filter by id.

FullTextEntityManager ftem = Search.getFullTextEntityManager(getEntityManager());

SearchFactory sf= ftem.getSearchFactory();
QueryBuilder qb= sf.buildQueryBuilder().forEntity(entityClass).get();

BooleanJunction<?> idJunction = qb.bool();
for (Long id : listIdsYouWant()) {
   idJunction.must(qb.match().onField("id").matching(id).createQuery());
}

org.apache.lucene.search.Query q = qb.bool()
   //processSearchExpression builds a lucene style full text search
    .must(processSearchExpression())
    .filteredBy(idJunction.createQuery)
    .createQuery();

FullTextQuery ftq= ftem.createFullTextQuery(q, entityClass);

If that is not an option, you should look for a way to index the data you need to reproduce the SQL query using a full-text query.

In your case, you seem to be using a custom-built versioning system.

One solution would be to use Hibernate Envers instead, which last time I checked was fully compatible with Hibernate Search. See http://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#envers

Another solution would be to assign the same document ID to all persons that you consider identical. That way, there would only ever be a single person in the index: the last that was modified. See https://docs.jboss.org/hibernate/search/5.11/reference/en-US/html_single/#id-annotation You may however run into some trouble when fetching the entity matching a given document, since multiple entities would match. That's why using Envers would be a better idea.

Upvotes: 1

Related Questions