How to do filter and sort in Broadleaf REST?

Question

In broadleaf, searching is done using solr. I'm able to search properly. I came to know that filtering and sorting are also done by solr, so I want to know how both can be achieved?

I've found that for filtering and sorting search facets are used, and I've executed below lines:

INSERT INTO BLC_FIELD (FIELD_ID, ENTITY_TYPE, PROPERTY_NAME, ABBREVIATION, SEARCHABLE, FACET_FIELD_TYPE) VALUES (1, 'PRODUCT', 'manufacturer', 'mfg', TRUE, 's');
INSERT INTO BLC_FIELD (FIELD_ID, ENTITY_TYPE, PROPERTY_NAME, ABBREVIATION, SEARCHABLE, FACET_FIELD_TYPE) VALUES (2, 'PRODUCT', 'defaultSku.retailPrice', 'price', FALSE, 'p');
INSERT INTO BLC_FIELD (FIELD_ID, ENTITY_TYPE, PROPERTY_NAME, ABBREVIATION, SEARCHABLE, TRANSLATABLE, FACET_FIELD_TYPE) VALUES (3, 'PRODUCT', 'defaultSku.name', 'name', TRUE, TRUE, 's');
INSERT INTO BLC_FIELD (FIELD_ID, ENTITY_TYPE, PROPERTY_NAME, ABBREVIATION, SEARCHABLE, FACET_FIELD_TYPE) VALUES (4, 'PRODUCT', 'model', 'model', TRUE, 's');
INSERT INTO BLC_FIELD (FIELD_ID, ENTITY_TYPE, PROPERTY_NAME, ABBREVIATION, SEARCHABLE, TRANSLATABLE) VALUES (5, 'PRODUCT', 'defaultSku.description', 'desc', TRUE, TRUE);
INSERT INTO BLC_FIELD (FIELD_ID, ENTITY_TYPE, PROPERTY_NAME, ABBREVIATION, SEARCHABLE, TRANSLATABLE) VALUES (6, 'PRODUCT', 'defaultSku.longDescription', 'ldesc', TRUE, TRUE);
INSERT INTO BLC_FIELD (FIELD_ID, ENTITY_TYPE, PROPERTY_NAME, ABBREVIATION, SEARCHABLE, FACET_FIELD_TYPE) VALUES (7, 'PRODUCT', 'defaultCategory.name', 'categoryName', TRUE, 's');

INSERT INTO BLC_FIELD_SEARCH_TYPES (FIELD_ID, SEARCHABLE_FIELD_TYPE) VALUES (1, 't');
INSERT INTO BLC_FIELD_SEARCH_TYPES (FIELD_ID, SEARCHABLE_FIELD_TYPE) VALUES (2, 't');
INSERT INTO BLC_FIELD_SEARCH_TYPES (FIELD_ID, SEARCHABLE_FIELD_TYPE) VALUES (3, 't');
INSERT INTO BLC_FIELD_SEARCH_TYPES (FIELD_ID, SEARCHABLE_FIELD_TYPE) VALUES (4, 't');
INSERT INTO BLC_FIELD_SEARCH_TYPES (FIELD_ID, SEARCHABLE_FIELD_TYPE) VALUES (5, 't');
INSERT INTO BLC_FIELD_SEARCH_TYPES (FIELD_ID, SEARCHABLE_FIELD_TYPE) VALUES (6, 't');
INSERT INTO BLC_FIELD_SEARCH_TYPES (FIELD_ID, SEARCHABLE_FIELD_TYPE) VALUES (7, 't');

INSERT INTO BLC_SEARCH_FACET (SEARCH_FACET_ID, FIELD_ID, LABEL, SHOW_ON_SEARCH, MULTISELECT, SEARCH_DISPLAY_PRIORITY) VALUES (1, 1, 'Manufacturer', FALSE, TRUE, 1);
INSERT INTO BLC_SEARCH_FACET (SEARCH_FACET_ID, FIELD_ID, LABEL, SHOW_ON_SEARCH, MULTISELECT, SEARCH_DISPLAY_PRIORITY) VALUES (2, 2, 'Price', FALSE, TRUE, 1);
INSERT INTO BLC_SEARCH_FACET (SEARCH_FACET_ID, FIELD_ID, LABEL, SHOW_ON_SEARCH, MULTISELECT, SEARCH_DISPLAY_PRIORITY) VALUES (3, 7, 'categoryName', FALSE, TRUE, 1);

For filtering, I'm able to do filter on price by using ?q=*&price=range[0:100]. I want to know how do I filter on manufacturer or on categoryName? I have added entries in database same as price for both, but I'm not able to filter on manufacturer or on categoryName.

