--- title: Search for orders using OrderContext slug: commerce-connect-15/docs/search-for-orders-using-ordercontext docTags: createdAt: 2026-07-30T20:34:41.000Z --- 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 - A description of the search stored procedure Order search is a common part of ecommerce solutions, including ERP integrations 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 run an order search in Commerce Connect using the API. Begin the search code with: ```csharp OrderSearchOptions searchOptions = new OrderSearchOptions(); searchOptions.StartingRecord = 0; // Adjust for paging. searchOptions.RecordsToRetrieve = 10000; // Adjust for paging. 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), call Search and return an array of Cart objects. PurchaseOrder[] purchaseOrderCollection = OrderContext.Current.Search(parameters, searchOptions); ``` The SQL query above resolves to `OrderGroupId IN (SELECT OrderGroupId FROM )`, where `
` is determined by the `OrderSearchOptions.Classes` collection. ## Use order search The order search returns arrays of `OrderGroup` objects: `ShoppingCart`, `PaymentPlan`, and `PurchaseOrder`. The order search is **not** intended to return shipments, line items, payment objects, or any other child objects or data of the order. The order search API does not search for those object types directly. The API lets you search for carts, orders, and payment plans by their properties — including metafields associated with any object in an `OrderGroup` — and by joining external database tables for filtering. The search pattern implemented in the API has two parts: 1. **Search phase** – Use the `ecf_OrderSearch` stored procedure to get a list of `OrderGroupId` values matching the criteria. Insert those values into the `OrderSearchResults` table with a GUID that distinguishes them from other search results. 2. **Load phase** – Load the `OrderGroupId` values from `OrderSearchResults` and return the corresponding array of `ShoppingCart`, `PurchaseOrder`, or `PaymentPlan` objects. :::hint{type="info"} **Note** You can sort based on `OrderGroup` table fields only. ::: ## Primary classes ### 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` – Inherited from `SearchOptions`, but not used by order search. - `RecordsToRetrieve` - `StartingRecord` – Integer. Use these fields to page search results. ### Classes `StringCollection` – Specifies the order metadata object on which to create a subquery. For example, to find all shipments where a shipment metafield matches 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 do not specify a `SqlMetaWhereClause` in the parameters object. If you specify only an `OrderGroup` class without a `SqlMetaWhereClause`, the search filters results to 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` – Combined with `OrderSearchOptions.Classes` to form a subquery based on an `OrderGroup` metaclass. For example, `"META.CardType = 'Visa'"` paired with `"CreditCardPayment"` in the `Classes` collection returns all orders, carts, and payment plans paid by Visa. **META** is the SQL alias for the table associated with the class you specify. See [Classes](docId\:VtqEVZVd6bOucmAqAwWr8). - `SqlWhereClause` – A SQL `WHERE` clause that lets you specify additional order search criteria to form a subquery in the search [stored procedure](docId\:VtqEVZVd6bOucmAqAwWr8). The `META` keyword here refers to a different alias than the one used in `SqlMetaWhereClause`, which aliases 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. For example, you could write `OrderGroupId IN (SELECT OrderGroupId FROM Shipment WHERE ShipmentTrackingNumber IS NOT 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 `ORDER BY` clause used to sort results. Use any column from the `OrderGroup` table, or `META.` for columns in the metaclass table (for example, in `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. ## Order search code examples The code is common and first defined for all of the following code examples. ```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. ### Search purchase orders by tracking number pattern and shipment assignment Search for purchase orders with a *tracking number* that is like a particular pattern, and with a tracking number (a metafield) 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); ``` ### Search purchase orders by line item RMA number Search for purchase orders containing line items with an `RMANumber` metafield assigned. ```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** Use the `SqlWhereClause` to ensure that only purchase orders are returned. Otherwise, the dataset may also include carts (or payment plans, if your system has any), and you see errors like `Index 0 is either negative or above rows count`. This happens because the metadata object loader cannot reconcile the different metafields between purchase orders and carts. If you query orders using the API and do not specify which `OrderGroup` object is the metaclass, add this `WHERE` filter to the `SqlWhereClause`. You can make a compound `WHERE` clause that includes this filter. ::: ### Search shopping carts by line item expiration date Search for 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); ``` ### Search purchase orders by shipment shipping status Search for 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); ``` ### Search orders by SKU name substring using a catalog join This more complex SQL `WHERE` clause returns all orders that contain SKUs whose name contains a substring, by joining 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. ### Search orders across multiple metadata tables using joined SQL clauses This pattern joins several order metadata tables in the SQL `WHERE` clause. It is the preferred approach for complex order searches with multiple order metaclasses, rather than relying on the options `Classes` collection. :::hint{type="info"} **Note** The following query may appear to return duplicate `OrderGroupId` values because 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 Server Management Studio first, and remember the closing `)` for your `SqlWhereClause` property value. ::: ## Stored procedure breakdown The first part of the order search runs a dynamic query based on the `OrderSearchParameters` and `OrderSearchOptions` properties set in code. The following is the final query built from the settings you specify. The stored procedure `ecf_OrderSearch` uses these properties to produce a list of order IDs. ```sql 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 metadata query is built from the class you specify and the `SqlMetaWhereClause` (described above). The **META** where clause inside `SqlMetaWhereClause` is different from the META alias available to the `SqlWhereClause`. - If no `SqlWhereClause` is specified, the query runs without the `AND [SqlWhereClause]` clause. The same applies to `SqlMetaWhereClause`. - Commerce Connect manages the application ID internally. :::