Large number of custom product fields and filters

-- HikaShop version -- : 4.4.3
-- Joomla version -- : 3.10.1
-- PHP version -- : 7.3

See following post: problem solved -- so, please ignore!

Hi,

I'm dealing with a large number of products across something like 200 categories. For the purpose of being able to conveniently filter products via a filter module, products have up to 10 custom fields, which are different for each category. Sure, thanks to restricting each set of say 10 custom fields to their respective category, it's fine on the product side. But I'll end up with the need for up to 200 x 10 = 2000 custom fields... and the same amount of filters.

With so many columns in the products table, I'm fearing performance issues... what do you say?

As it stands, each field uses its own column in the products table, even though only relatively few fields per each column will be populated (for the products in the category which the custom field is restricted to).

To avoid those up to 2000 extra columns in the products table, I've been working on a workaround. The good news is that each product is guaranteed in only one category, and all custom fields are of the same type (single dropdown in form, checkbox in filter).
Some of it went smooth, but at some point I'm stuck... and perhaps you can shed some helpful light there. Or have a better idea for a better solution/workaround?

This is what I've done:
1. Created 10 "generic" custom product fields, not restricted to any category.
2. Have a separate new DB table with very few columns (ID of the "generic" field, category ID, "realname", field values in the double-colon format) to hold the basic data of all the required "real" custom fields, one per row.
3. Per view override of the product backend edit form, after a DB query and depending on category I'm able to replace names and value options of the generic fields with the ones fetched from the separate table and store the correct value in the correct "generic" column in the products table.
(4. Haven't done this yet, but am pretty sure this won't be a problem in the frontend product page as well - if needed at all.)
5. Created 10 filters for the "generic" custom fields, again not restricted to any category, dynamic display = yes.
So, that's all fine.

Where I'm stuck is the filters display in the frontend module. Been messing with each
- /administrator/components/com_hikashop/classes/filter.php
- /components/com_hikashop/views/product/view.html.php
- override of /components/com_hikashop/views/product/tmpl/filter.php
Replacing the generic filter names with the right ones is easy, but I can't get the checkboxes with their right labels in the filters... I seem to be unable to get my head around the logic with which you're putting fields, filters and stored values together.

I'd greatly appreciate if you could give me some hints on how that's done, so I could continue this route.
Or perhaps suggest a different, better solution?
Or, against all odds, confirm that worst case 2000 more columns in the products table will not cause any issues, performance or otherwise?

Thanks a ton in advance. If you need further details on my "workaround in progress", I'll be happy to provide.

Hi again,

Couldn't let go... kept digging... and now found it:
function getFields in /administrator/components/com_hikashop/classes/filter.php

Apologies for the previous long post. Mystery solved, case closed!

Many thanks, Nicolas,

For your reply and presenting the idea of creating a new field type.

As stated, I've now done it "my way", it's working as expected, and keeping track of the mods in the core file is not a problem.

I know there have been requests for this only once in a blue moon, but having a "query field" to pull values and labels per MySQL query from another table would be sure nice. Well, maybe one of these years...

Anyway, thanks again!