Unknown column 'Relevance' in 'order clause

[keat]

I intially though fix (#1508) fixed this, but it still happens.

If I search for an item with a small product_code, it fails. (no products found)

A second subsequent search for the same product yields results

 

[11] SHOW COLUMNS FROM CubeCart_search; -- (0.000845909118652 sec) [CACHE WRITE]
[12] SELECT SQL_CALC_FOUND_ROWS `id`, `hits` FROM `CubeCart_search` WHERE CubeCart_search.searchstr = 'TR4' LIMIT 1; -- (0.00126695632935 sec) [NOT CACHED]
[13] SELECT FOUND_ROWS() as Count; -- (0.000113964080811 sec) [NOT CACHED]
[14] UPDATE `CubeCart_search` SET `hits` = '197' WHERE CubeCart_search.id = '229'; -- (0.000271081924438 sec) [NOT CACHED]
[15] SHOW INDEX FROM `CubeCart_inventory`; -- (0.000421047210693 sec) [CACHE WRITE]
[16] SHOW VARIABLES LIKE 'ft_min_word_len' -- (0.00037693977356 sec) [CACHE WRITE]
[17] SELECT I.* FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (I.name RLIKE '[[::]]' OR I.description RLIKE '[[::]]' OR I.product_code RLIKE '[[::]]') ORDER BY Relevance DESC LIMIT 12 OFFSET 0 -- (0.000531196594238 sec) [CACHE WRITE]
[18] SELECT I.* FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (I.name RLIKE '[[::]]' OR I.description RLIKE '[[::]]' OR I.product_code RLIKE '[[::]]') -- (0.0384960174561 sec) [CACHE WRITE]

 

[1] 1: Unknown column 'Relevance' in 'order clause' [ERROR - CACHE WRITE]

 

 

What I did notice, is that (#1508) when it yielded results, yielded more results.

ie searching TR1 also gave TR10, TR11, TR12 etc etc

 

I've gone back to changing $query = $q2.' '.$order_string.' '.$limit;  to $query = $q2.'  '.$limit;  for the time being.

 

 

[bsandall]

Al posted a different fix that should also work. I'm not sure why your code is still trying to add the sort by relevance clause, but it shouldn't be.

As for RLIKE returning more results, more results than what? FULLTEXT wouldn't have given you any results searching for 'TR1', and all of those other terms should also match. Is that an issue?

[Mike MacKechnie]

We had this error message pop up today in our 6.1.5 store.

 

I installed bsandall's fix and it seems to have resolved it.