Order Search in OFBiz Powered by Solr

While evaluating an E-commerce ERP platform for your business, enterprise search will likely be one of the requirements. Having Google-like quick, relevant, and auto-suggestion enabled search throughout your applications, can be a complete game changer. This can directly impact staff productivity and turn around time for the completion of certain tasks.

How can it help?

Here are a couple of examples where it can be of great help.  It is not limited to these two areas and it can be expanded to any level.

Customer Service

Your Customer Service department becomes efficient and more productive if it can quickly find customer profiles, orders, and products quickly. It also helps reduce turn around time in answering customer questions.

Warehouse and Fulfillment

Your order fulfillment staff becomes efficient and more productive if it can quickly identify orders ready for picking, packing, and shipping leading to quicker order fulfillment.  Good search can also aid in determining inventory availability.

Order Search Powered by Apache Solr

In my earlier blog post we used the OFBiz example component to demonstrate the use of Apache Solr search. In this post we will look at a real world example using Solr search. We will implement search for orders using Solr, with facets on order status and the sales channel.

We will assume you have already downloaded and setup Apache Solr and Apache OFBiz on your local computer.  If not, you can find details for the set up here.

Now I am trying to provide some development references. In this post we will mainly focus on the service part of the implementation, where we will create order documents, index those, and do a search query. Complete working code can be referenced from here.

Property Setup in OFBiz

Code reference available on GitHub here.

# solr.host is URL to access solr server
solr.host=http://localhost:8983/solr

Defining Document Schema in Solr

    .....
    .....
    <!-- Only remove the "id" field if you have a very good reason to. While not strictly
     required, it is highly recommended. A <uniqueKey> is present in almost all Solr 
     installations. See the <uniqueKey> declaration below where <uniqueKey> is set to "id".
   -->   
    <field name="orderId" type="string" indexed="true" stored="true" required="true" multiValued="false" />
    <field name="statusId" type="string" indexed="true" stored="false" required="false" multiValued="false" />
    <field name="orderTypeId" type="string" indexed="true" stored="false" required="false" multiValued="false" />
    <field name="salesChannelEnumId" type="string" indexed="true" stored="false" required="false" multiValued="false" />
    <field name="customer" type="text_general" stored="false" required="false" indexed="true" multiValued="true"/>
    <field name="orderDate" type="date" stored="false" required="false" indexed="true" multiValued="false"/>
    <field name="fullText" type="lowercase" stored="false" required="false" indexed="true" multiValued="true"/>
    <copyField source="orderId" dest="fullText"/>
    <copyField source="customer" dest="fullText"/>
......
......
......
<!-- Field to use to determine and enforce document uniqueness. 
      Unless this field is marked with required="false", it will be a required field
   -->
 <uniqueKey>orderId</uniqueKey>

Creating Document and Indexes for Solr in OFBiz

Code reference available on GitHub here.

Service Definition-

<!-- Create index in solr for an Order. -->
    <service name="indexOrder" engine="java" auth="true" require-new-transaction="true"
            location="org.ofbiz.orders.OrderServices" invoke="indexOrder" transaction-timeout="7200">
        <description>Creates index for an order.</description>
        <attribute name="orderId" type="String" mode="IN" optional="false"/>
    </service>

SECA Rules-

Code reference available on GitHub here.

    <eca service="createOrderHeader" event="global-commit-post-run">
        <action service="indexOrder" mode="async"/>
    </eca>
    <eca service="updateOrderHeader" event="global-commit-post-run">
        <action service="indexOrder" mode="async"/>
    </eca>
    <eca service="changeOrderStatus" event="global-commit-post-run">
        <action service="indexOrder" mode="async"/>
    </eca>

Service Implementation-

Code reference available on GitHub here.

