I am new to Hibernate search, and am trying to implement this for searching a database that contains transaction details. Sorry if this is a simple question. I am able to use the full-text queries and use projections to select certain fields for a query, etc.

But I am struggling with a group by query like this. Is it possible to write a query like this using hibernate search?

Select count(transaction_id), system, status from transactions where description like '%Downstream error%' group by system, status.

I have figured out how to tokenize and index the description field, so am good with the actual searching, but not able to get a good aggregate count of systems and statuses and group them.

I have searched long and hard for an example for implementing this but still not found any. Wondering if anyone can throw me a few pointers. Any help will be greatly appreciated.

Thanks.

Hi,

have you seen that you can set a Sort object via fulltextQuery.setSort(). I guess you could do something like this:



Not sure about the count though.

Thanks for the response.

Yes, i used the sort, and it works. So i am implementing logic to traverse the result set and count the matching rows grouped by system and status. I was not sure if there is a built-in function or ability to do this more efficiently using hibernate search, similar to the "count(*)" for database SQL queries. I am not sure if Hibernate search already has access to this information, so i dont have to traverse it again to count and group by,etc.

Hi,

I guess you could use FulltextQuery.getResultSize().

--Hardy

Thanks again for the response ! My understanding is that getResultSize() returns the entire resultset count. Can I still use it to get counts of subsets within the resultset?

The other alternative is to query by each system & status. Either way, my concern is there will be a performance impact. Approach one would mean I execute 1 single query but would still have to traverse the entire resultset and compute counts. In approach two, I would have to execute system * status number of queries, and I can get the size of each resultset, that would be same as the count. Is there another alternative? I just want to make sure I am not missing any other alternatives that may be more efficient.

Hi,
I would repeat the queries, after you've executed the main query all needed objects are still loaded in the buffer of the first query, like having the whole tree in memory, and containing the stats you're looking for: so you end up during N queries, but they should be very efficient for your use case.
Keep a reference to the original query, then make the others as boolean "AND" queries with the first one, adding a "must" clause on the term you want to group on, then don't execute it but run only the getResultSize().

A bit more verbose, but nicer IMHO, is to declare a filter having one parameter: the key to group on. You run the query several times, changing the filter each time. This way you can cache the filter's result and reuse them on other queries as well, I imagine you group on a small set of keyword terms so this might make sense.

Try to avoid iterating on the resultset of your original query: if this keywords list is limited it might be much better to do several queries than to load thousands (millions?) of objects.

Yes, the table contains 15 million records. So I will try the multiple queries approach. Thanks !