Combining index queries

Imar Spaanjaars

Posted on 29/02/2024 20:08:04

Hi there,.

I have an existing solution serving 20+ shops. Products are imported from different ERPs (one per shop). As part of the import, we update a date field per shop. So a product has fields like:

Update shop 1: 2024-2-28
Update shop 2: 2023-2-28
Update shop 3: null
...
We then have logic that only shows products on a given site if the product has been updated for that site in the past 14 days, it's included; otherwise it's filtered from the index.

Given the example above, the product would not be shown since the date is from last year.

This works fine and we get the right products on the right shop.

However, this also means we need to have a query file for each shop.as the column to filter on is different. That's becoming a pain now as every change to a query needs to be repeated 20+ times.

Is there a way to handle this dynamically with a Macro or so? I think inside the Macro I can find the given site / shop but not sure how to map that info to a field in the index. Also, each shop has around 70K products so performance could be a concern.

Does anyone have any ideas how I could bring back the number of repo queries from 20+ to 1 and handle this requirement in a single query file?

Thanks in advance for any insights!

Imar

Replies

Nuno Aguiar

Posted on 01/03/2024 09:57:04

HI Imar,

I have an idea, although I'm not sure how it would impact performance.

You can have a group of expressions using OR
(similar to what happens with Free-text search)
In it you create 1 expression for each shop
You then create a Macro that would get the Shop context and return null if it's not the accurate shop

That allows you to keep 1 single Query to manage. You may need to create some custom cached objects to improve the performance of the macro.

Best Regards,

Nuno Aguiar

Imar Spaanjaars

Posted on 01/03/2024 11:55:15

Hi Nuno,

I'm not sure how that would work. Would I have N expressions filtering on the date like the one in the image above, one for each shop? But then how would that Macro work? Can you elaborate a bit? It's important that only the specific shop's date is validated. Having a recent date for another shop doesn't matter and shouldn't include the product.

Thanks!

Imar

Nuno Aguiar

Posted on 01/03/2024 12:34:21

Hi Imar,

(as I wrote my response below, I realize what you need is a Code Provider)

Yes, create 1 expression for each field (specific shop's date field
Each expression always points to the same Code Provider
- You'd take 1 paremeter which could be the ShopId for that instance
The Code Provider then would have to be check what's the current's request context (worst case to get the pageId > get the area > get the shop for the are) and match it against the parameter
- If the shopids don't match, you can return null

Here's the code for DW's DateTime Provider for inspiration (the screenshot above)

using System;
using System.Collections;
using Dynamicweb.Extensibility.AddIns;
using Dynamicweb.Extensibility.Editors;
using Dynamicweb.Extensibility.Providers;
using Dynamicweb.SystemTools;
 
namespace Dynamicweb.Extensibility.CodeProviders
{
    [AddInName("Dynamicweb.ICodeProvider")]
    [AddInLabel("DateTime Provider")]
    [AddInActive(true)]
    [AddInGroup("System.DateTime")]
    public class DateTimeCodeProvider : CodeProviderBase, ICodeProvider, IDropDownOptions
    {
        [AddInParameter("Number")]
        [AddInParameterEditor(typeof(IntegerNumberParameterEditor), "inputClass=inputControl")]
        [AddInParameterGroup("Source")]
        public int Number { get; set; }
        [AddInParameter("Interval")]
        [AddInParameterEditor(typeof(DropDownParameterEditor), "inputClass=inputControl")]
        [AddInParameterGroup("Source")]
        public string Interval { get; set; }
 
        public override string BuildCodeString()
        {
            // Basically not used anymore because Execute is now being overriden
            return string.Format("@Code(DateTime.Now.Add{0}({1}))", Interval, Number);
        }
 
        public override string BuildDisplayValue()
        {
            return string.Format("Today {0} {1} {2}", Number >= 0 ? "+" : "-", Math.Abs(Number), Interval);
        }
 
        public override object Execute()
        {
            var switchExpr = Interval;
            switch (switchExpr)
            {
                case "Minutes":
                    {
                        return DateTime.Now.AddMinutes(Number);
                    }
 
                case "Hours":
                    {
                        return DateTime.Now.AddHours(Number);
                    }
 
                case "Days":
                    {
                        return DateTime.Now.AddDays(Number);
                    }
 
                case "Months":
                    {
                        return DateTime.Now.AddMonths(Number);
                    }
 
                case "Years":
                    {
                        return DateTime.Now.AddYears(Number);
                    }
            }
 
            return DateTime.Now;
        }
 
        public Hashtable GetOptions(string dropdownName)
        {
            // Get Hashtable
            var options = new Hashtable();
            options.Add("Minutes", Translator.Translate("Minutes"));
            options.Add("Hours", Translator.Translate("Hours"));
            options.Add("Days", Translator.Translate("Days"));
            options.Add("Months", Translator.Translate("Months"));
            options.Add("Years", Translator.Translate("Years"));
 
            // Return the hashtable
            return options;
        }
    }
}

I know we've done something similar in the past (returning null on Macros and/or Code Providers), so I see that as a possibility, but again, not sure how it would perform.

Best Regards,

Nuno Aguiar

Nicolai Pedersen

Posted on 01/03/2024 14:49:24

Why not add the updated time stamp to the same field? It might come from 20 different fields, but simply create a small add in that would use the same field in the index.

Imar Spaanjaars

Posted on 01/03/2024 15:12:45

>> Why not add the updated time stamp to the same field?

Because we have products that can be shared across shops. So it could be updated in one ERP yesterday and therefore shown on Shop1, but updated a month ago in another ERP and therefore not be shown on Shop2.

Or do I misunderstand what you are suggesting?

Imar

Nicolai Pedersen

Posted on 03/03/2024 12:22:58

No - that was what I was suggesting and asking. Just trying to see if it was easier to remove complexity instead of adding more... :-)

