Searching Through Serialized Carts (11.8.2)

Scott Reed
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

        /// 
        /// Find a cart object based on MerchantReference
        /// If found, return it
        /// 
        /// The merchant reference.
        /// The order or purchase order, or null if not found
        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.

Thanks,

Scott

#190326 Apr 09, 2018 15:01
  • Quan Mai
    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
  • Scott Reed
    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
  • Jafet Valdez
    Member since: 2016
     
    <p>I just want to add a comment to second the need for custom fields to be searchable on SerializableCarts.</p> <p>In my current project there are plans to move over from the old concrete&nbsp;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.</p> <p>Being able to search for that ID is critical, I'd say.</p> <p>As Scott implied there are workarounds, but it would be great if this was supported by the API.</p>
    #190357 Apr 10, 2018 10:43
  • Scott Reed
    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
  • Erik Norberg
    Member since: 2012
     

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

    #190417 Apr 10, 2018 17:22
  • Scott Reed
    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
  • Quan Mai
    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
  • Scott Reed
    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
  • martin.emanuelsson
    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
  • Scott Reed
    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);
                    sqlConnection.Open();
    
                    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
  • martin.emanuelsson
    Member since: 2007
     
    <p>Perfect Scott! Thanks a lot!&nbsp;</p> <p>I've also just stumbled across a mentioning of&nbsp;SerializableCartDB in a comment (documentation!) on&nbsp;<a href="/documentation/developer-guides/commerce/orders/serializable-carts/">https://world.episerver.com/documentation/developer-guides/commerce/orders/serializable-carts/</a>&nbsp;which sounds promising, will give that a try first, then look into your SQL-query :-)&nbsp;</p>
    #193159 May 24, 2018 9:50
  • Scott Reed
    Member since: 2010
     

    No worries, yeah I've seen that an as far as I remember although SerializableCartDB has a find method I think it's limited to dates and customer searching and doesn't have the same filtering paramters that the old cart/order system did for meta fields so you'd have to load all the carts and search in memory which would be expensive, hence my choice of this implementation.

    #193160 May 24, 2018 9:54
  • martin.emanuelsson
    Member since: 2007
     

    True, can only load all carts based on some "basic" criterias which might be quite expensive as you say. 

    #193161 May 24, 2018 9:56
  • Matthew Boniface
    Member since: 2009
     

    As per above thread, do we know if/when there will be a Cart search API to work with the serializablecarts? 

    I'm trying to load all carts starting with a specific name, e.g. "WHERE Name like 'prefix%' ", which would have been simple with the OrderContext.Current.FindCarts OrderSearchParameters API. But don't think there's any way to achieve this with current API's (I'm on Commerce v11.8.5).

    Scott's simple example might be our temporary way to achieve this.

    Just keen to know if this is something we can look forward to :)

    #195889 Aug 10, 2018 8:01
  • Quan Mai
    Member since: 2011
     

    There will be a cart search API, however it will not be an intensive API, it's not even public (will be in internal namespace, for instance). It will be quite basic, actually.

    I understand the need for a real search API, but currently that is not an easy problem to solve, given the priority 

    #195890 Aug 10, 2018 8:51
  • Matthew Boniface
    Member since: 2009
     

    Hi Quan

    I understand it'll be constrained by the limits of the serialized data, but all I needed was to filter by the name with a wildcard which is very simplistic in SQL and performs okay.

    Look forward to being able to use an API but I'll be okay code similar to Scott's for the meantime :)

    Thanks for the reply and info.

    Matt 

    #195925 Aug 13, 2018 0:12
  • Maris Krivtezs
    Member since: 2012
     

    Latest SQL Server (starting from 2016) has JSON column support:

    https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-2017

    If Episerver would drop support for older SQL Servers then could use this. Or create a separate library which adds support for this for projects on latest SQL Server.

    With JSON column, it would be possible to search, filter and do whatever is needed with serialized carts.

    #195926 Aug 13, 2018 8:09
  • Quan Mai
    Member since: 2011
     

    Drop support for old versions is easier said than done, especially for a business critial and (IMO) expensive software like SQL Server. Yes for me I would like to try JSON support,but that is not feasible to do in a framework level. Many customers will be angry to find out they will have to upgrade SQL Server just for that feature.

    #195928 Aug 13, 2018 8:53
  • Maris Krivtezs
    Member since: 2012
     

    Yes, agree.

    But there could be other options to support JSON column. Add some configuration which would allow choosing which version of SQL Server to use. Then Episerver could initialize proper column type. And additional library (NuGet package) could add support for search in serialized carts.

    #195929 Aug 13, 2018 9:14
  • Scott Reed
    Member since: 2010
     

    It's not actually a native JSON type it's just stored as an NVARCHAR type https://blogs.msdn.microsoft.com/jocapc/2015/05/16/json-support-in-sql-server-2016/ but there's a set of libraries for manipulating and handling the JSON in queries and indexing such as https://docs.microsoft.com/en-us/sql/relational-databases/json/index-json-data?view=sql-server-2017 . So i think it will be pretty much the same for handling apart from the spocs and indexing on the database would need to be optimized for the newer version so the queries and speed via indexing is faster. Underlying code for accessing the spocs I would think could possibly stay the same. To be honest I think my SQL search script I posted could be migrated to a possibly support this and an index on without affecting Episerver. 

    Something I might look in to.

    #196040 Aug 16, 2018 18:06