Getting an item by a meta property (Commerce 11.2.1)

Scott Reed

Vote:

Hi guys,

I have a variant class model called CourseScheduleVariant with a MetaClassName of Course_Schedule_Product. I'm trying to use the catalog search to get an entry that has a field called SystemId as defined below

        /// 
        /// Gets or sets the identifier.
        /// 
        /// The identifier.
        [Display(
            Name = "System Id",
            Description = "The unique ID of category in the SJA system",
            GroupName = SystemTabNames.Content,
            Order = 10)]
        [CultureSpecific]
        [IncludeInDefaultSearch]
        [Searchable]
        [Tokenize]
        public virtual int SystemId { get; set; }

I'm trying the code below as taken from docs and some forum posts to try and get my record

            var parameters = new CatalogSearchParameters
            {
                SqlMetaWhereClause = "META.SystemId = 713499"
            };

            var options = new CatalogSearchOptions
            {
                RecordsToRetrieve = 1,
                CacheResults = false,
                StartingRecord = 0,
                ReturnTotalCount = true
            };

            options.Classes.Add("Course_Schedule_Product");

            var result = CatalogContext.Current.FindItems(parameters, options, 
                new CatalogEntryResponseGroup(CatalogEntryResponseGroup.ResponseGroup.CatalogEntryFull));

But I'm getting an error that the field doesn't exist. Can someone help?

#182154

Edited, Sep 11, 2017 17:38

Quan Mai

Vote:

The code looks correct to me. You might try to remove

options.Classes.Add("Course_Schedule_Product");

Btw, these attributes are for indexed search, not the catalog search you're using

        [IncludeInDefaultSearch]
        [Searchable]
        [Tokenize]

#182158

Sep 11, 2017 23:09

David Knipe

Vote:

It might not fit your case but you can always use Find for this too (assuming you are using Find)?

#182160

Sep 12, 2017 0:49

Scott Reed

Vote:

Hi guys, firstly thanks Quan, I thought this catalog search was supposed to be a direct database call but it wasn't 100% obvious from the docs and the fact I couldn't seem to get any results back. The fields on the properties are there for when we implement Episerver Find at a later date.

David we are planning to implement Find but a) That's due on a later iteration b) This is an import & update process that's very large and was weary about constantly using up Find searches for a backend import.

I think I tried without the classes attribute but will try again. At the moment I've spent nearly a day trying to just pull out a record based on a meta field.

#182171

Edited, Sep 12, 2017 9:20

Scott Reed

Vote:

FYI I did consider using standard lucence at least for now to get this item out but even with all these fields set to index I've checked the lucene index with Luke and some of these fields are morning

#182173

Sep 12, 2017 9:23

Scott Reed

Vote:

Tried removing

options.Classes.Add("Course_Schedule_Product");

exactly the same error, I've tried the system name and friendly name as I see it in commerce manager and this is all I get

Error Image

#182175

Edited, Sep 12, 2017 9:27

Quan Mai

Vote:

I forgot to ask the very important question: which version are you using?

#182176

Sep 12, 2017 9:29

Scott Reed

Vote:

Ah sorry I forgot to pop it in the title, I updated to latest Episerver yesterday so I'm running 11.2.1

#182179

Sep 12, 2017 9:57

Quan Mai

Vote:

Now it would help if you post your class file. And if you look into your database, do you find any view named

Course_Schedule_Product

#182185

Sep 12, 2017 10:04

Scott Reed

Vote:

I've been looking at the database, I can see a view called CatalogEntryEx_Course_Schedule_Product with my value in and I have a view for all the other meta classes. Also the CatalogContentProperty has this correctly stored against the number field, I considered a manual query but I'd rather not be playing around with manual SQL when the catalog Should be able to do this

#182186

Edited, Sep 12, 2017 10:08

Quan Mai

Vote:

In Commerce 9 and later, the way you set SqlMetaWhereClause is changed, so it must be like this  searchParams.SqlMetaWhereClause = " META.MetaFieldName = 'SystemId' AND META.Number = '12345'"; And yes, you are totally correct that you should not use direct SQL if there is an API for that feature.

#182188

Sep 12, 2017 10:20

Scott Reed

Vote:

Ah I see it's documentated here http://world.episerver.com/documentation/developer-guides/commerce/catalogs/Catalog-product-search/ thanks for this I've manged to get it working without the classes so trying to add those now to test. As it's a number I did have to change the query slightly to

searchParams.SqlMetaWhereClause = " META.MetaFieldName = 'SystemId' AND META.Number = 12345";

#182190

Sep 12, 2017 10:40

Scott Reed

Vote:

P.s I can't mark you as an answer at the moment as I'm getting a "Something went wrong screen when trying" typical haha.

Thanks you very much, first time with commerce and knowing CMS like the back of my hand it's very interesting :-)

#182193

Sep 12, 2017 10:42

Quan Mai

Vote:

No problem - glad it worked for you :). I know the search query was changed in Commerce 9, but I always forgot how was it changed.

#182194

Sep 12, 2017 10:54

Scott Reed

Vote:

Yeh, I always find with any Episerver documentation the most annoying thing is google searches always end up being to the wrong version and it's not obvious always that you're then looking at outdated docs. But thanks again:-)

#182196

