Magento 2.3.1 – Search Bar Error: Unknown Column ‘search_result.score’

databasemagento2.3

Relatively new to Magento. Helped a friend migrate a site from M1 to M2. Using Porto Theme.

Search Bar seems to work (does auto querying as you type and makes suggestions) until you hit 'Enter' at that point you get the following error:

{"0":"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'search_result.score' in 'order clause', query was: SELECT e., price_index.price, price_index.tax_class_id, price_index.final_price, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS minimal_price, price_index.min_price, price_index.max_price, price_index.tier_price, cat_index.position AS cat_index_position, stock_status_index.stock_status AS is_salable FROM catalog_product_entity AS e\n INNER JOIN catalog_product_index_price AS price_index ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0\n INNER JOIN catalog_category_product_index_store1 AS cat_index ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(3, 4) AND cat_index.category_id=2\n INNER JOIN cataloginventory_stock_status AS stock_status_index ON e.entity_id = stock_status_index.product_id ORDER BY search_result.score desc\n LIMIT 24","1":"#1 Magento\Framework\DB\Statement\Pdo\Mysql->_execute(array()) called at [vendor/magento/zendframework1/library/Zend/Db/Statement.php:303]\n#2 Zend_Db_Statement->execute(array()) called at [vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php:480]\n#3 Zend_Db_Adapter_Abstract->query('SELECT e., p...', array()) called at [vendor\/magento\/zendframework1\/library\/Zend\/Db\/Adapter\/Pdo\/Abstract.php:238]\n#4 Zend_Db_Adapter_Pdo_Abstract->query('SELECTe.*,p…', array()) called at [vendor/magento/framework/DB/Adapter/Pdo/Mysql.php:542]\n#5 Magento\Framework\DB\Adapter\Pdo\Mysql->_query('SELECT e.*, `p…', array()) called at [vendor/magento/framework/DB/Adapter/Pdo/Mysql.php:617]\n#6 Magento\Framework\DB\Adapter\Pdo\Mysql->query(&Magento\Framework\DB\Select#0000000027346819000000004114b559#, array()) called at [vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php:737]…

Switched back to Luma theme and still seeing the issue.

Been attempting to track down a solution and have exhausted my limited knowledge. Found the same issue here: Column not found: 1054 Unknown column 'search_result.score' in 'order clause'; but the suggestions there didn't work for me.

It would appear that there is supposed to be a table named search_result and I can't see in the mysql logs where the system has created or attempted to create a temporary table named 'search_result'.

It looks to me like the sql is incorrect in that a relationship to search_result is never established in the query, but maybe that's not needed.

Do any of you magento experts have any idea what I'm dealing with here? Can I change the query?

Best Answer

A million thanks to @UncleHank for coming up with a solution that worked for me!

Here was his answer to a similar question: Column not found: 1054 Unknown column 'search_result.score' in 'order clause'

Related Topic