Searching Through Serialized Carts (11.8.2)

Vote:
 

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
Vote:
 

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
Vote:
 

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
Vote:
 
<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
Vote:
 

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
Vote:
 

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

#190417
Apr 10, 2018 17:22
Vote:
 

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
Vote:
 

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
Vote:
 

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
Vote:
 

@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
Vote:
 

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
Vote:
 
<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
Vote:
 

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
Vote:
 

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
Vote:
 

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
Vote:
 

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
Vote:
 

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
Vote:
 

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
Vote:
 

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
Vote:
 

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
Vote:
 

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
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.