Try our conversational search powered by Generative AI!

API Search not returning catalogs when Localization is enabled on Search by MetaField



We have a commerce solution which caters for multiple language, we have a request from client where we have to write our custom cms search provider.

Commerce version is 8.5 and Episerver cms is 7.5

On our solution there are metafields and these metafields are marked for multilanguage to be True. And these metafields are mapped against few of the metalcasses and not all.

What I found from the profiler the stored procedure ecf_CatalogEntrySearch is not utilizing _Localization. There is a bit of sql which is commented as shown below

set @Query_tmp = 'select META.ObjectId as ''Key'', 100 as ''Rank'' from ' + @TableName_tmp + ' META' -- INNER JOIN ' + @TableName_tmp + '_Localization LOC ON META.ObjectId = LOC.Id'

I have to modify the store procedure to include Localization

set @Query_tmp = 'select META.ObjectId as ''Key'', 100 as ''Rank'' from ' + @TableName_tmp + ' META INNER JOIN ' + @TableName_tmp + '_Localization LOC ON META.ObjectId = LOC.ObjectId'

After modifying the above sql it returns the result. Not sure if the commenting of the _Locationzation bit is intended. Obviously the modified sql missing the check for table exist, which is covered on my procedure. 

My C# code is as follows 

CatalogSearchParameters parms = new CatalogSearchParameters();
parms.OrderByClause = "CatalogEntry.Name";
//parms.Language = "en-gb";

CatalogSearchOptions options = new CatalogSearchOptions
	CacheResults = cacheResults,
        RecordsToRetrieve = pageSize,
        CacheTimeout = cacheTimeout

options.Namespace = "Mediachase.Commerce.Catalog";

string sqlMetaWhereClause = string.Format(@"Loc.OurMetaField like '%{0}%'", keyword);
parms.SqlMetaWhereClause = sqlMetaWhereClause;
return CatalogContext.Current.FindItems(parms, options, new CatalogEntryResponseGroup(CatalogEntryResponseGroup.ResponseGroup.CatalogEntryFull));

In the above code the multi language MetaField (which is OurMetaField) is found in AProductPage_Localization, BProductPage_Localization, CProductPage_Localization.

Can some one confirm if they faced the similar problem, or any solution without modifying the stored procedure.



Edited, Nov 12, 2017 12:59

I can confirm this happens, and I don't know why, for sure. The commented out code seems to be unintentional, and the history is lost at this point.

There is very little we can do now - 8.15 is an old version and we can't fix or test it. I'd say if you find the change works for you (and it look reasonable) - then test it carefully and use it at your own risk. 

Our recommendation would still be upgrading to a later version, and to use the indexed search. 

Nov 13, 2017 10:47

Thanks Quan. Will you be able to confirm which version of Commerce will have already fixed? 

Nov 13, 2017 11:28

Unfortunately there is no version when this is "fixed". In Commerce 9 the way to query for metafields were changed (and with some limitations), so this problem might disappear. 

Nov 13, 2017 11:33
* 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.