Can you sort and search on same field with Hibernate/Lucene?

Question

I have the following annotated class that I am trying to sort the results from a lucene/hibernate search query. I finally have the query working properly but it seems that when I implement the necessary annotations (seen on jobStatus) to sort that column, it makes it impossible to then search that column. I am basing this off the instructions I found here on google. I have been having issues figuring this whole hibernate search and sort thing out, now that I finally figured out how to sort and search all I need is to be able to do them together.

@Entity
@Table(name="jobReq")
@Indexed
public class JobReq {

@Id
@DocumentId
@GeneratedValue(strategy=GenerationType.IDENTITY)
private Integer id;

@Field(index = Index.YES)
@Column(name="jobId", nullable=false, unique=true)
private String jobId;

@Field(index = Index.YES)
@Column(name="jobTitle", nullable=false)
private String jobTitle;

@Field(index = Index.YES)
@Column(name="jobContract", nullable=false)
private String contract;

@Field(index = Index.YES)
@Column(name="jobProject", nullable=true)
private String project;

@Field(index = Index.YES)
@Column(name="jobLaborCategory", nullable=false)
private String laborCategory;

@Field(index = Index.YES)
@Column(name="jobSummary", nullable=false)
private String summary;

@Field(index = Index.YES)
@Column(name="jobDescription", nullable=false)
private String jobDescription;

@Fields({@Field, @Field(analyze  = Analyze.NO, name = "jobStatus")})
@Column(name="jobStatus", nullable=false)
private String status;

@Field(index = Index.YES)
@Column(name="TTONumber", nullable=false)
private String TTONumber;

@Field(index = Index.YES)
@Column(name="jobPostedDate", nullable=false)
@Type(type="date")
private Date postedDate;

And a snippet from the search function

Field[] allFields = this.type.getDeclaredFields();
SortField field =new SortField(sortColumn, SortField.STRING, reverseSort);
Sort sort = new Sort(field);
hibQuery = fullTextSession.createFullTextQuery(bq, this.type).setSort(sort);
results = hibQuery.list();

score 5 · Answer 1 · edited Jul 07 '21 at 08:28

Hibernate search documentation provides a solution to this problem similar to Adam's solution.

https://docs.jboss.org/hibernate/search/5.11/reference/en-US/html_single/#fields-annotation

Basically use two @Field annotation to index a field twice once with Analyze.NO for sorting and once with Analyze.YES for searching.

@Entity
@Indexed(index = "Book")
public class Book {

    @Field
    @Field(name = "summary_forSort", analyze = Analyze.NO, store = Store.YES)
    @SortableField(forField = "summary_forSort")
    public String getSummary() {
        return summary;
    }

    // ...
}

analyze: determines whether the property is analyzed (Analyze.YES) or not (Analyze.NO). The default value is Analyze.YES.

Tip
Whether or not you want to analyze a property depends on whether you wish to search the element as is, or by the words it contains. It make sense to analyze a text field, but probably not a date field.

Tip
Fields used for sorting or faceting must not be analyzed.

score 3 · Accepted Answer · answered Aug 14 '13 at 12:44

Turns out you cannot sort and search on the same field, that article from the hibernate book was a tad misleading. So as for a fix I found a solution on the hibernate forums in that you create a 'shadow' column that is a duplicate, one is annotated for searching while the other is annotated for sorting.

It took me a while to find this solution mostly because the answer seems a bit 'hack-y' though fairly straight forward and simple, duplicating data has always been a no-no in my training. But then again I guess you learn something new everyday.

score 1 · Answer 3 · answered Aug 12 '13 at 20:09

1

Two things:

Creating indexes on every column may hurt performance down the line, as index updates are not free. It can also use an unnecessary amount of extra storage space. Of course, if this isn't actually a bottleneck for you, it doesn't matter.

You can sort with Hibernate Criteria, e.g.:

Criteria c = session.createCriteria(MyObject.class).addOrder(Order.desc(sortColumn));
Query q = session.createFullTextQuery(bq).setCriteriaQuery(c);

The sort key columns do not need to be indexed.

answered Aug 12 '13 at 20:09

Jason C

38,729
14
126
182

Awesome, thanks. Two questions, I actually have other fields as well (didn't want to list all the unnecessary ones) so if I'm not sorting or searching on them best practice should be not to index them? Also, if I wanted to do a custom sorting criteria (ie. sort 'Open' before 'Closed' could I do that with this method? – Adam James Aug 13 '13 at 12:04
I just tried this method, I removed the `@Fields({@Field, @Field(analyze = Analyze.NO, name = "jobStatus")})` and replaced it with just `@Field()` and tried sorting it based on the column 'jobStatus' and 'status'. It doesn't sort it, I'm not sure if that's because the variable name is 'status' while the column name in the database is 'jobStatus' – Adam James Aug 13 '13 at 13:10
I have to apologize, and stop answering here, because I am not 100% familiar with Hibernate Search and I think I'm only going to end up giving you misinformation. I did not realize that Search uses its own indexing scheme. Therefore, my point 1 above is not valid. I was referring to database level indexes. In researching Hibernate Search, it does appear that you need to tell Search to index fields, and Search uses its own index format. I may be wrong about that as well, but I do not recommend following my previous advice re: indexes. Sorry to lead you down the wrong path there. – Jason C Aug 13 '13 at 13:17
That said, the part of that point that does still hold, and the answer to your question above, is that if you are not searching/sorting on a field, no, you should not create an index on it - you will sacrifice performance in generating the indexes with no gain since you never use them. You can always add the index later if you change what fields are searchable. The part of the point that *doesn't* hold is that you *must* create an index if you wish to search/sort. – Jason C Aug 13 '13 at 13:19
And no, you cannot implement a custom search criteria like that with Hibernate Criteria. Criteria sorts are limited to what you can do with SQL `ORDER BY`. The Criteria system essentially gives you a clean way to programmatically build HQL queries (instead of explicitly building query strings). For sorting based on business values, you will have to do the sort in your business logic by sorting a collection of objects using a custom `Comparator`. Alternatively, you could select `Open` and `Closed` items separately and concatenate the two lists, if that is more appropriate for you. – Jason C Aug 13 '13 at 13:22
Ah okay, so back to square one lol, but thanks for the info about the not-needed index. It's something I basically knew but didn't think ut would matter to specify (defaults to indexed) – Adam James Aug 13 '13 at 13:27

Can you sort and search on same field with Hibernate/Lucene?

3 Answers3

Linked