Using a subquery in a hibernate FullTextEntityManager query

Question

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 ?

yrodiere · Accepted Answer · 2019-05-29 10:08:11Z

1

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.

answered May 29, 2019 at 10:08

yrodiere

10.1k1 gold badge15 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gringogordo Over a year ago

Thanks that is very clear and comprehensive. I think I will try your ID approach as I think the code base I'm working on is in flux and so even if it is a little inefficient in my scenario it will be functionally adequate and I suspect we will have changes which will make it easier to do this and/or consider whether search is the right tool for the task we are trying to perform. At the moment I am committing the sin of getting my head down and trying to modify what is there just 'to get it working' and your answer is perfect for that!

Collectives™ on Stack Overflow

Using a subquery in a hibernate FullTextEntityManager query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related