Episerve Commerce 10 Catalog-product-search

Vote:
 

Hi ,

I am new to Episerver Commerce. I am migrating from the commerce 7.5 to commerce 10.

I am facing an issue in catalog product search.

When i am using the existing code 

var parameters = new CatalogSearchParameters();
parameters.SqlMetaWhereClause = "Meta.BrandId = '" + brandId + "' AND " + "Meta.SubBrandId = '" + subBrandId + "'";

var options = new CatalogSearchOptions();
options.Classes.Add("ProductEntry");
options.RecordsToRetrieve = 10;

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

Then i am getting exception saying that Invalid Colum name BrandId. Invalid Column name SubBrandId.

Then i gone through the documentation https://world.episerver.com/documentation/developer-guides/commerce/catalogs/Catalog-product-search/

and i changed my SqlMetaWhereClause condition like this

parameters.SqlMetaWhereClause = " META.MetaFieldName = 'BrandId' AND META.LongString ='" + brandId + "' AND " + "META.MetaFieldName = 'SubBrandId' AND META.LongString ='" + subBrandId + "'";

Now everytime i am getting result.ToResults value as -1.

Can someone please suggest me what i am doing wrong here or any help in knowing the table being used to search.

Thanks in advance,

Brijesh

#182338
Edited, Sep 15, 2017 14:01
Vote:
 

This is a good question - I think this is the limitation of Commerce 9. There is no way to specify that (you can't query a row which have MetaFieldName is both BrandId and SubBrandId.

One work around is to have two searches, one for META.MetaFieldName = 'BrandId' AND META.LongString ='" + brandId and one for META.MetaFieldName = 'SubBrandId' AND META.LongString ='" + subBrandId + "', then you can Intersect the result. Not optimal, yes, but it should work.

I'll see if we can fix this - but perhaps the better overall solution is to switch to indexed search (Find should be really really good at this)

#182341
Sep 15, 2017 14:27
Vote:
 

Hi Quan,

Thanks for the response.

Even when i am trying for the single metafield name then also i am getting no results.

parameters.SqlMetaWhereClause = " META.MetaFieldName = 'BrandId' AND META.LongString ='" + brandId + "'";

Can you please tell me which table it is using or how it is being retrieved so that i can go and verify in my db.

FYI i am having record in the my metaclass custom table.

BrandId SubBrandId
99            ZZZ
99            ZZT
99            ZZX
99            ZZY
99            ZZS

Thanks,

Brijesh

#182343
Sep 15, 2017 14:46
Vote:
 

It'll be looking into CatalogContentProperty table for the Meta part.

#182344
Sep 15, 2017 14:48
Vote:
 

Hi Quan,

Thank for the response this table name realy helped me to resolve the problem.

I am able to get he solution with your help and here is my code.

var parameters = new CatalogSearchParameters();
var options = new CatalogSearchOptions();
parameters.SqlMetaWhereClause = " MetaFieldName = 'BrandId' AND LongString ='" + brandId + "'";

//options.Classes.Add("ProductEntry");
options.RecordsToRetrieve = 10;
var result = CatalogContext.Current.FindItems(parameters, options, new CatalogEntryResponseGroup());

var brandIdResults = result.Entry;

parameters.SqlMetaWhereClause = " MetaFieldName = 'SubBrandId' AND LongString ='" + subBrandId + "'";
result = CatalogContext.Current.FindItems(parameters, options, new CatalogEntryResponseGroup());
var subBrandIdResults = result.Entry;

var totalResults = MatchingRecordExist(brandIdResults, subBrandIdResults);

************************************************************************

private static int MatchingRecordExist(Entry[] brandIdEntries, Entry[] subBrandEntries)
{
if (brandIdEntries!=null && brandIdEntries.Length > 0 && subBrandEntries!=null && subBrandEntries.Length > 0)
{
var result= brandIdEntries.Select(a => a.CatalogEntryId).Intersect(subBrandEntries.Select(b => b.CatalogEntryId));
return result.Count();
}
return -1;
}

Previously i was not able to get result because of the 

options.Classes.Add("ProductEntry");

When i commented this i am able to get the result.

Thanks,

Brijesh

#182356
Edited, Sep 15, 2017 16:09
Vote:
 
#182357
Sep 15, 2017 16:12
Vote:
 

Wonderfull! I was so glad to find this discussion. Helped me out guys.

#185461
Nov 21, 2017 8:51
This topic was created over six months ago and has been resolved. If you have a similar question, please create a new topic and refer to this one.
* 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.