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) ASminimal_price
,price_index
.min_price
,price_index
.max_price
,price_index
.tier_price
,cat_index
.position
AScat_index_position
,stock_status_index
.stock_status
ASis_salable
FROMcatalog_product_entity
ASe
\n INNER JOINcatalog_product_index_price
ASprice_index
ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0\n INNER JOINcatalog_category_product_index_store1
AScat_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 JOINcataloginventory_stock_status
ASstock_status_index
ON e.entity_id = stock_status_index.product_id ORDER BYsearch_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('SELECTe
.,p...', array()) called at [vendor\/magento\/zendframework1\/library\/Zend\/Db\/Adapter\/Pdo\/Abstract.php:238]\n#4 Zend_Db_Adapter_Pdo_Abstract->query('SELECT
e.*,
p…', array()) called at [vendor/magento/framework/DB/Adapter/Pdo/Mysql.php:542]\n#5 Magento\Framework\DB\Adapter\Pdo\Mysql->_query('SELECTe
.*, `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'