The Search Result page of default does not work when switching the price scope to "Store View"

Created by Rosa Truong, Modified on Tue, 9 Jun, 2020 at 11:05 AM by Rosa Truong

Issue: 

We are using extension version 2.2.4 on Magento 2.2.6. The extension seems to work fine in general, however, as soon as we are switching the price scope to "Store View" in the Default Store Configuration, the Search Result page of Magento's default search module does not work anymore. The following error occurs there:

2 exception(s):
Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'main_table.store_id' in 'where clause', query was: SELECT IFNULL(FLOOR(value / '1.0000' ) + 1, 1) AS `range`, COUNT(*) AS `metrix` FROM (SELECT DISTINCT  `main_table`.`entity_id`, `main_table`.`value` FROM `catalog_product_index_eav_decimal` AS `main_table`
 INNER JOIN `search_tmp_5ed0c997c2e006_67506348` AS `entities` ON main_table.entity_id  = entities.entity_id
 LEFT JOIN `cataloginventory_stock_status` AS `stock_index` ON main_table.source_id = stock_index.product_id WHERE (main_table.attribute_id = '179') AND (main_table.store_id = '1' ) AND (stock_index.stock_status = 1)) AS `main_table` WHERE (main_table.store_id = '1') GROUP BY `range` ORDER BY `range` ASC

Exception #1 (PDOException): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'main_table.store_id' in 'where clause'

Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'main_table.store_id' in 'where clause', query was: SELECT IFNULL(FLOOR(value / '1.0000' ) + 1, 1) AS `range`, COUNT(*) AS `metrix` FROM (SELECT DISTINCT  `main_table`.`entity_id`, `main_table`.`value` FROM `catalog_product_index_eav_decimal` AS `main_table`
 INNER JOIN `search_tmp_5ed0c997c2e006_67506348` AS `entities` ON main_table.entity_id  = entities.entity_id
 LEFT JOIN `cataloginventory_stock_status` AS `stock_index` ON main_table.source_id = stock_index.product_id WHERE (main_table.attribute_id = '179') AND (main_table.store_id = '1' ) AND (stock_index.stock_status = 1)) AS `main_table` WHERE (main_table.store_id = '1') GROUP BY `range` ORDER BY `range` ASC

Solution:

The issue is caused due to the fact that some attributes on your site have this config as follows:

"Catalog Input Type for Store Owner = Price" and "Use in Search Results Layered Navigation = Yes" 

Our module does not support when catalog_product_index_eav_decimal has the Price type.

However, we would like to give you the solution to resolve it.

To fix this issue, please change the config of these attributes as this "Use in Search Results Layered Navigation = No"

Please check and follow our guide.


If you need any further support, please feel free to contact us


Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article