Large number of custom product fields and filters

  • Posts: 2076
  • Thank you received: 703
  • Hikashop Business
2 weeks 1 hour ago #335518

-- 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.

Last edit: 1 week 6 days ago by lousyfool. Reason: Solved!

Please Log in or Create an account to join the conversation.

  • Posts: 2076
  • Thank you received: 703
  • Hikashop Business
1 week 6 days ago #335520

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!

Please Log in or Create an account to join the conversation.

  • Posts: 73730
  • Thank you received: 11306
  • MODERATOR
1 week 6 days ago #335531

Hi,

Regarding performances, 2000 columns is a lot but it depends on the number of products too. If you only have a few thousands products it should be fine.
It's all a matter of memory usage, which is defined by the number of columns times the number of rows (the products).

I would personally do it differently:
- to fill up dynamically the values of the custom field, I would actually create a new type of custom field with the Fields API:
www.hikashop.com/support/documentation/6...entation.html#fields
That way, I would extend from the default hikashopFieldSingledropdown class and just override the display function to set the data un $field->field_values before calling parent::display() and letting HikaShop do the rest.
- to fill up the filter, I would override the product / filter.php view file. In there, I would get the HTML of the filter, which should be an empty SELECT tag and fill it up with OPTION tags using str_replace. It's less nice than what can be done for the field with the Fields API, but it's way better than directly modifying the administrator/components/com_hikashop/classes/filter.php. Because if you do, you'll loose your changes after each update and have to manually add them back each time.

The following user(s) said Thank You: lousyfool

Please Log in or Create an account to join the conversation.

  • Posts: 2076
  • Thank you received: 703
  • Hikashop Business
1 week 6 days ago #335548

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!

Please Log in or Create an account to join the conversation.

  • Posts: 73730
  • Thank you received: 11306
  • MODERATOR
1 week 6 days ago #335558

Hi,

Thanks for your feedback. we'll look into it. That looks like a good idea and not too hard to add.

The following user(s) said Thank You: lousyfool

Please Log in or Create an account to join the conversation.

Time to create page: 0.054 seconds
Powered by Kunena Forum