London Dev Meetup Rescheduled! Due to unavoidable reasons, the event has been moved to 21st May. Speakers remain the same—any changes will be communicated. Seats are limited—register here to secure your spot!

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.