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.



#185216 Edited, Nov 12, 2017 12:59
  • Member since: 2011

    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. 

    #185229 Nov 13, 2017 10:47
  • Thanks Quan. Will you be able to confirm which version of Commerce will have already fixed? 

    #185232 Nov 13, 2017 11:28
  • Member since: 2011

    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. 

    #185233 Nov 13, 2017 11:33