I am just finishing off a site which heavily uses the new extended content in merchello 1.12.0. Some of the pages on the site require lists of products based on a value in the extended content. At the moment I am fetching all of the products like this:
var merchelloHelper = new MerchelloHelper();
var products = merchelloHelper.Query.Product.Search(1, 1000).Items
.Select(x => (ProductDisplay)x)
.Where(x => x.Available && x.DetachedContents.Any(y => y.CanBeRendered));
var factory = new ProductContentFactory();
return products.Select(factory.BuildContent);
Then using the resultant IEnumerable to run linq queries against, this is, however, very very slow. The merchelloHelper.Query.Product.Search(1, 1000).Items alone takes close to 5 seconds. Is there a better way to approach this?
Yes and no! Most queries that i'll be doing won't return anywhere near that many but they do have an all products link which shows all of their (currently 800) products. Also, if the merchello helper query doesn't return all of the products, isn't it likely that my linq queries won't actually be able to filter properly since they won't have all of the products to apply the filters to?
I'd be interested in your list of filters you want/need as these are becoming frequent requests. I may start a thread on that =)
I think returning 1000 (800) products from a single query would wind up being pretty slow and you may consider creating a little POCO object and just Paging your database with your own queries.
Another thing to consider would be creating some dynamic collections like I did under the Sales menu in the back office (Unpaid invoices, Paid invoices ...)
The difference would be you would use your the ApplicationContext.Current.DatabaseContext.Database to execute your queries and T would be IProduct. The page of GUID queries are used by the MerchelloHelper via
var helper = new MerchelloHelper();
var result = helper.Query.Product.GetFromCollection([collectionKey], ...
where the collection key would be some GUID you set in the Attribute of your class.
It sounds like a pain, but they actually go really quickly and you get the benefit of being able to utilize the Examine indexes with database fall back that is already built into the MerchelloHelper without having to touch the Merchello Core code.
I've struggled to understand the way your collections work so i've worked on a poco that returns a list of variant keys for products i'm interested in which is, thanks to it's simplicity in looking directly at the db tables, lightning fast. What i'm doing after that is using the product services to get first the variants then the products from those variants, this is, unfortunately, rather slow. Here's my code:
var productVariantService = MerchelloContext.Current.Services.ProductVariantService;
var productService = MerchelloContext.Current.Services.ProductService;
var merchelloProducts = productVariantService.GetByKeys(allProducts.Select(x => x.productVariantKey)).Select(x => productService.GetByKey(x.ProductKey)).Select(x => AutoMapper.Mapper.Map<ProductDisplay>(x));
var factory = new ProductContentFactory();
return merchelloProducts.Select(factory.BuildContent);
Is there a better way to get an IEnumerable of IProductContent with a list of variant keys?
The product service will still query for all of the variants of a product to populate Product.ProductVariants.
I pretty sure it would be faster to go through the MerchelloHelper to do these queries since it will try to get a serialized product from the Examine index first.
Have you looked at MerchelloHelper.Query.Product methods?
Has there been any movement on the efficiency side of this? I'm on 2.0.1. I've got about 15k products, and am writing an angular "app" to return collections of products.
I'm using MerchelloHelper as you suggested, @Rusty, which works great and fast. But the problem is that I need to apply more filters on the search, not just ByCollection.. I need to filter certain manufacturers, detached values etc. Is this possible? Here's my code, and
//How can I filter manufacturers as well?
QueryResultDisplay allcollection = helper.Query.Product.GetFromCollection(thecollection.Key, model.page, 24, "", SortDirection.Ascending);
//Perform additional manufacturing filters here? I obviously can't query the Items property since it's a paged value, right?
var returnproducts = from x in allcollection.Items.Select(x=>(ProductDisplay)x)
let detachvals = x.DetachedContents.FirstOrDefault().DetachedDataValues
let prim_image = detachvals.Any(b => b.Key == "primaryImage") ? detachvals.FirstOrDefault(b => b.Key == "primaryImage").Value.Replace("\"", "") : ""
let image_path = detachvals.FirstOrDefault(d => d.Key == "colorProductImage").Value
let umbracomedia = !string.IsNullOrEmpty(prim_image) ? (Umbraco.TypedMedia(prim_image)) : null //Umbraco Media Picker IPublishedContent
let image_uploaded = (umbracomedia == null) ? "" : umbracomedia.Url
select new
{
Name = x.Name,
Sku = x.Sku,
MainLocal = image_path,
UploadedImage = image_uploaded, //umbraco media picker
DetachedVals = detachvals.ToDictionary(d => d.Key, d => d.Value) // Key/Value pairs are such a pain in Angular.. Dictionary serializes nicely to "key" : "value" as you'd expect
};
return Request.CreateResponse(HttpStatusCode.OK, new { collection = ret });
But now let's say I had the following filter array
["Manufacturer1", "Manufacturer2"]
How would I go about applying this above without disrupting the performance? I've tried everything.
The MerchelloHelper does have a a method for querying by Manufacturer which itself returns a paged set but it looks like your trying to filter on multiple things (collections and then manufacturers) - which is not part of the core.
The quickest way to do it is to create a query directly against the database that returns a Page{Guid} - the product keys based on the filter criteria which will require a few table joins. These sort of "PagedKey" queries are what the MerchelloHelper uses internally to retrieve information from the Merchello ProductIndex. In the event that a product does not exist in the index, the helper internally falls back to the database to query for the Product and reindexes the product so that on the next query, it will be found in the index.
As far as querying for detached content values, it's sort of the same problem as searching for content embedded in the Grid which I know Umbraco has been working on (or maybe even solved - I've not looked in a while but was planning on talking to Shannon at CG about it).
One method which has been done before is to create custom fields in an examine index by handling events on the indexer. This is relatively straight forward when you know what properties have been added to the detached content but rather difficult from a core perspective since there is no way of knowing what properties (of what type) to expect, whether they store their values as JSON, text or whatever and whether or not they should be indexed at all ... ex. image cropper.
This is a great discussion and would love to see it continue so that we can develop an implementation strategy.
I cloned the project earlier and have been sifting through the implementation. I think for now I can live without detached filtering, and perhaps just handle those on the frontend with an angular filter. Time is somewhat of a consideration.
Manufacturer will be pretty crucial though! Out of curiosity, I see that searchTerm paramater on one of your Query.Product.GetFromCollection overloads is used to build up some additional SQL query that checks partially on name/sku. I also notice that Manufacturer is in the current Index.
Do you see any ramifications of changing the
BuildProductSearchSql(string searchTerm) line 1685(currently)
sql.Where("sku LIKE @sku OR name LIKE @name", new { @sku = preparedTerms, @name = preparedTerms });
to
sql.Where("sku LIKE @sku OR name LIKE @name OR manufacturer LIKE @manufacturer", new { @sku = preparedTerms, @name = preparedTerms, @manufacturer = preparedTerms });
This is less than desirable, as I'd like to be able to upgrade Merchello, but in the short term it could solve a lot of problems?
Thanks again for the insight!! I'm absorbing morchello every day!
I'd have to look - but I think those queries may be the ones used in the back office product listing search/filter and my gut feeling is adding manufacturer to that mix may give really odd results - say for example someone uses the the manufacturer in the product name. That could make the back office search by name pretty much useless.
I think a better approach would be to come up with an override for the method so that it remains independent and implicitly called.
Best case scenario we would be able to come up with a syntax to be able to build queries on the fly ... I know Morten was dabbling with this a year or so ago, but I've not seen it pop up in the Umbraco Core - maybe due to the transition to NPoco in V8 ... I really have no idea.
Yea you're right.. Well I ended up going down the other road you suggested. But I think I'm missing something that I was hoping you might be able to point out.
Primarily, I am able to run the paged query PetaPoco, but not sure how I can cast this to some form of IProduct.. I can query 1 or more manufacturers inside of a collection, and it's lightening fast so far.
Here is where I'm at (sorry I gutted a lot of things), that maps to your existing ProductVariantDto
//THE USAGE
MyMerchelloHelper myhelper = new MyMerchelloHelper();
List<string> manufacturers = new List<string>();
manufacturers.Add("Manufacturer1");
manufacturers.Add("Manufacturer2");
List<ProductVariantDto> items = myhelper.GetByCollectionAndManufacturers(thecollection.Key, manufacturers, 1, 24, SortDirection.Ascending).Items;
//THE IMPLEMENTATION (obviously not very DRY, just proving this to myself here)
private string BuildCollectionAndManufacturerSQL(Guid collectionKey, List<string> manufacturers)
{
string sqlbuild = @"
SELECT *
FROM merchProductVariant
WHERE merchProductVariant.productKey IN
(SELECT DISTINCT([productKey])
FROM merchProduct2EntityCollection
WHERE merchProduct2EntityCollection.entityCollectionKey = '" + collectionKey + "')" +
"AND merchProductVariant.master=1 AND (";
for (int i = 0; i < manufacturers.Count; i++)
{
if (!string.IsNullOrEmpty(manufacturers[i]))
sqlbuild = sqlbuild + "merchProductVariant.manufacturer = '" + manufacturers[i] + "' " + ((i+1 < manufacturers.Count) ? "OR " : "");
}
sqlbuild = sqlbuild + ")";
return sqlbuild;
}
//I see your GetPageFromKeyPage but unsure how to use and I was getting internal errors, the scope was different?
public Page<ProductVariantDto> GetByCollectionAndManufacturers(Guid collectionKey, List<string> Manufacturers, long page, long itemsPerPage, SortDirection sortDirection = SortDirection.Descending)
{
var database = ApplicationContext.Current.DatabaseContext.Database;
return database.Page<ProductVariantDto>(page, itemsPerPage, BuildCollectionAndManufacturerSQL(collectionKey, Manufacturers));
}
I'm hoping this is pretty easy! I was "this" close to just joining the detached values 'values' into this table, but I know that wouldn't be right :)
What am I missing here? and thanks again for tossing a helping hand. Hopefully this helps someone down the road.
For Angular, I'm not sure there is any benefit for trying to return IProductContent. I would think you would want to be dealing with ProductDisplay (like we do in the back office).
The way it works in the MerchelloHelper is to create a QueryResultDisplay object (basically a Paged wrapper with Items being IEnumerable<ProductDisplay>)
Well I guess I don't care how the data comes in, it's transformed into JSON anyway. But I had originally tried that approach, but those methods are all protected.. Trying to do this outside of the Merchello core/web and not getting anywhere fast.. I had subclassed MerchelloHelper originally but that didn't help.
plus I receive Invalid cast from 'System.Int64' to 'System.Guid' when I try to grab Page<Guid> instead of Page<ProductVariantDto>
Querying products in 1.12.0
I am just finishing off a site which heavily uses the new extended content in merchello 1.12.0. Some of the pages on the site require lists of products based on a value in the extended content. At the moment I am fetching all of the products like this:
Then using the resultant IEnumerable to run linq queries against, this is, however, very very slow. The merchelloHelper.Query.Product.Search(1, 1000).Items alone takes close to 5 seconds. Is there a better way to approach this?
Thanks
Chris
Do you need 1000 products per request?
Hi Rusty,
Yes and no! Most queries that i'll be doing won't return anywhere near that many but they do have an all products link which shows all of their (currently 800) products. Also, if the merchello helper query doesn't return all of the products, isn't it likely that my linq queries won't actually be able to filter properly since they won't have all of the products to apply the filters to?
Thanks
Chris
Hey Chris,
I'd be interested in your list of filters you want/need as these are becoming frequent requests. I may start a thread on that =)
I think returning 1000 (800) products from a single query would wind up being pretty slow and you may consider creating a little POCO object and just Paging your database with your own queries.
Another thing to consider would be creating some dynamic collections like I did under the Sales menu in the back office (Unpaid invoices, Paid invoices ...)
Checkout :
https://github.com/Merchello/Merchello/blob/merchello-dev/src/Merchello.Core/EntityCollections/Providers/DynamicPaidInvoiceCollectionProvider.cs
The difference would be you would use your the ApplicationContext.Current.DatabaseContext.Database to execute your queries and T would be IProduct. The page of GUID queries are used by the MerchelloHelper via
where the collection key would be some GUID you set in the Attribute of your class.
It sounds like a pain, but they actually go really quickly and you get the benefit of being able to utilize the Examine indexes with database fall back that is already built into the MerchelloHelper without having to touch the Merchello Core code.
Hi Rusty,
I've struggled to understand the way your collections work so i've worked on a poco that returns a list of variant keys for products i'm interested in which is, thanks to it's simplicity in looking directly at the db tables, lightning fast. What i'm doing after that is using the product services to get first the variants then the products from those variants, this is, unfortunately, rather slow. Here's my code:
Is there a better way to get an IEnumerable of IProductContent with a list of variant keys?
Chris
Actually, i've eliminated one step by updating my poco to get the product keys rather than the variant keys so the slow line now is
Anything I can do about this?
Chris
What is
allProductsplease?Because I would like to know how I can get all products but not paged but just an IEnumerable list so that query will not be executed unless told.
Thank you
Kind Regards
The product service will still query for all of the variants of a product to populate Product.ProductVariants.
I pretty sure it would be faster to go through the MerchelloHelper to do these queries since it will try to get a serialized product from the Examine index first.
Have you looked at MerchelloHelper.Query.Product methods?
Hey Gents',
Has there been any movement on the efficiency side of this? I'm on 2.0.1. I've got about 15k products, and am writing an angular "app" to return collections of products.
I'm using MerchelloHelper as you suggested, @Rusty, which works great and fast. But the problem is that I need to apply more filters on the search, not just ByCollection.. I need to filter certain manufacturers, detached values etc. Is this possible? Here's my code, and
But now let's say I had the following filter array
["Manufacturer1", "Manufacturer2"]
How would I go about applying this above without disrupting the performance? I've tried everything.
Thanks if anyone knows the answer.
The MerchelloHelper does have a a method for querying by Manufacturer which itself returns a paged set but it looks like your trying to filter on multiple things (collections and then manufacturers) - which is not part of the core.
The quickest way to do it is to create a query directly against the database that returns a Page{Guid} - the product keys based on the filter criteria which will require a few table joins. These sort of "PagedKey" queries are what the MerchelloHelper uses internally to retrieve information from the Merchello ProductIndex. In the event that a product does not exist in the index, the helper internally falls back to the database to query for the Product and reindexes the product so that on the next query, it will be found in the index.
As far as querying for detached content values, it's sort of the same problem as searching for content embedded in the Grid which I know Umbraco has been working on (or maybe even solved - I've not looked in a while but was planning on talking to Shannon at CG about it).
One method which has been done before is to create custom fields in an examine index by handling events on the indexer. This is relatively straight forward when you know what properties have been added to the detached content but rather difficult from a core perspective since there is no way of knowing what properties (of what type) to expect, whether they store their values as JSON, text or whatever and whether or not they should be indexed at all ... ex. image cropper.
This is a great discussion and would love to see it continue so that we can develop an implementation strategy.
Thanks Rusty,
I actually like the SQL approach, especially since I'm short on time. I was looking at this post https://our.umbraco.org/projects/collaboration/merchello/merchello/73959-extra-data-on-invoice-and-query .. same concept with products? It looks fairly straight forward, just want to be sure I'm not going in the wrong direction.
I cloned the project earlier and have been sifting through the implementation. I think for now I can live without detached filtering, and perhaps just handle those on the frontend with an angular filter. Time is somewhat of a consideration.
Manufacturer will be pretty crucial though! Out of curiosity, I see that searchTerm paramater on one of your Query.Product.GetFromCollection overloads is used to build up some additional SQL query that checks partially on name/sku. I also notice that Manufacturer is in the current Index.
Do you see any ramifications of changing the BuildProductSearchSql(string searchTerm) line 1685(currently)
to
This is less than desirable, as I'd like to be able to upgrade Merchello, but in the short term it could solve a lot of problems?
Thanks again for the insight!! I'm absorbing morchello every day!
I'd have to look - but I think those queries may be the ones used in the back office product listing search/filter and my gut feeling is adding manufacturer to that mix may give really odd results - say for example someone uses the the manufacturer in the product name. That could make the back office search by name pretty much useless.
I think a better approach would be to come up with an override for the method so that it remains independent and implicitly called.
Best case scenario we would be able to come up with a syntax to be able to build queries on the fly ... I know Morten was dabbling with this a year or so ago, but I've not seen it pop up in the Umbraco Core - maybe due to the transition to NPoco in V8 ... I really have no idea.
Yea you're right.. Well I ended up going down the other road you suggested. But I think I'm missing something that I was hoping you might be able to point out.
Primarily, I am able to run the paged query PetaPoco, but not sure how I can cast this to some form of IProduct.. I can query 1 or more manufacturers inside of a collection, and it's lightening fast so far.
Here is where I'm at (sorry I gutted a lot of things), that maps to your existing ProductVariantDto
I'm hoping this is pretty easy! I was "this" close to just joining the detached values 'values' into this table, but I know that wouldn't be right :)
What am I missing here? and thanks again for tossing a helping hand. Hopefully this helps someone down the road.
For Angular, I'm not sure there is any benefit for trying to return IProductContent. I would think you would want to be dealing with ProductDisplay (like we do in the back office).
The way it works in the MerchelloHelper is to create a QueryResultDisplay object (basically a Paged wrapper with Items being
IEnumerable<ProductDisplay>)https://github.com/Merchello/Merchello/blob/merchello-dev/src/Merchello.Web/Search/CachedQueryBase.cs#L207
Just confirming you are querying for a
Page<Guid>and not aPage<ProductVariantDto>using the internal Merchello ProductVariantDto .Well I guess I don't care how the data comes in, it's transformed into JSON anyway. But I had originally tried that approach, but those methods are all protected.. Trying to do this outside of the Merchello core/web and not getting anywhere fast.. I had subclassed MerchelloHelper originally but that didn't help.
plus I receive Invalid cast from 'System.Int64' to 'System.Guid' when I try to grab
Page<Guid>instead ofPage<ProductVariantDto>Are there any examples of this anywhere? Extending/Custom Queries etc?
How can use GetFromCollection from ProductService?
var contentService = ApplicationContext.Services.ContentService;
var clientId = member.GetValue("client");
var client = contentService.GetById(int.Parse(clientId.ToString()));
var categoryId = client.GetValue("category");
var productService = MerchelloContext.Current.Services.ProductService;
var products = productService.GetFromCollection(new Guid(categoryId.ToString()), 1, 10);
var products count is 0 instead of 2 products in data.
@Nguyen that looks correct. Are you sure the GUID is correct?
How are you storing the collection key on the client page? What data type?
As the above code, how can I get the total items then of the query since you are returning an IEnumerable?
Thank you
Kind Regards
is working on a reply...