How do I create a SqlMetaWhereClause

November Happy Hour will be moved to Thursday December 5th.

AI OnAI Off

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

How do I create a SqlMetaWhereClause

Joe Douglas

Vote:

I have two functions

        public static ICart GetCartFromCustomerId(Guid CustomerId)
        {
            try
            {
                OrderSearchOptions searchOptions = new OrderSearchOptions();
                searchOptions.CacheResults = false;
                searchOptions.StartingRecord = 0;
                searchOptions.RecordsToRetrieve = 1000000000;
                searchOptions.Namespace = "Mediachase.Commerce.Orders";

                OrderSearchParameters parameters = new OrderSearchParameters();
                searchOptions.Classes.Add("ShoppingCart");
                parameters.SqlMetaWhereClause = $"META.ContactIdGuidAsString = '{CustomerId.ToString()}'";     // see get values from [MetaFieldId]
                parameters.SqlWhereClause = "OrderGroupId IN (Select ObjectId FROM OrderGroup_ShoppingCart)";
                Cart[] cartCollection = OrderContext.Current.FindCarts(parameters, searchOptions);

                if (cartCollection != null && cartCollection.Any())
                    return cartCollection.Select(x => (ICart)x).Where(x => x.CustomerId == CustomerId).OrderByDescending(x => x.Created).ToList().FirstOrDefault();
            }
            catch (Exception ex)
            {
                Log.LogError($"**** COMMERCE ERROR: EpiserverCommerceHelpers.GetCartFromCustomerId() has thrown an exception: {ex.Message}");
            }

            return null;
        }

and

        public static IPurchaseOrder RecoverPurchaseOrder2(string OrderNumber)
        {
            try
            {
                OrderSearchOptions searchOptions = new OrderSearchOptions();
                searchOptions.CacheResults = false;
                searchOptions.StartingRecord = 0;
                searchOptions.RecordsToRetrieve = 1000000000;
                searchOptions.Namespace = "Mediachase.Commerce.Orders";

                OrderSearchParameters parameters = new OrderSearchParameters();
                searchOptions.Classes.Add("PurchaseOrder");
                parameters.SqlMetaWhereClause = $"META.TrackingNumber = '{OrderNumber}'";   // see get values from [MetaFieldId]
                parameters.SqlWhereClause = "OrderGroupId IN (Select ObjectId FROM OrderGroup_PurchaseOrder)";
                PurchaseOrder[] purchaseOrderCollection = OrderContext.Current.FindPurchaseOrders(parameters, searchOptions);

                if (purchaseOrderCollection != null && purchaseOrderCollection.Any())
                    return purchaseOrderCollection.Select(x => (IPurchaseOrder)x).Where(x => x.OrderNumber.ToUpper().Trim() == OrderNumber.ToUpper().Trim()).OrderByDescending(x => x.Created).ToList().FirstOrDefault();
            }
            catch (Exception ex)
            {
                Log.LogError($"**** COMMERCE ERROR: EpiserverCommerceHelpers.RecoverPurchaseOrder2() has thrown an exception: {ex.Message}");
            }

            return null;
        }

but every time I try to create the SqlMetaWhereClause, the query fails.

All I am trying to do is get the Cart given the customerId and get the PO given the OrderNumber with the above respective functions but I am forced down a route constructing these weird sql-like c# construction that always lead no where.

The functions work if you take out the SqlMetaWhereClauses. I suppose my question is: How do I create SqlMetaWhereClauses for any problem? What is the secret to constructing these things.

#191124

Apr 20, 2018 13:48

Quan Mai

Vote:

What do you mean by "Fails" - the query throws an exception, or it returns no row?

For for first query - as I commented on the ticket, you can use Cart.LoadCartsByCustomerAndName(Guid customerId, string name)

And in both case, the SqlWhereClause is not needed.

#191126

Apr 20, 2018 14:53

Joel Yourstone

Vote:

A bit off topic, but I'm pretty sure searchOptions.CacheResults doesn't do anything, it won't be cached regardless. Do you have some insight there, Quan?

#191131

Apr 20, 2018 16:22

Joe Douglas

Vote:

Quan,

In the function you quoted: Cart.LoadCartsByCustomerAndName(Guid customerId, string name), is name the cart name?

Do you know whether I can set name to an empty string and still get back carts relating to the customerId?

Incidently FYI, I've now got SqlMetaWhereClause working for recovering the PO given the OrderNumber which is now:

        public static IPurchaseOrder RecoverPurchaseOrder2(string OrderNumber)
        {
            try
            {
                OrderSearchOptions searchOptions = new OrderSearchOptions();
                searchOptions.CacheResults = false;
                searchOptions.StartingRecord = 0;
                searchOptions.RecordsToRetrieve = 1; // 1000000000;
                searchOptions.Namespace = "Mediachase.Commerce.Orders";

                OrderSearchParameters parameters = new OrderSearchParameters();
                searchOptions.Classes.Add("PurchaseOrder");
                parameters.SqlMetaWhereClause = $"META.TrackingNumber = '{OrderNumber}'";   // see get values from [MetaFieldId]
                parameters.SqlWhereClause = $"OrderGroupId IN (SELECT TOP {searchOptions.RecordsToRetrieve} ObjectId FROM OrderGroup_PurchaseOrder ORDER BY Created DESC)";
                PurchaseOrder[] purchaseOrderCollection = OrderContext.Current.FindPurchaseOrders(parameters, searchOptions);

                if (purchaseOrderCollection != null && purchaseOrderCollection.Any())
                    return purchaseOrderCollection.Select(x => (IPurchaseOrder)x).Where(x => x.OrderNumber.ToUpper().Trim() == OrderNumber.ToUpper().Trim()).OrderByDescending(x => x.Created).ToList<IPurchaseOrder>().FirstOrDefault();
            }
            catch (Exception ex)
            {
                Log.LogError($"**** COMMERCE ERROR: EpiserverCommerceHelpers.RecoverPurchaseOrder2() has thrown an exception: {ex.Message}");
            }

            return null;
        }

#191132

Apr 20, 2018 16:32

Quan Mai

Vote:

No,you have to set a name. However when I looked into your database, all your carts are named Default, so you can just use that.

And the second one, you can remove the SqlWhereClause completely because you are already looking into PurchaseOrder.

#191133

Apr 20, 2018 16:36

Quan Mai

Vote:

Good question. No it is not working in this case - I just checked. It is used a little bit for catalog searching, but not orders.

Each order is cached individually, however

#191134

Apr 20, 2018 16:37

Joe Douglas

Vote:

Quan,

Your suggestion for getting the cart worked and the performance problem we were having has now been resolved.

Thanks

#191141

Apr 20, 2018 17:38

Joe Douglas

Vote:

#191142

Apr 20, 2018 17:39

Joel Yourstone

Vote:

On database level? Or how do you mean cached individually? When I reflected the code and saw what I thought was a broken cache, I tried it as well, always leading to a SP call.

#191172

Apr 23, 2018 11:27

Quan Mai

Vote:

Each order will be cached by synchronized object item cache. However for search it'll be very tricky to implement cache on search results. The CacheResults is more likely an accident - as it is shared between catalog and order search. It should have been a property in CatalogSearchOptions only. It's safe to say that search for order is not cached.

#191173

Apr 23, 2018 11:31

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.