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

Question

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

Answer 1

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.

Answer 2

I was finally able to fix this issue by changing the catalog search settings in Magento 2.3.1 from MySQL to Elasticsearch6+. Note, this has to be supported by your webhosting provider. I am with Webdomain.com and they do support it on the shared hosting plans.

Answer 3

Wow, I CAN'T believe it was THAT simple! I could have sworn I tried changing the search engine from MySQL to Elasticsearch6+ in the past, but nothing had worked.. I finally disabled and removed Mirasvit Elasticsearch Ultimate Extension, and I disabled the Mageplaza search extension that came with my Porto theme, and after disabling both of those, switching from MySql to Elasticsearch 6+ worked like a charm!

I'm going to try to reinstall Mirasvit Elasticsearch Ultimate again to see if it breaks the site again.

Thank you again for your response! This was driving me CRAZY!

FYI: This started happening AFTER I upgraded from 2.2.x to 2.3.. and I had Mirasvit Elasticsearch Ultimate, and Porto Theme.