search

magento / magento2

Prior to making any Submission(s), you must sign an Adobe Contributor License Agreement, available here at: https://opensource.adobe.com/cla.html. All Submissions you make to Adobe Inc. and its affiliates, assigns and subsidiaries (collectively “Adobe”) are subject to the terms of the Adobe Contributor License Agreement.
http://www.magento.com
Open Software License 3.0
11.56k stars 9.32k forks source link

Magento 2.1.5: Product Widget for Homepage - SQL Error SQLSTATE[42S22] Column not found #8721

Closed caliboy007 closed 7 years ago

caliboy007 commented 7 years ago

Environment

  1. Magento 2.1.5
  2. PHP Version: 5.5.38
  3. MySQL Version 5.6.32

Steps to reproduce

  1. Login to Magento 2 Admin
  2. Content > Pages > Select 'Homepage' > Edit
  3. Under Content > Insert Widget (2nd icon from left, top row)
  4. Widget Type: Catalog Products List
  5. Enter Title, Number of Products, etc.
  6. Under Conditions, select category in which products are displayed and custom condition such as 'display on homepage' which is set to Yes.

Expected result

  1. Products matching the category and single condition (yes/no) should display on the homepage.

Actual result

  1. Error received, after enabling developer mode:

Error filtering template: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.b_homepage' in 'where clause', query was:

SELECT COUNT(DISTINCT e.entity_id) FROM catalog_product_flat_1 AS e INNER JOIN catalog_category_product_index AS cat_index ON cat_index.product_id=e.entity_id AND cat_index.store_id='1' AND cat_index.visibility IN(2, 4) AND cat_index.category_id='2' 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 WHERE (((IFNULL(e.entity_id, 0) IN (SELECT catalog_category_product.product_id FROM catalog_category_product WHERE (category_id IN ('42')))) AND(IFNULL(e.b_homepage, 0) = '1') ))

I have already performed the following tasks:

  1. Full Reindex
  2. Full Cache Clean and Flush
  3. Static Content Deploy
  4. Checked manually in the following files for fixes (as per this issue appear in older version of Magento 2)

/vendor/magento/module-catalog-widget/Model/Rule/Condition/Combine.php /vendor/magento/module-catalog-widget/Model/Rule/Condition/Product.php /vendor/magento/module-catalog-widget/Test/Unit/Model/Rule/Condition/CombineTest.php

  1. When setting a single condition such as category by itself no error is shown and products are displayed correctly.
  2. When setting two conditions (other than the b_homepage condition) no error is shown and products are displayed correctly.
caliboy007 commented 7 years ago

Solved: In regards to the attribute giving the error, I did not select the settings under 'Use in Product Listing' to 'Yes'. After I selected 'Yes' and did complete reindex the widget worked with the two conditions.

allanpaiste commented 7 years ago

considering that it's possible to end up crashing any page when using that widget with certain attributes in the filter rules, I would recommend not closing this issue report.

Basically the catalog-widget expects all the attributes to be in the flat index, which is not the case with some of the default options it provides for the user to build a filter. There's logic in place there as well that will work when flat is disabled which should kick in for non-flat attributes. Currently it's pretty rough on making the decision without any exceptions. It's either everything flat or everything explicit join.

Here's one potential fix for that: https://github.com/allanpaiste/magento2-patches/blob/master/fix-crash-when-configuring-rules-for-non-flat-indexer-attributes.patch

Note that it's pretty dodgy as it does another DB request to get the info about the indexed attributes, but it does avoid the reported crash.