Search results varies according to the search terms order

[razor7]

Hi! I have this issue in VM, if I search IE: "Safety Helmet" it matches the results of products with "Safety Helmet" keywords, but if I change the order of the search keyword to "Helmet Safety" it won't match anything. According to joomla debug log, it seems to be the SQL used by VM to perform the search with *LIKE*. In that case I suggest some changes to the actual query

Actual query

Code Select Expand

SELECT SQL_CALC_FOUND_ROWS p.virtuemart_product_id, l.product_name, l.product_s_desc, l.product_desc
FROM w2f9n3p4_virtuemart_products as p
LEFT JOIN w2f9n3p4_virtuemart_products_es_es as l ON l.virtuemart_product_id = p.virtuemart_product_id
LEFT JOIN w2f9n3p4_virtuemart_product_shoppergroups as ps ON p.virtuemart_product_id = ps.virtuemart_product_id
LEFT JOIN w2f9n3p4_virtuemart_product_categories as pc ON p.virtuemart_product_id = pc.virtuemart_product_id
LEFT JOIN w2f9n3p4_virtuemart_categories as c ON c.virtuemart_category_id = pc.virtuemart_category_id
LEFT JOIN w2f9n3p4_virtuemart_categories_es_es as cl ON cl.virtuemart_category_id = pc.virtuemart_category_id
WHERE (
  (
    l.product_name LIKE "%Safety%Helmet%"
    OR p.product_sku LIKE "%Safety%Helmet%"
    OR l.product_s_desc LIKE "%Safety%Helmet%"
    OR l.product_desc LIKE "%Safety%Helmet%"
    OR category_name LIKE "%Safety%Helmet%"
    OR category_description LIKE "%Safety%Helmet%"
  )
  AND c.published = 1
  AND (
    ps.virtuemart_shoppergroup_id= "1"
    OR ps.virtuemart_shoppergroup_id IS NULL
  )
  AND p.published="1"
)
group by p.virtuemart_product_id
ORDER BY p.product_sku ASC, p.virtuemart_product_id ASC
LIMIT 20
Maybe use:

Code Select Expand

WHERE MATCH(l.product_name, l.product_s_desc, l.product_desc, category_name, category_description)
      AGAINST ('+Safety +Helmet' IN BOOLEAN MODE)
Or option 2, tokenize the search words then create each word as a search where

Code Select Expand

$terms = explode(' ', $search);
foreach ($terms as $t) {
  $where .= " OR (l.product_name LIKE '%$t%' OR ... )";
}
Also maybe a config option to enable this by keyword search method where the default is full phrase search.

What do you think?

[razor7]

Bump?