Column not found: 1054 Unknown column ‘search_result.score’ in ‘order clause’

You may find this error probably using Porto theme installed and work with the parent or child theme on magento.

When you try to search for a product you get this error:

Column not found: 1054 Unknown column ‘search_result.score’ in ‘order clause’.

query was:

SELECT `e`.*, 
`stock_status_index`.`stock_status` AS `is_salable`, 
    `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` 
    FROM `catalog_product_entity` AS `e`
    INNER JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id 
    AND stock_status_index.website_id = 0 
    AND stock_status_index.stock_id = 1
    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
    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='3' 
    WHERE (stock_status_index.stock_status = 1) 
    ORDER BY `search_result`.`score` desc LIMIT 9

 

This issue was caused by the file <Site>public_html/app/code/Mageplaza/LayeredNavigation/Model/ResourceModel/Fulltext/Collection.php had a part of the code commented out.

$table = $temporaryStorage->storeDocuments($this->searchResult->getItems());

$this->getSelect()->joinInner(
[
‘search_result’ => $table->getName(),
],
‘e.entity_id = search_result.’ . TemporaryStorage::FIELD_ENTITY_ID,
[]
);

This code created the temporary search_result table but the line after with the order by using the score form table was not. Removing the comments around this block of code resolved this issue.

Leave a Reply

Your email address will not be published. Required fields are marked *