public static String getSolrHost(Delegator delegator, String coreName) {
        String solrHost = UtilProperties.getPropertyValue("search", "solr.host");
        if (UtilValidate.isNotEmpty(solrHost)) {
            solrHost += "/" + coreName;
            String tenantId = delegator.getDelegatorTenantId();
            if (UtilValidate.isNotEmpty(tenantId)) {
                solrHost = solrHost + "-" + tenantId;
            }
        }
        return solrHost;
    }

    public static Map<String, Object> getPaginationValues(Integer viewSize, Integer viewIndex, Integer listSize) {
        Map<String, Object> result = FastMap.newInstance();
        if (UtilValidate.isNotEmpty(listSize)) {
            Integer lowIndex = (viewIndex * viewSize) + 1;
            Integer highIndex = (viewIndex + 1) * viewSize;
            if (highIndex > listSize) {
                highIndex = listSize;
            }
            Integer viewIndexLast = (listSize % viewSize) != 0 ? (listSize / viewSize + 1) : (listSize / viewSize);
            result.put("lowIndex", lowIndex);
            result.put("highIndex", highIndex);
            result.put("viewIndexLast", viewIndexLast);
        }
    public static Map<String, Object> indexOrder(DispatchContext dctx, Map<String, ? extends Object> context) {
        Delegator delegator = dctx.getDelegator();
        String orderId = (String) context.get("orderId");
        Locale locale = (Locale) context.get("locale");
        String solrHost = getSolrHost(delegator, "orders");
        HttpSolrServer server = new HttpSolrServer(solrHost);
        SolrInputDocument solrDocument = new SolrInputDocument();
        GenericValue order = null;
        try {
            order = delegator.findOne("OrderHeader", UtilMisc.toMap("orderId", orderId), false);
        } catch (GenericEntityException e) {
            Debug.logError(e, module);
        }
        solrDocument.addField("orderId", orderId);
        solrDocument.addField("orderTypeId", order.get("orderTypeId"));
        solrDocument.addField("statusId", order.get("statusId"));
        GenericValue orderItem = null;
        try {
            orderItem = EntityUtil.getFirst(order.getRelated("OrderItem", null, null, false));
        } catch (GenericEntityException e) {
            Debug.logError(e, module);
        }
        // Solr supports yyyy-MM-dd'T'HH:mm:ss.SSS'Z' date format.
        DateFormat df = UtilDateTime.toDateTimeFormat("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'", TimeZone.getDefault(), locale);
        solrDocument.addField("orderDate", df.format(order.get("orderDate")));
        String roleTypeId = "BILL_TO_CUSTOMER";
        if ("PURCHASE_ORDER".equals(order.getString("orderTypeId"))) {
            roleTypeId = "BILL_FROM_VENDOR";
        }
        GenericValue orderRole = null;
        try {
            orderRole = EntityUtil.getFirst(order.getRelated("OrderRole", UtilMisc.toMap("roleTypeId", roleTypeId), null, false));
        } catch (GenericEntityException e) {
            Debug.logError(e, module);
        }
        solrDocument.addField("customer", PartyHelper.getPartyName(delegator, orderRole.getString("partyId"), false));
        solrDocument.addField("salesChannelEnumId", order.get("salesChannelEnumId"));

        try {
            server.add(solrDocument);
            server.commit();
        } catch (SolrServerException e) {
            Debug.logError(e, module);
        } catch (IOException e) {
            Debug.logError(e, module);
        }
        return ServiceUtil.returnSuccess();
    }

Searching

Code reference available on GitHub here.

GetOrderSearchFacets.groovy

import java.sql.Timestamp;

import org.apache.solr.client.solrj.impl.HttpSolrServer;
import org.apache.solr.client.solrj.SolrQuery;
import org.apache.solr.client.solrj.SolrRequest;
import org.apache.solr.client.solrj.request.QueryRequest;
import org.apache.solr.client.solrj.util.ClientUtils;

import org.ofbiz.base.util.*;

import org.ofbiz.orders.OrderServices;

keyword = parameters.keyword?.trim() ?: "";
customer = parameters.customer?.trim() ?: "";

// Get server object
server = new HttpSolrServer(OrderServices.getSolrHost(delegator, "orders"));

// Common query
commonQuery = new SolrQuery();
keywordString = keyword.split(" ")
keywordQueryString = "";
keywordString.each { token->
    token = ClientUtils.escapeQueryChars(token);
    if (keywordQueryString) {
        keywordQueryString = keywordQueryString + " OR *" + token + "*";
    } else {
        keywordQueryString = "*" + token + "*";
    }
}
commonQuery.setParam("q", "fullText:(" + keywordQueryString + ") AND orderTypeId:SALES_ORDER");
commonQuery.setFacet(true);

//Status Filter Query
searchedStatusString = parameters.status;
searchedStatus = searchedStatusString?.split(":");
statusFilterQuery = "";
if (searchedStatus) {
    searchedStatus.each { searchedStatusId ->
        if (searchedStatusId) {
            if (statusFilterQuery) {
                statusFilterQuery = statusFilterQuery + " OR " + searchedStatusId;
            } else {
                statusFilterQuery = searchedStatusId;
            }
        }
    }
    if (statusFilterQuery) {
        statusFilterQuery = "statusId:" + "(" + statusFilterQuery + ")";
    }
}

//Channel Filter Query
channelFilterQuery = "";
channel = parameters.channel;
if (channel) {
    channelFilterQuery = "salesChannelEnumId:" + channel;
}

// Status Facets
facetStatus = [];

//Get the order statuses
allStatusIds = delegator.findByAnd("StatusItem", [statusTypeId : "ORDER_STATUS"], ["sequenceId", "description"], false);

allStatusIds.each { status ->
    commonQuery.addFacetQuery("statusId:" + status.statusId);
}
if (channelFilterQuery) {
    commonQuery.addFilterQuery(channelFilterQuery);
}
// Get status facet results.
qryReq = new QueryRequest(commonQuery, SolrRequest.METHOD.POST);
rsp = qryReq.process(server);

Map<String, Integer> facetQuery = rsp.getFacetQuery();
commonParam = "";
if (parameters.keyword) {
    commonParam = "keyword=" + keyword;
}
if (commonParam) {
    statusUrlParam = commonParam + "&status=";
} else {
    statusUrlParam = "status=";
}
allStatusIds.each { status ->
    statusInfo = [:];
    statusParam = statusUrlParam;
    statusCount = facetQuery.get("statusId:" + status.statusId);
    commonQuery.removeFacetQuery("statusId:" + status.statusId);
    if (statusCount > 0 || searchedStatus?.contains(status.statusId)) {
        statusInfo.statusId = status.statusId;
        statusInfo.description = status.description;
        statusInfo.statusCount = statusCount;

        if (searchedStatus) {
            urlStatus = [];
            searchedStatus.each { searchedStatusId ->
                if (searchedStatusId){
                    if (!(searchedStatusId.equals(status.statusId))){
                        urlStatus.add(searchedStatusId);
                    }
                }
            }
            urlStatus.each { urlStatusId->
                statusParam = statusParam + ":" + urlStatusId;
            }
            if (!(searchedStatus.contains(status.statusId))) {
                statusParam = statusParam + ":" + status.statusId;
            }
        } else {
            statusParam = statusParam + status.statusId;
        }
        statusUrl = statusParam;
        if (channel) {
            statusUrl = statusUrl + "&channel=" + channel;
        }
        statusInfo.urlParam = statusUrl;
        facetStatus.add(statusInfo);
    }
}
if (channel) {
    statusUrlParam = statusUrlParam + "&channel=" + channel;
}
context.clearStatusUrl = statusUrlParam;

context.facetStatus = facetStatus;

//Channel Facets
facetChannels = [];

//Get the channels
allChannels = delegator.findByAnd("Enumeration", [enumTypeId : "ORDER_SALES_CHANNEL"], ["sequenceId"]);

allChannels.each { channel ->
    commonQuery.addFacetQuery("salesChannelEnumId:" + channel.enumId);
}
if (statusFilterQuery) {
    commonQuery.addFilterQuery(statusFilterQuery);
}
if (channelFilterQuery) {
    commonQuery.removeFilterQuery(channelFilterQuery);
}

//Get channel facet results.
qryReq = new QueryRequest(commonQuery, SolrRequest.METHOD.POST);
rsp = qryReq.process(server);
facetQuery = rsp.getFacetQuery();

defaultStatusParam = "";
if (searchedStatus) {
    searchedStatus.each { searchedStatusId ->
        if (searchedStatusId) {
            defaultStatusParam = defaultStatusParam + ":" + searchedStatusId;
        }
    }
}

if (commonParam) {
    channelParam = commonParam + "&channel=";
} else {
    channelParam = "channel=";
}
allChannels.each { channel ->
    channelInfo = [:];
    channelCount= facetQuery.get("salesChannelEnumId:" + channel.enumId);
    commonQuery.removeFacetQuery("salesChannelEnumId:" + channel.enumId);
    if (channelCount > 0) {
        channelUrlParam = channelParam;
        channelInfo.channelId = channel.enumId;
        channelInfo.description = channel.description;
        channelInfo.channelCount = channelCount;
        if (parameters.channel) {
            if (!parameters.channel.equals(channel.enumId)) {
                channelUrlParam = channelUrlParam + channel.enumId;
            } 
        } else {
            channelUrlParam = channelUrlParam + channel.enumId;
        }
        if(defaultStatusParam) {
            channelUrlParam = channelUrlParam + "&status=" + defaultStatusParam;
        }
        channelInfo.urlParam = channelUrlParam;
        facetChannels.add(channelInfo);
    }
}
context.facetChannels = facetChannels;

if (channelFilterQuery) {
    commonQuery.addFilterQuery(channelFilterQuery);
}
qryReq = new QueryRequest(commonQuery, SolrRequest.METHOD.POST);
rsp = qryReq.process(server);

facetQuery = rsp.getFacetQuery();
facetDays = [];

SearchOrderByFilters.groovy

import java.math.BigDecimal;
import java.sql.Timestamp;

import javolution.util.FastMap;

import org.apache.solr.client.solrj.impl.HttpSolrServer;
import org.apache.solr.client.solrj.request.QueryRequest;
import org.apache.solr.client.solrj.SolrQuery;
import org.apache.solr.client.solrj.SolrRequest;
import org.apache.solr.client.solrj.util.ClientUtils;

import org.ofbiz.base.util.UtilDateTime;
import org.ofbiz.base.util.UtilNumber;
import org.ofbiz.base.util.UtilValidate;

import org.ofbiz.entity.condition.EntityCondition;
import org.ofbiz.entity.condition.EntityOperator;
import org.ofbiz.entity.util.EntityUtil;

import org.ofbiz.order.order.OrderReadHelper;
import org.ofbiz.orders.OrderServices;

int scale = UtilNumber.getBigDecimalScale("order.decimals");
int rounding = UtilNumber.getBigDecimalRoundingMode("order.rounding");
BigDecimal ZERO = (BigDecimal.ZERO).setScale(scale, rounding);

keyword = parameters.keyword?.trim() ?: "";
customer = parameters.customer?.trim() ?: "";
viewSize = Integer.valueOf(context.viewSize ?: 20);
viewIndex = Integer.valueOf(context.viewIndex ?: 0);
channel = parameters.channel;
status = parameters.status;

//Get server object
HttpSolrServer server = new HttpSolrServer(OrderServices.getSolrHost(delegator, "orders"));

// filtered queries on facets.
query = new SolrQuery();
keywordString = keyword.split(" ");
keywordQueryString = "";
keywordString.each { token->
    token = ClientUtils.escapeQueryChars(token);
    if (keywordQueryString) {
        keywordQueryString = keywordQueryString + " OR *" + token + "*";
    } else {
        keywordQueryString = "*" + token + "*";
    }
}
query.setParam("q", "fullText:(" + keywordQueryString + ") AND orderTypeId:SALES_ORDER");
query.setParam("sort", "orderDate desc");

completeUrlParam = "";
if (parameters.keyword) {
    completeUrlParam = "keyword=" + keyword;
}
if (status) {
    queryStringStatusId = "";
    if (completeUrlParam) {
        completeUrlParam = completeUrlParam + "&status=";
    } else {
        completeUrlParam = "status=";
    }
    statusString = status;
    statusIds = statusString.split(":");
    statusIds.each { statusId ->
        if (statusId) {
            if (queryStringStatusId) {
                queryStringStatusId = queryStringStatusId + " OR " + statusId;
            } else {
                queryStringStatusId = statusId;
            }
            completeUrlParam = completeUrlParam + ":" + statusId;
        }
    }
    if (queryStringStatusId) {
        query.addFilterQuery("statusId:(" + queryStringStatusId + ")");
    }
}

if (channel) {
    if (completeUrlParam) {
        completeUrlParam = completeUrlParam + "&channel=" + channel;
    } else {
        completeUrlParam = "channel=" + channel;
    }
    query.addFilterQuery("salesChannelEnumId:" + channel);
}

completeUrlParamForPagination = completeUrlParam;

qryReq = new QueryRequest(query, SolrRequest.METHOD.POST);
rsp = qryReq.process(server);

listSize = Integer.valueOf(rsp.getResults().getNumFound().toString());

Map<String, Object> result = FastMap.newInstance();
if (UtilValidate.isNotEmpty(listSize)) {
    Integer lowIndex = (viewIndex * viewSize) + 1;
    Integer highIndex = (viewIndex + 1) * viewSize;
    if (highIndex > listSize) {
        highIndex = listSize;
    }
    Integer viewIndexLast = (listSize % viewSize) != 0 ? (listSize / viewSize + 1) : (listSize / viewSize);
    result.put("lowIndex", lowIndex);
    result.put("highIndex", highIndex);
    result.put("viewIndexLast", viewIndexLast);
}
paginationValues = result;

query.setRows(viewSize);
query.setStart(paginationValues.get('lowIndex') - 1);
qryReq = new QueryRequest(query, SolrRequest.METHOD.POST);

rsp = qryReq.process(server);
docs = rsp.getResults();
orderIds = [];
docs.each { doc->
    orderIds.add((String) doc.get("orderId"));
}
orderList = delegator.findList("OrderHeader", EntityCondition.makeCondition("orderId", EntityOperator.IN, orderIds), null, ["orderDate DESC"], null, false);
orderInfoList = [];
orderList.each { order->
    orderInfo = [:];
    orderInfo.orderId = order.orderId;
    orderInfo.orderDate = order.orderDate;
    orh = OrderReadHelper.getHelper(order);
    partyId = orh.getPlacingParty()?.partyId;
    orderInfo.partyId = partyId;
    partyEmailResult = dispatcher.runSync("getPartyEmail", [partyId: partyId, userLogin: userLogin]);
    orderInfo.emailAddress = partyEmailResult?.emailAddress;
    channel = order.getRelatedOne("SalesChannelEnumeration");
    orderInfo.channel = channel;
    partyNameResult = dispatcher.runSync("getPartyNameForDate", [partyId: partyId, compareDate: order.orderDate, userLogin: userLogin]);
    orderInfo.customerName = partyNameResult?.fullName;
    List<GenericValue> orderItems = orh.getOrderItems();
    BigDecimal totalItems = ZERO;
    for (GenericValue orderItem : orderItems) {
       totalItems = totalItems.add(OrderReadHelper.getOrderItemQuantity(orderItem)).setScale(scale, rounding);
    }
    orderInfo.orderSize = totalItems.setScale(scale, rounding);
    statusItem = order.getRelatedOne("StatusItem");
    orderInfo.statusId = statusItem.statusId;
    orderInfo.statusDesc = statusItem.description;
    orderInfoList.add(orderInfo);
}
context.completeUrlParam = completeUrlParam;
context.completeUrlParamForPagination = completeUrlParamForPagination;
context.viewIndex = viewIndex;
context.viewSize = viewSize;
context.lowIndex = paginationValues.get("lowIndex");
context.listSize = listSize;
context.orderList = orderList;
context.orderInfoList = orderInfoList;

A complete working component named ordersearchsolr along with sample order webapp is available on GitHub under evolvingofbiz setup (you will find ordersearchsolr in hot-deploy directory as its committed there). You can setup this codebase which leverages OFBiz 13.07 from GitHub.

This codebase will give you will give you results as shown in below given image as you access url – https://localhost:8443/orders (after creating some orders from ordermgr)

If you want to know more about enterprise search as part of your e-commerce or ERP project contact us today.