For sorting, I've found "sortQuery" field in class named "ProductSearchCriteria", but I dunno how to use sort in query string. I've tried ?q=*&sort=price+asc and setting sortQuery in CatalogEndpoint.java like,

searchCriteria.setSortQuery(sort);

and then calling method,

result = getSearchService().findProductsByQuery(q, searchCriteria, sort);

but it is not working. Please kindly help.

Answer 1

If you are still stuck with adding more filters than this might help.

In order to add a new filter, you will need to make few entries in the database as below:

1. Make an entry in the BLC_SEARCH_FACET table for the new field (e.g. size):

INSERT INTO BLC_SEARCH_FACET (SEARCH_FACET_ID, FIELD_ID, LABEL,  SHOW_ON_SEARCH, MULTISELECT, SEARCH_DISPLAY_PRIORITY) VALUES (5, 9, 'size', TRUE, TRUE, 1);

2. Insert into table BLC_CAT_SEARCH_FACET_XREF to associate the field with categories:

INSERT INTO BLC_CAT_SEARCH_FACET_XREF (CATEGORY_SEARCH_FACET_ID, CATEGORY_ID, SEARCH_FACET_ID, SEQUENCE) VALUES (10, 10050, 5, 9);
INSERT INTO BLC_CAT_SEARCH_FACET_XREF (CATEGORY_SEARCH_FACET_ID, CATEGORY_ID, SEARCH_FACET_ID, SEQUENCE) VALUES (11, 10054, 5, 9);
INSERT INTO BLC_CAT_SEARCH_FACET_XREF (CATEGORY_SEARCH_FACET_ID, CATEGORY_ID, SEARCH_FACET_ID, SEQUENCE) VALUES (12, 10055, 5, 9);
INSERT INTO BLC_CAT_SEARCH_FACET_XREF (CATEGORY_SEARCH_FACET_ID, CATEGORY_ID, SEARCH_FACET_ID, SEQUENCE) VALUES (13, 10056, 5, 9);

3. Insert into table BLC_SEARCH_FACET_RANGE to specify range for your new field:

INSERT INTO BLC_SEARCH_FACET_RANGE (SEARCH_FACET_RANGE_ID, SEARCH_FACET_ID, MIN_VALUE, MAX_VALUE) VALUES (5, 5, 0, 5);
INSERT INTO BLC_SEARCH_FACET_RANGE (SEARCH_FACET_RANGE_ID, SEARCH_FACET_ID, MIN_VALUE, MAX_VALUE) VALUES (6, 5, 5, 10);
INSERT INTO BLC_SEARCH_FACET_RANGE (SEARCH_FACET_RANGE_ID, SEARCH_FACET_ID, MIN_VALUE, MAX_VALUE) VALUES (7, 5, 10, 15);
INSERT INTO BLC_SEARCH_FACET_RANGE (SEARCH_FACET_RANGE_ID, SEARCH_FACET_ID, MIN_VALUE, MAX_VALUE) VALUES (8, 5, 15, null);

Once this is done, in order to include this filter for products, you need to specify the product attribute "size" & its value in the advanced settings tab while defining a new product. And that's it you will be able to see the size filter in the left panel now.

Answer 2

I've figured out how to enable sort in broadleaf.

Consider method findProductsByQuery() in CatalagEndpoint.java. Add one query parameter named sort like this, @QueryParam("sort") String sort. Now set sort in searchCriteria.

searchCriteria.setSortQuery(sort);

Now when result = getSearchService().findProductsByQuery(q, searchCriteria); is called, it will execute method findProductsByQuery() of SolrSearchServiceImpl.java. This will return findProducts(query, facets, searchCriteria, null). Note, here defaultSort is set as null. For enabling sort, change

result = getSearchService().findProductsByQuery(q, searchCriteria);

to

result = getSearchService().findProductsByQuery(q, searchCriteria, sort);

in CatalogEndpoint.java. This will pass sort query string into method findProductsByQuery() of SolrSearchServiceImpl.java.

Now change SolrSearchServiceImpl.java for adding sort parameter in to method findProductsByQuery(). The method will look like this :

@Override
public ProductSearchResult findProductsByQuery(String query, ProductSearchCriteria searchCriteria, String sort) throws ServiceException {
    List<SearchFacetDTO> facets = getSearchFacets();
    query = "(" + sanitizeQuery(query) + ")";
    return findProducts(query, facets, searchCriteria, sort);
}

Also you have to add sort in interface SearchService,

