3

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

1
  • did you find any solution on this? I'm getting this error on M232(upgraded from M231) with ES 6 setup on multi-stores.
    – sandip
    Jul 12 '19 at 13:19
3

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

4
  • Thanks for this - Porto still haven't updated their versions of the Mageplaza files to the latest versions which this is apparently fixed in. Aug 7 '19 at 10:09
  • did you get any solution ?
    – Camit1dk
    Sep 19 '19 at 12:45
  • @Camit1dk Yes this was the solution
    – Uncle Hank
    Sep 27 '19 at 11:59
  • @UncleHank Yes its solved. Thanks.
    – Camit1dk
    Sep 27 '19 at 12:49
0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.