Commerce 11: catalog products search with 3 meta fields



I just migrated our commerce site from version 8.17.0 to 11.2.5 and i'm having trouble with our products searches.

We use the function CatalogContext.Current.FindItems to find items in our catalog and in the parameters param we set the SqlMetaWhereClause to :

Meta.ReadyForWeb='False' AND (Meta.OurWebStatus = 'X' OR Meta.OurStatus = 'X')

It worked in commerce 8 but with commerce 11, I get the Invalid column name 'ReadyForWeb', Invalid column name 'OurWebStatus' and Invalid column name 'OurStatus'.

It looks like it changed in Commerce 9, and in this post ( it looks like the solution is to do 2 searches and intersect the results.

Is it still the case?

In my case, do I have to do 3 searches and then intersect/combine the results or is there any more efficient way to do it?

Oct 31, 2017 21:00

Yes that would still be the way to do it. We looked into it, but it was too complicated fix to be done.

Performance wise you might want to switch to index based search provider.

Oct 31, 2017 22:25

When I try to do my catalog query like this, I get no results:

CatalogSearchParameters pars = new CatalogSearchParameters();
CatalogSearchOptions options = new CatalogSearchOptions
CacheResults = false,
RecordsToRetrieve = 10


pars.Language = "fr";

pars.SqlMetaWhereClause = "(META.MetaFieldName = 'OurWebStatus' AND META.Number <> 578)";

pars.SqlWhereClause = "(MetaClassId=28 OR MetaClassId=29) AND IsActive = 'true'";


Entries tmpEntries = CatalogContext.Current.FindItems(pars, options, new CatalogEntryResponseGroup());

When I remove my MetaWhereClause, I get results. So it's seems the problem is in the MetaWhereClause/Classes.

Here's the request I saw in my SQL Server Profiler when I try to call the FindItems function above:

declare @p17 int
set @p17=-1
exec ecf_CatalogEntrySearch @SearchSetId='87FA5F2F-68A4-4F03-ABE7-8DF5339A83DD',@Language=N'fr',@Catalogs=N'OurShop',@CatalogNodes=N'',@SQLClause=N'(MetaClassId=28 OR MetaClassId=29) AND IsActive = ''true''',@MetaSQLClause=N'
FROM CatalogContentProperty META
INNER JOIN MetaClass MC ON MC.MetaClassId = META.MetaClassId
LEFT JOIN MetaKey MK ON META.Number = MK.MetaKey
LEFT JOIN MetaMultiValueDictionary mmvd ON mk.MetaKey = mmvd.MetaKey
LEFT JOIN MetaDictionary METADIC ON mmvd.MetaDictionaryId = METADIC.MetaDictionaryId AND mk.MetaFieldId = METADIC.MetaFieldId
WHERE META.ObjectTypeId = 0
AND (META.MetaFieldName = ''OurWebStatus'' AND META.Number <> 578) AND MC.Name IN (SELECT Item FROM ecf_splitlist('''''' + OurVariation,OurPackage + '''''')) ) FilteredEntries
ON FilteredEntries.ObjectId = [CatalogEntry].CatalogEntryId ',@KeywordPhrase=N'',@OrderBy=N'',@Classes=N'OurVariation,OurPackage',@StartingRec=0,@NumRecords=10,@JoinType=N'',@SourceTableName=N'',@TargetQuery=N'',@SourceJoinKey=N'',@TargetJoinKey=N'',@RecordCount=@p17 output,@ReturnTotalCount=1
select @p17

By the way, I renamed some of our fields with the 'our' prefix for this example instead of the company name.

Can you spot what's wrong with the query that I don't have any results?

When I do this query, I have 7786 rows:

FROM [eCommerce].[dbo].[CatalogContentProperty] as META
INNER JOIN MetaClass MC ON MC.MetaClassId = META.MetaClassId
WHERE(MetaFieldName = 'OurWebStatus' AND Number <> 578)
AND(MC.[Name] = 'OurVariation' OR MC.[Name] = 'OurPackage')