public ProductSearchResult findProductsByQuery(String query, ProductSearchCriteria searchCriteria, String sort) throws ServiceException;

Add sort parameter in method findProducts(), the method will look like this :

@Deprecated
protected ProductSearchResult findProducts(String qualifiedSolrQuery, List<SearchFacetDTO> facets, ProductSearchCriteria searchCriteria, String defaultSort) throws ServiceException {
    return findProducts(qualifiedSolrQuery, facets, searchCriteria, defaultSort, null);
}

After changing this, sort query string is sent as defaultSort, and solr query is generated based on defaultSort.

Now in findProduct() method, one method is called,

attachSortClause(solrQuery, searchCriteria, defaultSort);

This method's impelementation is below:

protected void attachSortClause(SolrQuery query, ProductSearchCriteria searchCriteria, String defaultSort) {
    Map<String, String> solrFieldKeyMap = getSolrFieldKeyMap(searchCriteria);

    String sortQuery = searchCriteria.getSortQuery();
    if (StringUtils.isBlank(sortQuery)) {
            sortQuery = defaultSort;
    }

    if (StringUtils.isNotBlank(sortQuery)) {
            String[] sortFields = sortQuery.split(",");
            for (String sortField : sortFields) {
                String field = sortField.split(" ")[0];
                if (solrFieldKeyMap.containsKey(field)) {
                        field = solrFieldKeyMap.get(field);
                }
                ORDER order = "desc".equals(sortField.split(" ")[1]) ? ORDER.desc : ORDER.asc;

                if (field != null) {
                        query.addSortField(field, order);
                }
            }
    }
}

This is setting sortClauses as null, for setting sortClauses, change above implementation to below :

protected void attachSortClause(SolrQuery query, ProductSearchCriteria searchCriteria, String defaultSort) {
    Map<String, String> solrFieldKeyMap = getSolrFieldKeyMap(searchCriteria);

    String sortQuery = searchCriteria.getSortQuery();
    if (StringUtils.isBlank(sortQuery)) {
        sortQuery = defaultSort;
    }

    if (StringUtils.isNotBlank(sortQuery)) {
        String[] sortFields = sortQuery.split(",");
        for (String sortField : sortFields) {
            String field = sortField.split(" ")[0];
            if (solrFieldKeyMap.containsKey(field)) {
                field = solrFieldKeyMap.get(field);
            }
            ORDER order = "desc".equals(sortField.split(" ")[1]) ? ORDER.desc : ORDER.asc;

            if (field != null) {
                query.addSortField(field, order);
                List<SortClause> sortClauses = new ArrayList<SortClause>();
                SortClause clause = SortClause.create(field, order);
                sortClauses.add(clause);
                query.setSorts(sortClauses);
            }
        }
    }
}

This will set the sortClause based on sort query passed in query string.

Now when you pass ?q=*&sort=price+asc, it will return you json of products having ascending prices.

If you want to see how solr is creating indexing and sorting, put below line in log4j.xml of site.

<logger name="org.broadleafcommerce.core.search.service"> <level value="trace" /> </logger>

When your server is started solr creates all indexing and that indexes are shown as log after putting above line in log4j.xml.

When you request catalog/search/products?q=*&sort=price+asc, it will trace productIds based on price. You can see products are sorted based on ascending price in console.

[TRACE] 11:29:01 SolrSearchServiceImpl - SolrDocument{productId=10461}
[TRACE] 11:29:01 SolrSearchServiceImpl - SolrDocument{productId=10441}
[TRACE] 11:29:01 SolrSearchServiceImpl - SolrDocument{productId=10204}
[TRACE] 11:29:01 SolrSearchServiceImpl - SolrDocument{productId=10244}
[TRACE] 11:29:01 SolrSearchServiceImpl - SolrDocument{productId=10284}
[TRACE] 11:29:01 SolrSearchServiceImpl - SolrDocument{productId=10283}
[TRACE] 11:29:01 SolrSearchServiceImpl - SolrDocument{productId=10243}
[TRACE] 11:29:01 SolrSearchServiceImpl - SolrDocument{productId=10248}
[TRACE] 11:29:01 SolrSearchServiceImpl - SolrDocument{productId=10246}
[TRACE] 11:29:01 SolrSearchServiceImpl - SolrDocument{productId=10247}
[TRACE] 11:29:01 SolrSearchServiceImpl - SolrDocument{productId=10416}
[TRACE] 11:29:01 SolrSearchServiceImpl - SolrDocument{productId=10359}
[TRACE] 11:29:01 SolrSearchServiceImpl - SolrDocument{productId=10433}

And in JSON, you will get products based on orders of above traced productIds.