Search in PurchaseOrder

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 02, 2020 7:40
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?
Mari Jørgensen - 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/
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
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.