Recently I stumped upon this question:
which is very interesting to me. I can see this is a real scenario – and even quite common. When a price become obsolete, you want your contents to be reindexed so the next time you query, the search result will be returned correctly. But how?
There is no event to handle such changes. As I commented on the thread, it would be quite technical challenging to do so. The official, supported method of using APIs (loading all entries, as showed here ), would be too slow:
using EPiServer.Commerce.Catalog.ContentTypes;
using EPiServer.Core;
using Mediachase.Commerce;
using Mediachase.Commerce.Pricing;
namespace EPiServer.Reference.Commerce.Site.Infrastructure
{
public class PriceUpdater
{
private readonly IContentLoader _contentLoader;
private readonly IPriceService _priceService;
public PriceUpdater(IContentLoader contentLoader,
IPriceService priceService)
{
_contentLoader = contentLoader;
_priceService = priceService;
}
public void CheckPrices(ContentReference contentLink)
{
var children = _contentLoader.GetChildren<CatalogContentBase>(contentLink);
foreach (var child in children)
{
if (child is ProductContent)
{
CheckProductPrices(child.ContentLink);
}
else if (child is NodeContent || child is CatalogContent)
{
CheckPrices(child.ContentLink);
}
}
}
private void UpdateProductPrices(ContentReference contentLink)
{
var prices = _priceService.List(contentLink);
if (prices.Any(p=>p.ValidUntil != null && p.ValidUntil < DateTime.UtcNow))
{
//Reindex content
}
}
}
}You might ask why the “update prices” sample was OK/fast enough, while this is not. Because running that code can be from 30 minutes, to hours, depending on your catalog size. However, you don’t update prices globally that often – usually once in a while (if not only once!). But to make the indexing effective, you’ll have to run it every 5 minutes or so. Otherwise, you are risking having obsolete prices for too long!
Can we have a faster solution? Yes. When the API is not fast enough (because we are asking it to do something it is not built for) – the next thing we should look at, is the database and direct query.
As always, having direct database queries comes at a cost. Direct data manipulation is very risky and should be avoided. Direct data queries are much safer (Than manipulation), but your code might still break if Episerver releases a new version and change the schema (the database schema is not part of APIs, and there is no backward compatibility guaranteed). You have to consider if the benefits of doing so outweigh the drawbacks.
You still want to continue? OK. Then let’s go. To check for prices, we have two tables to look at: PriceGroup and PriceValue. We need the codes of the entries, which are in PriceGroup, but we also have to check for ValidUntil values. No problem, a simple join would solve it for us:
declare @until datetime declare @from datetime set @until = GETDATE() set @from = DATEADD(MINUTE, -5, @until) select CatalogEntryCode from PriceGroup pg inner join PriceValue pv on pg.PriceGroupId = pv.PriceGroupId where pv.ValidUntil is not null and (pv.ValidUntil between @from and @until)
Here we are getting CatalogEntryCode(s), which have prices obsoleted in last 5 minutes. Depends on how big your catalog is – you might change that value, 10 minutes, 15 minutes, etc.
This query should be very fast – but it only solves a half of problem. You will need to write some code to call that query (better turn it to a stored procedure), and then resolve the ContentReference from the code (Remember our little friend ReferenceConverter ?), and then index the content.
You can optimize the query further to include a distinct – because an entry might have multiple obsolete prices (in multiple markets/currencies) – and we only have to index it once.
I’ll leave that part to you!
