Our monthly Virtual Happy Hour is happening this week, Fri Feb 23!

Try our conversational search powered by Generative AI!

Getting an item by a meta property (Commerce 11.2.1)

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

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

#182176
Sep 12, 2017 9:29
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
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
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
Vote:
 
<p>In Commerce 9 and later, the way you set&nbsp;<span>SqlMetaWhereClause is changed, so it must be like this&nbsp;</span></p> <p><span>searchParams</span><span>.</span><span>SqlMetaWhereClause</span>&nbsp;<span>=</span>&nbsp;<span>"&nbsp;META.MetaFieldName&nbsp;=&nbsp;'SystemId'&nbsp;AND&nbsp;META.Number =&nbsp;'12345'"</span><span>;<br /><br /></span><span><br />And yes, you are totally correct that you should not use direct SQL if there is an API for that feature.</span></p>
#182188
Sep 12, 2017 10:20
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
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
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
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
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
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
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
Vote:
 
<p>I was wrong.&nbsp;<span>You can use&nbsp;FindNodesDto or&nbsp;FindCatalogItemsTable</span></p>
#182292
Sep 14, 2017 16:35
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
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
Vote:
 

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

#182296
Sep 14, 2017 17:22
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
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
Vote:
 

"FindNodesDto or FindCatalogItemsTable"

You just can't find categories by FindItemsDto

#182499
Sep 21, 2017 13:19
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
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
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
Vote:
 
<p></p> <p>Okay, I added logging on the node search stored procedure&nbsp;ecf_CatalogNodeSearch stored procedure to log the params and the final&nbsp;@FullQuery. If I replicate what's being searched from calling this stored procedure directly I get a record in the&nbsp;CatalogNodeSearchResults table in which the CatalogNodeId is correct to my desired item. I can run the logged&nbsp;<span>@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. </span></p> <p><span>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</span></p>
#182505
Sep 21, 2017 16:02
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.