CODEWITHSUNDEEP
javahibernatehibernate-search
DfM
DfM

Reputation: 539

Hibernate Search: Getting the functionality of the SQL IN operator

Is it possible to get the functionality of the SQL IN operator when creating queries in Hibernate search?

For example, I have these two simple classes (constructors, getters and setters are omitted):

@Entity
@Indexed
public class Product {
    private long id;
    @Field
    private String title;
    @IndexedEmbedded
    private Category category;
}

@Entity
public class Category {
    private long id;
    @Field
    private String name;
}

Currently I have the following query, which searches products by title:

org.apache.lucene.search.Query luceneQuery = queryBuilder
        .keyword()
        .onField("title")
        .matching(queryString)
        .createQuery();

But I'd like to search products by title only in specific categories. Having a list of categories' ids (or names) to search in, how can I create such query?

I've found this SO question, but the author hasn't found a proper solution. Maybe now such functionality already exists?

I would be grateful for any help.

Upvotes: 0

Views: 168

Answers (1)

yrodiere
yrodiere

Reputation: 9977

First, index the id of categories:

@Entity
@Indexed
public class Product {
    private long id;
    @Field
    private String title;
    @IndexedEmbedded
    private Category category;
}

@Entity
public class Category {
    @Field // Added this
    private long id;
    @Field
    private String name;
}

Then, make sure to reindex your data, for example with the mass indexer.

Then, change your query code as explained below.

You will first need the "SQL IN", which is expressed in the Lucene world as category.id = <first> OR category.id = <second> OR .... Only boolean operators are bit different that what you may be used to (see here); in your case, you want "at least one" clause to match, so you'll have to use the should operator:

List<Long> categoryIds = ...; // Provided by the user
BooleanJunction<?> categoryIdJunction = queryBuilder.bool();
for ( categoryId : categoryIds ) {
    categoryIdJunction.should(
        queryBuilder
            .keyword()
            .onField("category.id")
            .matching(categoryId)
            .createQuery();
    );
}
org.apache.lucene.search.Query categoryIdQuery = categoryIdJunction.createQuery();

Finally you will need to combine that query with your other query on the title. To that end, use another boolean junction, this time with the must operator (all clauses must match):

org.apache.lucene.search.Query titleQuery = queryBuilder
        .keyword()
        .onField("title")
        .matching(queryString)
        .createQuery();
org.apache.lucene.search.Query luceneQuery = queryBuilder.bool()
        .must( categoryIdQuery )
        .must( titleQuery )
        .createQuery();

Upvotes: 1

Related Questions