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
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).
Patrick, even this does the MV automatically for you. Check the config of the prototype