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

magento2sql

I'm searching on the internet for an answer but no luck so far.

I have 2 Magento 2 webshops. One is version 2.2.7 and one is 2.3.1.
Both have the latest Porto theme installed and both work with the child theme.

With both webshops I did some product imports and 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

The only time I saw someone with this issue was in another question:
magento2 search_tmp join leads to empty results in category listing from Gabriel but no answer so far.

What is it that is causing this?

Cheers,

Steve

Best Answer

I had a similar issue with the same setup (Porto Theme and Mageplaza Search Extensions). This issue was caused by the file public_html/magento/app/code/Mageplaza/LayeredNavigation/Model/ResourceModel/Fulltext/Collection.php had a part of the code commented out. 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.

Screenshot of commented code

Related Topic