Imar Spaanjaars

Posted on 05/03/2024 22:13:25

What about keeping it simpler and just setting up a date check + a shop ID check for each site? I.e.

--
|Active = true
--
AND
--
|  |--
|  | Updated01 > 14 days ago
|  | Shop ID = 1
|  | Shop ID = Context.ShopId
|  |--
|  OR
|  |--
|  | Updated02 > 14 days ago
|  | Shop ID = 2
|  | Shop ID = Context.ShopId
|  |--
   OR
|  |-- 
|  | Other dates / shops here
--
Other criteria here

Seems simple enough, and would require an update only when new shops are added.

Any potential performance issues with this?

Imar

Nicolai Pedersen

Posted on 06/03/2024 11:11:03

Performance should be fine on these kind of expressions.

Nuno Aguiar

Posted on 06/03/2024 11:44:03

Hi Imar,

That is what I meant; you made a nice visual for it. But I believe you can't set Expressions for the ShopID validation. I believe you'll have to use a CodeProvider for that. Here's a simple visual of how it would look in the end

You can start with a copy of the DateTime Provider (sent above) and then add the Shop parameter and the logic you described to validate the context.

Best Regards,

Nuno Aguiar

Imar Spaanjaars

Posted on 06/03/2024 12:43:18

I see. I was thinking about this though:

This way, the shop ID must be, say, H01 and the context shop ID must also be H01. If neither is true, we're on a different shop and the whole group will not match. But if we are, then the date is also checked. Inside each group I can add other shop-specific filters, while the generic ones go at the level of Active expression.

Does that make sense? Or do you still see a use case for a code provider?

Nuno Aguiar

Posted on 06/03/2024 12:50:30

This post has been marked as an answer

Hi Imar,

That works too from what I can tell. I hadn't really remembered about the Shop IDs field.

In this case I would go with standard. The only benefit to the Code Provider would be to have a simpler/shorter UI and less error prone to configure, but I would certainly use your way first.

BR,

Nuno Aguiar

Votes for this answer: 1

You must be logged in to post in the forum

Developer forum

Combining index queries

Replies