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

SQL exception when creating a widget with either layout update for pages and layout condition for a specific layout #8966

Closed grasdaggel closed 7 years ago

grasdaggel commented 7 years ago

Preconditions

  1. We are using Magento 2.1.5
  2. We are currently developing a custom theme
  3. PHP: 7.0.16
  4. Apache2

Steps to reproduce

  1. Log into magento backend
  2. Create a CMS page with layout: 2 columns with right bar and save
  3. Go to Widget area and click on "Create new widget" on top right corner
  4. As Type use: "List of catalog products", as style choose your custom theme (not luma or blank)
  5. select the default store view as desired store view and save
  6. After save go to options.
  7. Enter any title
  8. opt out for controls
  9. enter any number for products
  10. change condition to: (if all true then) layout is 2 columns with right bar
  11. add a layout update
  12. Displayed on: "pages"
  13. Page: CMS Pages (All) 13.1. this seems also to be a bug, as our custom cms page is not listed here, while it should
  14. Container: Main Content Are
  15. Save and reload frontpage of store

Expected result

  1. On all pages with 2 columns with right bar as layout there is now an unstyled list of products. As template I most likely will have n-times the single item category layout.

Actual result

  1. Exception. 2 exception(s): 
    
Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.page_layout' 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`.`page_layout`, 0) = '2columns-right') ))
Exception #1 (PDOException): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.page_layout' in 'where clause'

Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.page_layout' 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.page_layout, 0) = '2columns-right') ))



### Request to bugfix

Insert custom created CMS pages into the list of targetable CMS Pages. This will most likely automatically fix the bug, as Magento cannot get into an logic trap with the Condition and the layout update. 
<!--- (This may be platform independent comment) -->
grasdaggel commented 7 years ago

A simple workaround:

You can achieve the very same result with the following:

  1. create a block and add widget logic to it - or whatever you want.
  2. go to all desired pages and add following XML to your Design-tab:
<referenceContainer name="sidebar.main">
<block class="Magento\Cms\Block\Block" name="right.permanent.callout">
<arguments>
        <argument name="block_id" xsi:type="string">your-block-identifier</argument>
</arguments>
</block>
</referenceContainer>
  1. change your-block-identifier to the block you've created before
grasdaggel commented 7 years ago

7 days, not even noticed until now. Is anyone there?

korostii commented 7 years ago

If you're looking for a quick advice, you've come to the wrong place. Things move rather slow out here. Even if your issue gets reproduced and fixed, it'll take approximately 2 months minimum for the fix to reach a release version. I'd suggest that you describe your desired result on http://magento.stackexchange.com/ (and code you have and then you just might get a that the condition you enter for the "List of catalog products" is used to filter exactly those catalog products, and products don't have a property or attribute called "page_layout".

But, nevertheless, the Exception is indeed symptomatic of a bug, I'd expect such an invalid value to get filtered by Magento. Were you inserting the condition via the visual constructor (or manually the widget code after creating widget)?

grasdaggel commented 7 years ago

I don't look for quick advice. This is clearly a bug as Magento2 shouldn't allow configurations that trigger hard SQL exceptions. As you can see I've already gave myself a "quick solution", so I don't really need a solution for that.

I used CMS blocks to inject and create the widget.

korostii commented 7 years ago

Ah so the "You can achieve the very same result" meant a workaround, not a second way to reproduce the issue? Sorry, I misunderstood. In that case, I'd suggest that you comprise a less ambiguous and easier to follow list of steps to reproduce. Personally, I got lost at step 6. Could you please provide the raw widget code you had inside your page's content in the end or a screenshot of it's condition? I think that would aid reproducing it (bugs that cannot be consistently reproduced on a clean installation don't even seem to reach the developers at Magento Inc. these days).

On a sidenote, I consider the custom design layouts utterly unpleasant to use in development due to the limitations they have. A possibility to target a specific CMS page (or a set of pages, categories etc.) from a fully functional layout .xml file (i.e. via a handle including its identifier or via an <update> tag in custom design layout) imho would save a lot of nerves in comparison. That way, you could track and deploy the changes done to this file via git, otherwise it's always the same dialog: "it's not working on my machine - oh, I forgot to tell you; you need to do some changes in admin".

grasdaggel commented 7 years ago

Im sorry, I do not have the widget raw code anymore. Maybe I can re-create it somehow, but more or less Step 6 is meant to "Save the widget initially and then re-edit it". Then you have the possibility to go to "Options". Adding a screenshot seems very useful to me so I'm about to reproduce it and make a better list to reproduce the error. :) Thanks for your constructive input so far!

Also, I've edited my first answer to make it more clear that this is a workaround and not a way to reproduce the bug. Thanks for that hint!

magento-engcom-team commented 7 years ago

@grasdaggel, thank you for your report. We were not able to reproduce this issue by following the steps you provided. If you'd like to update it, please reopen the issue. We tested the issue on 2.3.0-dev, 2.2.0, 2.1.9

UpSage commented 6 years ago

What happened? Is this already implemented?