Nov 01, 2017 19:48

Update: Seems like a bug when we use options.Classes.Add to use with the pars.SqlMetaWhereClause.

When I see the generated request from ecf_Search_CatalogEntry in Sql Server Profiler, I get:

with OrderedResults as (SELECT count([CatalogEntry].CatalogEntryId) OVER() TotalRecords, [CatalogEntry].CatalogEntryId, ROW_NUMBER() OVER(ORDER BY CatalogEntry.CatalogEntryId) RowNumber FROM [CatalogEntry] CatalogEntry 
FROM CatalogContentProperty META 
INNER JOIN MetaClass MC ON MC.MetaClassId = META.MetaClassId 
LEFT JOIN MetaKey MK ON META.Number = MK.MetaKey 
LEFT JOIN MetaMultiValueDictionary mmvd ON mk.MetaKey = mmvd.MetaKey 
LEFT JOIN MetaDictionary METADIC ON mmvd.MetaDictionaryId = METADIC.MetaDictionaryId AND mk.MetaFieldId = METADIC.MetaFieldId 
WHERE META.ObjectTypeId = 0 
AND (META.MetaFieldName = 'OurWebStatus' AND META.Number <> 578) AND MC.Name IN (SELECT Item FROM ecf_splitlist(''' + OurVariation,OurPackage + ''')) ) FilteredEntries 
ON FilteredEntries.ObjectId = [CatalogEntry].CatalogEntryId INNER JOIN @Catalogs_temp catalogs ON CatalogEntry.CatalogId = catalogs.CatalogId INNER JOIN CatalogLanguage l ON l.CatalogId = CatalogEntry.CatalogId AND l.LanguageCode = N'fr' WHERE 1=1 AND ((MetaClassId=28 OR MetaClassId=29) AND IsActive = 1)) INSERT INTO @Page_temp (TotalRecords, ObjectId, SortOrder) SELECT top(10) TotalRecords, CatalogEntryId, RowNumber FROM OrderedResults WHERE RowNumber > 0;

The problem is : ecf_splitlist(''' + OurVariation,OurPackage + ''') 
If I replace it with ecf_splitlist('OurVariation,OurPackage'), it works.

So I think there's a bug when the call to ecf_Search_CatalogEntry is made.

The guy in this post( said he fixed his problem by removing his classes.add line.

If I try this, I get these errors when I call CatalogContext.Current.FindItems : 
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

Exception in ecf_Search_CatalogEntry: Column 'Code' is constrained to be unique. 
Value '303205' is already present.Column 'Code' is constrained to be unique. 

Value '303205' is already present.Column 'Code' is constrained to be unique. 


And many more errors like that.

Is there a workaround or how can I do this catalog search with our meta fields? 
We already have a solr index but we use it after this cleanup is done and we can't use it to clean our catalog items.

Nov 02, 2017 15:43

Thanks, we will look into this. I don't know a workaround that work, but technically you can leave empty classes, and add this to SqlMetaWhereClause to have (META.MetaClassId = 12 OR META.MetaClassId = 13) AND ...

I haven't tried it, but it might work :) 

Nov 02, 2017 23:02

I finally got it.

I had to include a META.Language = 'en' in the SqlMetaWhereClause or else all my results were doubled and I had an exception.

So my SqlMetaWhereClause was : (META.LanguageName = 'en' AND META.MetaFieldName = 'OurWebStatus' AND META.Number <> 578)

And I removed all classes.

It's not great performance wise coz now I have to do 2 queries that return a lot more results than when they were combined and even if the sql query is quick, when the CatalogContext.Current.FindItems is called and returns 7500 items, it takes a couple of minutes to run even if the SQL was quick.

But at least I have a workaround.

Nov 03, 2017 20:21
* 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.