--- title: Search for orders using OrderContext slug: commerce-connect-14/docs/searching-for-orders-using-ordercontext createdAt: 2026-07-23T10:25:34.040Z --- This topic describes how to work with the order search API, including: - The capabilities and limitations of Optimizely Commerce Connect order search - API classes and properties - Code examples of different scenarios - Description of the search stored procedure Searching for orders is a common part of ecommerce solutions, such as integrating with ERPs and custom administrative interfaces where orders are exported, filtered, or edited. You often need to load orders based on submission date, status, or particular properties or meta-field properties of the order or cart, including properties of its child objects (for example, shipments or line items). Classes in this topic are available in the `Mediachase.Commerce.Orders` namespace. ## Search pattern The following pattern is the simplest way to execute order search in Commerce Connect using the API. Begin the search code with: ```csharp OrderSearchOptions searchOptions = new OrderSearchOptions(); searchOptions.StartingRecord = 0; //or whatever you want to specify for paging purposes searchOptions.RecordsToRetrieve = 10000; //or whatever you want to specify for paging purposes searchOptions.Namespace = "Mediachase.Commerce.Orders"; ``` Then continue with: ```csharp OrderSearchParameters parameters = new OrderSearchParameters(); //the following is for returns-only purchase orders... for shopping carts, specify ShoppingCart parameters.SqlMetaWhereClause = ""; searchOptions.Classes.Add("PurchaseOrder"); parameters.SqlWhereClause = sqlQuery.ToString(); //If you are searching for shopping carts (non serializable cart system), you can call the Search method and return an array of Cart objects PurchaseOrder[] purchaseOrderCollection = OrderContext.Current.Search(parameters, searchOptions); ``` The SQL query above is `OrderGroupId IN (SELECT OrderGroupId FROM .)`. ## Use order search The order search returns arrays of `OrderGroup` objects: `ShoppingCart`, `PaymentPlan`, and `PurchaseOrder`. The order search is only intended to return shipments, line items, payment objects or any other objects or data in the order. The Order Search API does not conduct an order search for those types of objects alone. The API lets you search for carts, orders, and payment plans based on properties, including metafields associated with any of the objects in an `OrderGroup` and any outside database tables you may want to use for filtering or joins. The search pattern implemented in the API has two parts: 1. Get a list of `OrderGroupIds` as search results using the stored procedure `ecf\_OrderSearch`. Insert those values into a database table `OrderSearchResults` with a GUID that separates it from other search results. This is the search phase, based on criteria passed to the search. 2. The `OrderGroupIds` are loaded from O`rderSearchResults`, and the corresponding array of `ShoppingCart`, `PurchaseOrder`, and `PaymentPlan` is loaded and returned. :::hint{type="info"} **Note** You can sort based on `OrderGroup` table fields only. ::: ## Primary classes and properties ### Mediachase.Commerce.Orders.Search.OrderSearchOptions This class lets you specify the number of records to return, if results are to be cached, the type of `OrderGroup` data to be returned, and the starting record number (for paging). ### Properties - `CacheResults` – This is inherited from `SearchOptions` but is not actually used by order search. - `RecordsToRetrieve` - `StartingRecord` – Integer. Use these fields to page search results. ### Classes `StringCollection` – This lets you specify the order metadata object on which you want to create a subquery. For example, to find all shipments where a shipment metafield value is a particular value, specify *ShipmentEx* for the classes property. This is a string collection. In most cases, you only need to specify one class. You can specify the following classes with the corresponding database table that is queried: - `CashCardPayment (OrderFormPayment_CashCard)` - `CreditCardPayment (OrderFormPayment_CreditCard)` - `ExchangePayment (OrderFormPayment_Exchange)` - `GiftCardPayment (OrderFormPayment_GiftCard)` - `InvoicePayment (OrderFormPayment_Invoice)` - `LineItemEx (LineItemEx)` - `OrderFormEx (OrderFormEx)` - `OrderGroupAddressEx (OrderGroupAddressEx)` - `OtherPayment (OrderFormPayment_Other)` - `PaymentPlan (OrderGroup_PaymentPlan)` - `PurchaseOrder (OrderGroup_PurchaseOrder)` - `ShipmentEx (ShipmentEx)` - `ShoppingCart (OrderGroup_ShoppingCart)` :::hint{type="info"} **Note** You must specify at least one class, even if you don't specify a `SQLMetaWhereClause` in the parameters object. If you do not specify `SQLMetaWhereClause` but specify an `OrderGroup`, it filters the results for that type only. ::: ### Namespace For order searches, this should always be `Mediachase.Commerce.Orders`. ### Mediachase.Commerce.Orders.Search.OrderSearchParameters This class lets you specify where clauses necessary to narrow the order search. **Properties** - `OrderByClause` – This clause can use only `OrderGroup` table fields. If this field is not set, `OrderGroupId` is used. - `SqlMetaWhereClause` – This field matches with the `OrderSearchOptions.Classes` field to form a subquery based on an `OrderGroup` metaclass. This clause looks like this: `"META.CardType = 'Visa'"` where the option classes collection contains `"CreditCardPayment"`. That returns all orders, carts, and payment plans with visa payments. **META** is the SQL alias for the table associated with the class you specify. See [Classes](docId\:cSpxntMgZiiZwJL9zEJ6U). - `SqlWhereClause` – A SQL `WHERE` clause that lets you specify additional order search criteria to form a subquery in the search [stored procedure](docId\:cSpxntMgZiiZwJL9zEJ6U). Using the `META` keyword here refers to a different alias than using it in the `SqlMetaWhereClause`, where it is an alias for the table associated with the class specified in the options object. In the `SQLWhereClause`, it refers to a table in memory that has two columns: - **Key** – The `OrderGroupId`. - **Rank** – Part of the meta `WHERE` clause subquery. (You probably do not want to use META here.) Only the `OrderGroup` table is referenced directly in the SQL `WHERE` clause. So, you could have a clause like `OrderGroupId IN (SELECT OrdergroupId FROM Shipment WHERE NOT ShipmentTrackingNumber IS NULL)`. :::hint{type="info"} **Note** You cannot add an `ORDER BY` clause here because it is part of a subquery. ::: The following fields are *not* used for order search: - `JoinSourceTable` - `JoinSourceTableKey` - `JoinTargetQuery` - `JoinTargetTableKey` - `JoinType` `OrderByClause` – A SQL `OrderBy` clause that you can order the record. You can use any column name of columns in `OrderGroup` table, or `META.` for columns in the metaclass table. For example, `OrderGroup_PurchaseOrder`. ### Mediachase.Commerce.Orders.OrderContext This class is a singleton with several methods to find orders. - `FindActiveOrders()` – Retrieves purchase orders where the `OrderGroup.Status` is either `InProgress` or `PartiallyShipped`. - `Current.Search()` – Finds shopping carts based on parameters and options specified with the two other classes. - `FindPaymentPlans()` – Finds payment plans based on parameters and options specified with the two other classes. - `FindPurchaseOrders()` – Finds purchase orders based on parameters and options specified with the two other classes. - `FindPurchaseOrdersByStatus()` – Retrieves orders with particular order statuses. ## Code examples The code is common and first defined for all of the following code examples. Sample code ```csharp OrderSearchOptions searchOptions = new OrderSearchOptions(); searchOptions.StartingRecord = 0; searchOptions.RecordsToRetrieve = 10000; searchOptions.Namespace = "Mediachase.Commerce.Orders"; ``` The following examples have code that follows the code above. **Example 1** Retrieves purchase orders with a *tracking number* that is like a particular pattern, and with a tracking number (a meta-field) assigned to at least one shipment in the purchase order. ```csharp OrderSearchParameters parameters = new OrderSearchParameters(); searchOptions.Classes.Add("PurchaseOrder"); parameters.SqlMetaWhereClause = "META.TrackingNumber LIKE '%PO%'"; parameters.SqlWhereClause = "OrderGroupId IN (SELECT OrdergroupId FROM Shipment WHERE NOT ShipmentTrackingNumber IS NULL)"; PurchaseOrder[] purchaseOrderCollection = OrderContext.Current.Search(parameters, searchOptions); ``` **Example 2** Retrieves purchase orders containing line items with a `RMANumber` (a meta-field) associated with them. ```csharp OrderSearchParameters parameters = new OrderSearchParameters(); searchOptions.Classes.Add("LineItemEx"); parameters.SqlMetaWhereClause = "NOT META.RMANumber IS NULL"; parameters.SqlWhereClause = "OrderGroupId IN (Select ObjectId FROM OrderGroup_PurchaseOrder)"; PurchaseOrder[] purchaseOrderCollection = OrderContext.Current.Search(parameters, searchOptions); ``` :::hint{type="info"} **Note** The `SqlWhereClause` is used to ensure that only purchase orders are returned. If you do not do this, you may get carts (or payment plans, if they exist in your system) also in the dataset, and you will see errors like *Index 0 is either negative or above rows count*. This is because the metadata object load is getting confused by the different metafields in a purchase order instead of a cart. If you query orders using the API and do not specify which `OrderGroup` object as the metaclass, add this where filter in the `SQLWhereClause`. You can make a compound `WHERE` clause including this filter. ::: **Example 3** Retrieves shopping carts where line items have an expiration date (a metafield) greater than a particular value. ```csharp OrderSearchParameters parameters = new OrderSearchParameters(); searchOptions.Classes.Add("LineItemEx"); parameters.SqlMetaWhereClause = "META.ExpirationDate > '1/1/2012'"; parameters.SqlWhereClause = "OrderGroupId IN (Select ObjectId FROM OrderGroup_ShoppingCart)"; Cart[] cartCollection = OrderContext.Current.Search(parameters, searchOptions); ``` **Example 4** Retrieves purchase orders that have shipments with a specific shipping status (as defined by a metafield). ```csharp OrderSearchParameters parameters = new OrderSearchParameters(); searchOptions.Classes.Add("ShipmentEx"); parameters.SqlMetaWhereClause = "NOT META.PrevStatus IS NULL"; parameters.SqlWhereClause = "OrderGroupId IN (Select ObjectId FROM OrderGroup_PurchaseOrder)"; PurchaseOrder[] purchaseOrderCollection = OrderContext.Current.Search(parameters, searchOptions); ``` **Example 5** This is a more complex SQL where clause that returns all orders that contain SKUs whose name contains a substring by using a join with the `CatalogEntry` table. ```csharp StringBuilder sqlQuery = new StringBuilder(); sqlQuery.Append("OrderGroupId IN (SELECT OrderGroupId From LineItem li "); sqlQuery.Append("INNER JOIN CatalogEntry en "); sqlQuery.Append("ON li.CatalogEntryId = en.Code "); sqlQuery.Append("WHERE en.Name LIKE '%Wine%')"); OrderSearchParameters parameters = new OrderSearchParameters(); searchOptions.Classes.Add("PurchaseOrder"); parameters.SqlMetaWhereClause = ""; parameters.SqlWhereClause = sqlQuery.ToString(); PurchaseOrder[] purchaseOrderCollection = OrderContext.Current.Search(parameters, searchOptions); ``` :::hint{type="info"} **Note** You do not have an `SqlMetaWhereClause` but do specify a `PurchaseOrder` class. ::: You can add any subquery, allowing the most flexibility. **Example 6** Joins several order metadata tables in the SQL where clause. This is a better, easier way to do more complex order searches with multiple order meta-classes, rather than using the options `Classes` collection to filter order metaclasses. :::hint{type="info"} **Note** The following query appears to have the potential to return duplicate ordergroupids, as an order can have multiple shipments or line items. However, the order search stored procedure ensures that only distinct order IDs are returned. ::: ```csharp 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.Search(parameters, searchOptions); ``` :::hint{type="info"} **Note** Always test queries in SQL Management Studio first, and remember the closing `)` for your `SqlWhereClause` property value. ::: ## Stored procedure breakdown The first part of the order search performs a dynamic search for orders based on the `OrderSearchParameters` and `OrderSearchOption` properties set in code. This is the final query built based on settings you specify. The stored procedure that uses these properties to create a list of order ids is `ecf\_OrderSearch`. ```csharp declare @Page_temp table (TotalRecords int, OrderGroupId int); with OrderedResults as (SELECT count([OrderGroup].OrderGroupId) OVER() TotalRecords, [OrderGroup].OrderGroupId, Rank, ROW_NUMBER() OVER(ORDER BY ) RowNumber FROM [OrderGroup] OrderGroup INNER JOIN (select distinct U.[KEY], U.Rank from (select 100 as 'Rank', META.ObjectId as 'Key', * from META WHERE ) U) META ON OrderGroup.[OrderGroupId] = META.[KEY] ()) INSERT INTO @Page_temp (TotalRecords, OrderGroupId) SELECT top() TotalRecords, OrderGroupId FROM OrderedResults WHERE RowNumber > ;; select @RecordCount = TotalRecords from @Page_temp; INSERT INTO OrderSearchResults (SearchSetId, OrderGroupId) SELECT [SearchSetId], OrderGroupId from @Page_temp; ``` :::hint{type="info"} - **Note** The query for metadata is built using the class you specify and the `SQLMetaWhereClause` you specify (already mentioned above). The **META** where clause inside the `SQLMetaWhereClause` is different from the META alias available to the `SQLWhereClause`. - If no `SQLWhereClause` is specified, the query executes without the `AND [SQLWhereClause]` portion. The same goes for `SQLMetaWhereClause`. - The Application ID is managed internally by Commerce Connect. :::