Hi,

I am currently evaluating Hibernate Search for a project that already does custom Lucene indexing of certain fields in a Hibernate-driven database. Certain text fields on a table are indexed, and certain enum and integer based fields are not.

The application allows the creation of complex queries involving all available fields on the table, combining text searches with enum matches and integer comparisons. The query is created in HQL and returns the mapped objects from the table. In general this is working well, except where the Lucene-HQL bridge is concerned.

Currently, when a text field is included in the user query I am performing a Lucene search for the ids of the matching objects to obtain a List of Longs. Back in HQL I then add an 'and object.id in (:ids)' clause and bind the List to the HQL Query using the 'ids' parameter. This works well enough on small datasets, but if the Lucene query returns many matches the HQL/SQL query simply becomes too long and fails.

I had hoped that Hibernate Search would address this problem - it seems to be logical that it would combine the best of Hibernate and Lucene - but I can't find any help on resolving this problem.

I could use pagination to limit the Lucene search results, but then the final results count of the HQL query will likely be smaller than what it should be. Certain results may also be excluded should the integer and enum restrictions supplied be much tighter than the free text search in the user query.

Any ideas are much appreciated.

Thanks,
Craig

You are right, there are some limitations; in some cases easy to work around, in some cases harder.
I would like to discuss how to implement a general solution for all harder cases; some ideas could be to use pagination as you suggest, or apply some sort of object filter on a fulltext scrollable result (something like a validator, which discards some elements from the resultset), or have a general purpose combiner for HQL restrictions on top of a fulltext search.
As you noticed this solutions are tricky especially for the result count, but also the performance is affected by many factors: a savvy programmer would probably prefer to build it's own specific optimized solution instead than relying on a "blind" general purpose pattern, I mean "blind" as unaware of the specifics of the persistent model; very often there is some way to think of something smart depending on the case.

In your specific case, I would really recommend to just index the missing enums and integers: that's really the best performing solution and it won't take up much space in the index. Most of the systems I've worked on use a sort of "strategy selection": they look at the fields requested by the user, and then take a decision about using plain HQL or go the fulltext way. Of course to cover the most complex situations you must have enough information in your index so that you can apply all restrictions on the fulltext only.

Using Hibernate Search you need only to add some annotations to add the remaining fields to the index, even if they are not used for proper "fulltext", and you should be able to apply all restrictions quite easily, and actually achieving very good performance.
Especially when using indexed enums you have more benefits, for example you could use Search's filters to stack restrictions declaratively.

You're welcome to propose alternative solutions :-)

Hi Sanne,

Thanks for your response. I'd like to try your route of using a Lucene query for all parameters, but it's tricky mapping HQL to the Lucene query syntax - not impossible tho.

One route I have been looking at is creating a temporary table to hold all the ids from the Lucene search, and querying this in HQL using a subselect. This means the temporary table needs to be mapped in Hibernate though. That's also possible, but if you have more than one Lucene search in your query you need to have a second mapped table and so on (MySQL doesn't allow a temporary table to be used twice in the same statement).

So, at this stage it's an ugly solution, with a few mapped tables holding lists of ids and limiting on the number of Lucene results for any searches beyond that in the query.

Since I'm running up against Hibernate limitations (and not Java/SQL/database ones) I think it should be possible to extend HQL (when used in combination with Hibernate Search) to allow this solution to work generally.

Anyway, any thoughts are welcome.

Craig

Hi, Craig,

I ran into the same problem a few days ago and find this method in the Hibernate Search API:

setCriteriaQuery(Criteria criteria);

don't know if this works?