Search in PurchaseOrder

Juan Munoz

Vote:

Hi
I trying to create a SQL query that I can execute with

var searchOptions = new OrderSearchOptions
{
CacheResults = false,
StartingRecord = 0,
RecordsToRetrieve = 10000,
Namespace = OrderNamespace.OrdersNamespace
};

var parameters = new OrderSearchParameters();

var sqlQueryFilter = new StringBuilder();
sqlQueryFilter.Append("OrderGroupId IN (SELECT ObjectId From OrderFormEx ");
sqlQueryFilter.AppendFormat("WHERE OrderFormEx.MyField = '{0}' )", DataToFind);
parameters.SqlWhereClause = sqlQueryFilter.ToString();
OrderContext.Current.Search<PurchaseOrder>(parameters, options);

I need to query also a field in OrderFormEx.
Any idea how to create the sqlQueryFilter?

Thanks

#227336

Edited, Sep 01, 2020 21:20

Vote:

If I recall correctly you can do something like:

searchOptions.Classes.Add("OrderFormEx");

#227356

Sep 02, 2020 7:35

Juan Munoz - Sep 02, 2020 7:42

How should the code looks like then? and where should I put the "Where" case?

- Sep 02, 2020 7:47

Take a look at this old blog post, that should give you examples you need:
https://world.episerver.com/blogs/Shannon-Gray/Dates/2012/12/EPiServer-Commerce-Order-Search-Made-Easy/

There are also example code in the developer guide: https://world.episerver.com/documentation/developer-guides/commerce/orders/Searching-for-orders-using-OrderContext/

Sanjay Kumar

Vote:

Hi Juan,

The where clause should go like this.

StringBuilder sqlQuery = new StringBuilder();
sqlQuery.Append("WHERE OrderFormEx.MyField = " + DataToFind+ " )");

You have to modify query as following:

https://world.episerver.com/documentation/developer-guides/commerce/orders/Searching-for-orders-using-OrderContext/

StringBuilder sqlQuery = new StringBuilder();
sqlQuery.Append("OrderGroupId IN (SELECT li.OrderGroupId From LineItem li ");
sqlQuery.Append("INNER JOIN LineItemEx ex ");
sqlQuery.Append("ON li.LineItemId = ex.ObjectId ");
sqlQuery.Append("INNER JOIN Shipment sh ");
sqlQuery.Append("ON li.OrderGroupId = sh.OrderGroupId ");
sqlQuery.Append("INNER JOIN ShipmentEx shex ");
sqlQuery.Append("ON sh.ShipmentId = shex.ObjectId ");
sqlQuery.Append("WHERE ex.ExpirationDate > '1/1/2011'");
sqlQuery.Append("AND NOT shex.PrevStatus IS NULL)");

OrderSearchParameters parameters = new OrderSearchParameters();
searchOptions.Classes.Add("PurchaseOrder");
parameters.SqlMetaWhereClause = "";
parameters.SqlWhereClause = sqlQuery.ToString();
PurchaseOrder[] purchaseOrderCollection = OrderContext.Current.FindPurchaseOrders(parameters, searchOptions);

#227398

Sep 02, 2020 19:56