Sep 12, 2017 10:56

Scott Reed

Vote:

hi Quan, I wondered if you might know this. The CatalogContext is working for Product and Variants however it's not working for Category (NodeContent) do you know if there's a different conext for this to work?

#182289

Sep 14, 2017 16:20

Quan Mai

Vote:

I guess you meant CatalogContext.Current.FindItems? It only works with entries (products, variants, packages, bundles), not category.

The only way to find category is to use Find.Commerce. => WRONG. You can use FindNodesDto or FindCatalogItemsTable

#182290

Edited, Sep 14, 2017 16:24

Scott Reed

Vote:

Thanks for the update, personally I find that really dissapointing I've just built my who import process around the catalogue being updated from our clients third party service around the use of the CatalogContext.Current.FindItems. I wouldn't want to be using find for something that's is a scheduled job update process that will be running lots and ltos and could causes masses of find queries. It didn't occur to me that categories wouldn't be in this, as I can query these items in the exact same way in the database and contextually I thought that the top level "Catalog" item was the category so there would be no reason in this not working.

Thanks anyhow, I'm just finding everything takes 3 times as long to do anything in Commerce

#182291

Sep 14, 2017 16:30

Quan Mai

Vote:

I was wrong. You can use FindNodesDto or FindCatalogItemsTable

#182292

Sep 14, 2017 16:35

Scott Reed

Vote:

Fantastic, thanks Quan you're the gman I'll have a look at these :-) Sorry for the rant :-)

#182293

Sep 14, 2017 16:36

Scott Reed

Vote:

I've tried using the FindItemsDto instead and I get no results back. it accepts the same options so I assume there's nothing different paramater wise CatalogContext.Current.FindItems?

#182295

Sep 14, 2017 17:05

Quan Mai

Vote:

Make sure you set the options.Classes and parameters.SqlMetaWhereClause correctly - better remove them ...

#182296

Sep 14, 2017 17:22

Scott Reed

Vote:

As you suggested removing the classes and also as I couldn't get the queries to work with the CatalogContext.Current.FindItems when using the classes options I removed them. The SqlMetaWhereClause is set correct as I've built a builder for these queries and it's working fine when using the CatalogContext.Current.FindItems it's just not doing anything when using FindItemsDto. I'll try adding in the classes to see if it makes a difference

#182297

Sep 14, 2017 17:30

Scott Reed

Vote:

FYI I've still not managed to get the FindItemsDto working to find a category I'm using

    var parameters = new CatalogSearchParameters
    {
        SqlMetaWhereClause = " META.MetaFieldName = 'Id' AND  META.LongString = 'TARM'"
    };

    var options = new CatalogSearchOptions
    {
        RecordsToRetrieve = 1,
        CacheResults = false,
        StartingRecord = 0,
        ReturnTotalCount = true
    };

    var outcount = 1;

    var result = CatalogContext.Current.FindItemsDto(parameters, options, ref outcount,
        new CatalogEntryResponseGroup(CatalogEntryResponseGroup.ResponseGroup.CatalogEntryFull));

And I get no results, doing this query in the CatalogContentProperty table returns a result.

Any ideas? I've tried without the metafield name as well

#182498

Sep 21, 2017 13:07

Quan Mai

Vote:

"FindNodesDto or FindCatalogItemsTable"

You just can't find categories by FindItemsDto

#182499

Sep 21, 2017 13:19

Scott Reed

Vote:

Apologies I misread I've tried switching to

    var parameters = new CatalogSearchParameters
    {
        SqlMetaWhereClause = " META.MetaFieldName = 'Id' AND Meta.LongString = 'PUFA'"
    };

    var options = new CatalogSearchOptions
    {
        RecordsToRetrieve = 1,
        CacheResults = false,
        StartingRecord = 0,
        ReturnTotalCount = true
    };

    var outcount = 1;

    var result = CatalogContext.Current.FindNodesDto(parameters, options, ref outcount, new CatalogNodeResponseGroup(CatalogNodeResponseGroup.ResponseGroup.CatalogNodeFull));

And it's not working either, I've again tried with and without the metafieldname.

Thanks for all the help on this

#182500

Sep 21, 2017 13:46

Quan Mai

Vote:

I just tested and it works for me. Are you sure that you have a metafield named "Id"? What do you get if you run this query

SELECT * FROM CatalogContentProperty WHERE ObjectTypeId = 1 AND MetaFieldName = 'Id' AND LongString = 'PUFA' ?

#182502

Sep 21, 2017 14:16

Scott Reed

Vote:

Yes, I've already checked by doing a select in the database myself and just double checked which was why I was confused.

#182503

Edited, Sep 21, 2017 14:20

Scott Reed

Vote:

Okay, I added logging on the node search stored procedure ecf_CatalogNodeSearch stored procedure to log the params and the final @FullQuery. If I replicate what's being searched from calling this stored procedure directly I get a record in the CatalogNodeSearchResults table in which the CatalogNodeId is correct to my desired item. I can run the logged @FullQuery output when coming from code and run this and again the record in this table is generated, however if I run this from code I get no records returned and no records in this table. I've decompiled this code and I can't see this code deleting these records after the query unless i missed it so I'm sonfused as to my these records aren't appearing

#182505

Sep 21, 2017 16:02