Multivariants values not indexed

[stAn99]

Hello friends,
i was sent to this forum to open a discussion about the problem of selecting, searching and filtering products per their custom field values, especially if multi variants are used.

the current behavior is (vm3.0.18.5):
- when creating a multivariant the vaues of the attributes such as Color (Red, Green) and Size (S, XL) are inputted manually
- they get stored within the params of virtuemart_product_customfields.params in a json format
- the multi variant allows to associate a single string customfield to a product which value is stored in virtuemart_product_customfields.custom_value

for purpose of creating queries (and logic):
- select products in a categories
- where the product's attribut's Color is RED

there is no indexed field of either value (RED) or the ID (of the RED) since it's got a different ID per each child product (if custom field string is used) and is only part of the json-encoded params value of the customfield at the child products.

My suggestion is to use:
- new customfield type of "Collection" that would be a parent of the below (and it's title is "Color" )  (virtuemart_custom_id = 1)
- new customfield type of "Collection" which is a child of Color and it's title is "Red"                        (virtuemart_custom_id = 2)
- new customfield type of "Collection" which is a child of Color and it's title is "Green"                        (virtuemart_custom_id = 7)
- new customfield type of "Collection" that would be a parent of the below (and it's title is "Size" )  (virtuemart_custom_id = 3)
- new customfield type of "Collection" which is a child of Color and it's title is "XL"   (virtuemart_custom_id = 4)
- new customfield type of "Collection" which is a child of Color and it's title is "S"   (virtuemart_custom_id = 5)

- make sure that at least one column in virtuemart_customs is indexed, if we do not want to create new columns we can index the TITLE itself (would require a length limit of 160varchar for innodb and cca 200 varchar for myisam)

- while creating the multivariant you can choose "Collection: Color" as the Feed for the Attributes
- each child would be associated automatically to virtuemart.customs with all the rows:
Product Red+XL would get per above example virtuemart_custom_id with value identical to the Title of the customfield (for backward compatiblity): virtuemart_custom_id=2, virtuemart_custom_id=4,
Product Green+XL would get per above example virtuemart_custom_id with value identical to the Title of the customfield (for backward compatiblity): virtuemart_custom_id=7, virtuemart_custom_id=4,

since virtuemart_custom_id + virtuemart_product_id are all indexed in virtuemart_product_customfields we could do a query:
- as far as we know that RED is ID 4:
query:
select pf.virtuemart_product_id from virtuemart_product_customfields as pf, virtuemart_customs as c where c.virtuemart_custom_id = pf.virtumart_custom_id and pf.virtuemart_custom_id = 4  //this is a full indexed query to get products with color RED
indexed query by value:
select pf.virtuemart_product_id from virtuemart_product_customfields as pf, virtuemart_customs as c where c.virtuemart_custom_id = pf.virtumart_custom_id and c.custom_tile = "RED"  //this is a full indexed query to get products with color RED by value as far as we index custom_title

please write down your notes here so we can make this happen.

best regards, stan

[Studio 42]

Hi,
I don't think that multivariant need this, i use simple customfields text on add this in an unknow customfield position to hide it and use this for filtering.
But i agree that an index for the customfields values should be added in virtuemart(i already added it on some websites).

[Milbo]

Patrick, even this does the MV automatically for you. Check the config of the prototype