Searching Through Serialized Carts (11.8.2)

Member since: 2010

We have a custom property called MerchantReference we have added to our ICart object which is being serialized in to the serialized carts table. I need to be able to search and return a cart based upon the MerchantReference field. I've picked up someone else's code

        /// <summary>
        /// Find a cart object based on MerchantReference
        /// If found, return it
        /// </summary>
        /// <param name="merchantReference">The merchant reference.</param>
        /// <returns>The order or purchase order, or null if not found</returns>
        private IOrderGroup FindCart(string merchantReference)
            OrderSearchOptions searchOptions = new OrderSearchOptions
                Classes = new StringCollection { "ShoppingCart" }

            OrderSearchParameters parameters = new OrderSearchParameters();
            parameters.SqlMetaWhereClause = $"META.MerchantReference = '{merchantReference}'";
            parameters.SqlWhereClause = "OrderGroupId IN (SELECT ObjectId FROM OrderGroup_ShoppingCart)";
            Cart[] cartCollection = OrderContext.Current.FindCarts(parameters, searchOptions);

            if (cartCollection != null && cartCollection.Length > 0)
                return cartCollection.FirstOrDefault(); // Should only be one with the merchant reference

            return null;

Which I think has been written for the old cart system. Does anyone have any examble of how I would do this without writing some direct SQL statements and a like query? I've been trying to find some examples but coming up raw.



#190326 Apr 09, 2018 15:02
  • Member since: 2011

    Unfortunately there is no way to search for serializable carts now - at least for metafield filter you'd want. Due to the nature of how the data is stored, that might be proven to be impossible. 

    If that is critical for businesses we might try to see what we can do - but I can't promise anything just yet

    #190327 Apr 09, 2018 15:05
  • Member since: 2010

    Thanks Quan, I'll have to go down the manual route then which I think will work, just trying to minimze custom SQL and it's the only identifer we have.

    #190328 Apr 09, 2018 15:07
  • Member since: 2016

    I just want to add a comment to second the need for custom fields to be searchable on SerializableCarts.

    In my current project there are plans to move over from the old concrete classes to the new abstraction APIs, Serializable Carts would be a part of that. Due to how our payment provider works we need to be able to identify and retrieve carts based on their ID of a purchase.

    Being able to search for that ID is critical, I'd say.

    As Scott implied there are workarounds, but it would be great if this was supported by the API.

    #190357 Apr 10, 2018 10:43
  • Member since: 2010

    Yeah, I've managed to easily implemented it with a custom SQL query but I'd obviously prefer to have something this is nativly supported and garaunteed to be efficient and future proof.

    #190367 Apr 10, 2018 11:57
  • Member since: 2012

    Is there a need to support actual searches or is filtering enough?

    #190417 Apr 10, 2018 17:22
  • Member since: 2010

    I'm looking for a search on meta field that is against the entire cart collection, e.g. similar to how the order search worked in old carts and works for PurchaseOrders

    #190418 Apr 10, 2018 17:24
  • Member since: 2011

    We actually have a story on implementing the search functionalities for new abstraction APIs in our "backlog". I'll try to push for it - can't promise will get it through, but I'll try.

    @Scott and Jafet: Just so you know EMVPs have a slight boost on priority ;). We of couse appreciate any feedbacks. 

    #190520 Apr 11, 2018 15:46
  • Member since: 2010

    Better work to getting that EMVP then :p Thanks, what I've written seems to be working and we're a cut of point for upgrades at we have moved to a later phase of our UAT rounds but it would be good for future work.

    #190527 Apr 11, 2018 15:59
  • Member since: 2007

    @Scott. Is it possible for you to share how you implemented this functionality in SQL? We've just hit that same brick wall switching over from the old cart system to the serializable cart system. 

    #193157 May 24, 2018 9:42
  • Member since: 2010

    In my case I'm looking for a string value and using 

            public ICart GetCartByMerchantReference(string reference)
                var connectionString = ConfigurationManager.ConnectionStrings["EcfSqlConnection"];
                var sqlString = "SELECT CartId FROM [SerializableCart] WHERE Data Like '%\"MerchantReference\":{\"$type\":\"System.String\",\"$value\":\"" + reference +"\"}%'";
                using (var sqlConnection = new SqlConnection(connectionString.ConnectionString))
                    var cmd = new SqlCommand(sqlString, sqlConnection);
                    var value = cmd.ExecuteScalar();
                    if (value != null)
                        var cartId = (int)value;
                        return _serializableCartProvider.Load(cartId);
                return null;

    You would need to change the type if you're searching for something different and obviously replace it with your meta field name.

    #193158 Edited, May 24, 2018 9:48
First   1 2   Last