Search in PurchaseOrder

Five New Optimizely Certifications are Here! Validate your expertise and advance your career with our latest certification exams. Click here to find out more

AI OnAI Off

Home / Forums / Developers (Main Products) Forum / Customized Commerce 13 and earlier versions /

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

This topic was created over six months ago and has been resolved. If you have a similar question, please create a new topic and refer to this one.