Products Custom Fields Search
Posted: Tue May 20, 2014 9:58 am
Hi Folks
I know it still have a bug with the search functionnality with custom fields on the PRODUCTS module. We cant make a succefull search with 2 or more custom fields. But I think I have found
where the problem come from.
Take this SQL query for exemple:
SELECT SQL_CALC_FOUND_ROWS C . * , PH.hierarchy_id
FROM cms_module_products C
LEFT JOIN cms_module_products_prodtohier PH ON PH.product_id = C.id
LEFT JOIN cms_module_products_fieldvals FVA ON C.id = FVA.product_id
AND FVA.fielddef_id =5
LEFT JOIN cms_module_products_fieldvals FVB ON C.id = FVB.product_id
AND FVB.fielddef_id =1
WHERE C.status = 'published'
AND (
FVA.value = 'tertiaire'
)
AND (
FVB.value = '1 pers.'
)ORDER
BY C.product_name ASC
LIMIT 0 , 10000
This is a SQL query from my site after a serach on ONLY 2 custom fields. I
notice that the values of FVA and FVB are wrong. they are inverted: FVA.value should be "1 pers." and FVB.value should be "tertiaire" (ou also can imagine that the fielddef_id are inverted instead of value).
This is the reason why the search result are never good with more than one custom fields.
I tried to look inside the PHP code of this module. The mistake seems to be related to class.products_resultset.php and the $fieldid, $fieldval variables. But I am not good enough in PHP object to find where it start to go wrong.
Its not seems to be a big deal for someone who understand the object logic. And it will fix a realy important functionality of you GREAT products module.
thx for any help
I know it still have a bug with the search functionnality with custom fields on the PRODUCTS module. We cant make a succefull search with 2 or more custom fields. But I think I have found
where the problem come from.
Take this SQL query for exemple:
SELECT SQL_CALC_FOUND_ROWS C . * , PH.hierarchy_id
FROM cms_module_products C
LEFT JOIN cms_module_products_prodtohier PH ON PH.product_id = C.id
LEFT JOIN cms_module_products_fieldvals FVA ON C.id = FVA.product_id
AND FVA.fielddef_id =5
LEFT JOIN cms_module_products_fieldvals FVB ON C.id = FVB.product_id
AND FVB.fielddef_id =1
WHERE C.status = 'published'
AND (
FVA.value = 'tertiaire'
)
AND (
FVB.value = '1 pers.'
)ORDER
BY C.product_name ASC
LIMIT 0 , 10000
This is a SQL query from my site after a serach on ONLY 2 custom fields. I
notice that the values of FVA and FVB are wrong. they are inverted: FVA.value should be "1 pers." and FVB.value should be "tertiaire" (ou also can imagine that the fielddef_id are inverted instead of value).
This is the reason why the search result are never good with more than one custom fields.
I tried to look inside the PHP code of this module. The mistake seems to be related to class.products_resultset.php and the $fieldid, $fieldval variables. But I am not good enough in PHP object to find where it start to go wrong.
Its not seems to be a big deal for someone who understand the object logic. And it will fix a realy important functionality of you GREAT products module.
thx for any help