Tweeted twitter.com/#!/StackMagento/status/525801849316577281
3 update with additional information
source | link

On the first sight I would have guessed this should mean e.special_price should be a known column as it is present in columns at index 37. However the error remains the same, Unknown column 'e.special_price' in 'where clause'.
When I try to use 'main_table.special_price' instead I don't get the error but the query doesn't work/has no effect at all.
And by the way it doesn't matter if I use ->addFieldToFilter() or ->addAttributeToFilter(); I'v tried all variants of these I know and the error is always the exact same.


Edit2: It seems the problem is not directly caused by the statement above but indirectly. As this is in Mage_Catalog_Model_Layer at some point Mage_Catalog_Model_Resource_Layer_Filter_Price->loadPrices() gets called and the passed filter also uses the parts of the $collection you see above. So the resulting SQL here for the example above is

SELECT ROUND(((e.min_price -(e.min_price/(1+(CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END))*CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END)+((e.min_price-(e.min_price/(1+(CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END))*CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END))*CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END))) * 1, 2) AS `min_price_expr` FROM `catalog_product_index_price` AS `e`
INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id='3' AND cat_index.category_id = '2' 
WHERE ((`e`.`special_price` > 0)) AND ( e.website_id = '1' ) AND ( e.customer_group_id = 0) AND ((e.min_price -(e.min_price/(1+(CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END))*CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END)+((e.min_price-(e.min_price/(1+(CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END))*CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END))*CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END)) IS NOT NULL) AND ((e.min_price -(e.min_price/(1+(CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END))*CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END)+((e.min_price-(e.min_price/(1+(CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END))*CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END))*CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END)) >= -0.005) ORDER BY (e.min_price -(e.min_price/(1+(CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END))*CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END)+((e.min_price-(e.min_price/(1+(CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END))*CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END))*CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END)) ASC LIMIT 30 OFFSET 32

And here in the WHERE clause you see ((e.special_price > 0)) which is causing the error. But well, I still have no clue why this is only happening with flat tables enabled and I also am not sure what the best way to work around this would be...

On the first sight I would have guessed this should mean e.special_price should be a known column as it is present in columns at index 37. However the error remains the same, Unknown column 'e.special_price' in 'where clause'.
When I try to use 'main_table.special_price' instead I don't get the error but the query doesn't work/has no effect at all.
And by the way it doesn't matter if I use ->addFieldToFilter() or ->addAttributeToFilter(); I'v tried all variants of these I know and the error is always the exact same.

On the first sight I would have guessed this should mean e.special_price should be a known column as it is present in columns at index 37. However the error remains the same, Unknown column 'e.special_price' in 'where clause'.
When I try to use 'main_table.special_price' instead I don't get the error but the query doesn't work/has no effect at all.
And by the way it doesn't matter if I use ->addFieldToFilter() or ->addAttributeToFilter(); I'v tried all variants of these I know and the error is always the exact same.


Edit2: It seems the problem is not directly caused by the statement above but indirectly. As this is in Mage_Catalog_Model_Layer at some point Mage_Catalog_Model_Resource_Layer_Filter_Price->loadPrices() gets called and the passed filter also uses the parts of the $collection you see above. So the resulting SQL here for the example above is

SELECT ROUND(((e.min_price -(e.min_price/(1+(CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END))*CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END)+((e.min_price-(e.min_price/(1+(CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END))*CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END))*CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END))) * 1, 2) AS `min_price_expr` FROM `catalog_product_index_price` AS `e`
INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id='3' AND cat_index.category_id = '2' 
WHERE ((`e`.`special_price` > 0)) AND ( e.website_id = '1' ) AND ( e.customer_group_id = 0) AND ((e.min_price -(e.min_price/(1+(CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END))*CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END)+((e.min_price-(e.min_price/(1+(CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END))*CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END))*CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END)) IS NOT NULL) AND ((e.min_price -(e.min_price/(1+(CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END))*CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END)+((e.min_price-(e.min_price/(1+(CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END))*CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END))*CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END)) >= -0.005) ORDER BY (e.min_price -(e.min_price/(1+(CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END))*CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END)+((e.min_price-(e.min_price/(1+(CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END))*CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END))*CASE e.tax_class_id WHEN 1 THEN       0.1900 WHEN 2 THEN       0.0700 WHEN 4 THEN       0.1900 WHEN 7 THEN       0.0700  ELSE 0 END)) ASC LIMIT 30 OFFSET 32

And here in the WHERE clause you see ((e.special_price > 0)) which is causing the error. But well, I still have no clue why this is only happening with flat tables enabled and I also am not sure what the best way to work around this would be...

2 added debug info
source | link
$collection = Mage::getModel('catalog/category')
    ->load($rootCategoryId)
    ->getProductCollection()
    ->addAttributeToSelect('*')
    ->addFieldToFilter(array(array('attribute' => 'special_price', 'gt' => 0)))
    ->addStoreFilter()
    ->setPageSize($this->getProductsLimit())
    ->setCurPage($page)
    ->addAttributeToFilter('status', 1);

However "special_price" also is a column of catalog_product_flat_x so I'm not really sure why this problem comes with enabling flat tables. And I also have no idea how to work around it other than disabling flat tables which I'd like to avoid as the product catalog is very big and I guess it would impact the performance. So how can I make this work with flat tables enabled?


Edit: When I run Mage::log($collection->getSelect()) without the problematic line (because otherwise I get the error and wouldn't reach Mage::log) 'special_price' has the following occurrences from top to bottom:

Varien_Db_Select Object
(
    [[...]]
    [_adapter:protected] => Magento_Db_Adapter_Pdo_Mysql Object
        (
            [[...]]
            [_ddlCache:protected] => Array
                (
                    [1] => Array
                        (
                            [catalog_product_flat_3] => Array
                                (
                                    [[...]]
                                    [special_price] => Array
                                        (
                                            [SCHEMA_NAME] => 
                                            [TABLE_NAME] => catalog_product_flat_3
                                            [COLUMN_NAME] => special_price
                                            [COLUMN_POSITION] => 33
                                            [DATA_TYPE] => decimal
                                            [DEFAULT] => 
                                            [NULLABLE] => 1
                                            [LENGTH] => 
                                            [SCALE] => 4
                                            [PRECISION] => 12
                                            [UNSIGNED] => 
                                            [PRIMARY] => 
                                            [PRIMARY_POSITION] => 
                                            [IDENTITY] => 
                                        )
                                    [[...]]
                                )
                        )
                )
        )
    [[...]]
    [_parts:protected] => Array
        (
            [straightjoin] => 
            [distinct] => 
            [columns] => Array
                (
                    [[...]]
                    [37] => Array
                        (
                            [0] => e
                            [1] => special_price
                            [2] => 
                        )
                    [[...]]
                    [65] => Array
                        (
                            [0] => at_main_table.special_price
                            [1] => Zend_Db_Expr Object
                                (
                                    [_expression:protected] => IF(at_main_table.special_price.value_id > 0, at_main_table.special_price.main_table.special_price, at_main_table.special_price_default.main_table.special_price)
                                )

                            [2] => main_table.special_price
                        )
                    [[...]]
                )
            [[...]]
            [from] => Array
                (
                    [[...]]
                    [at_main_table.special_price_default] => Array
                        (
                            [joinType] => inner join
                            [schema] => 
                            [tableName] => catalog_product_entity
                            [joinCondition] => (`at_main_table`.`special_price`.`entity_id` = `e`.`entity_id`) AND `at_main_table`.`special_price_default`.`store_id` = 0
                        )

                    [at_main_table.special_price] => Array
                        (
                            [joinType] => left join
                            [schema] => 
                            [tableName] => catalog_product_entity
                            [joinCondition] => (`at_main_table`.`special_price`.`entity_id` = `e`.`entity_id`) AND (`at_main_table`.`special_price`.`store_id` = 3)
                        )
                    [[...]]
                )
            [where] => Array
                (
                    [0] => ((IF(at_main_table.special_price.value_id > 0, at_main_table.special_price.main_table.special_price, at_main_table.special_price_default.main_table.special_price) > 0))
                )
            [[...]]
        )
    [[...]]
)

On the first sight I would have guessed this should mean e.special_price should be a known column as it is present in columns at index 37. However the error remains the same, Unknown column 'e.special_price' in 'where clause'.
When I try to use 'main_table.special_price' instead I don't get the error but the query doesn't work/has no effect at all.
And by the way it doesn't matter if I use ->addFieldToFilter() or ->addAttributeToFilter(); I'v tried all variants of these I know and the error is always the exact same.

Mage::getModel('catalog/category')
    ->load($rootCategoryId)
    ->getProductCollection()
    ->addAttributeToSelect('*')
    ->addFieldToFilter(array(array('attribute' => 'special_price', 'gt' => 0)))
    ->addStoreFilter()
    ->setPageSize($this->getProductsLimit())
    ->setCurPage($page)
    ->addAttributeToFilter('status', 1);

However "special_price" also is a column of catalog_product_flat_x so I'm not really sure why this problem comes with enabling flat tables. And I also have no idea how to work around it other than disabling flat tables which I'd like to avoid as the product catalog is very big and I guess it would impact the performance. So how can I make this work with flat tables enabled?

$collection = Mage::getModel('catalog/category')
    ->load($rootCategoryId)
    ->getProductCollection()
    ->addAttributeToSelect('*')
    ->addFieldToFilter(array(array('attribute' => 'special_price', 'gt' => 0)))
    ->addStoreFilter()
    ->setPageSize($this->getProductsLimit())
    ->setCurPage($page)
    ->addAttributeToFilter('status', 1);

However "special_price" also is a column of catalog_product_flat_x so I'm not really sure why this problem comes with enabling flat tables. And I also have no idea how to work around it other than disabling flat tables which I'd like to avoid as the product catalog is very big and I guess it would impact the performance. So how can I make this work with flat tables enabled?


Edit: When I run Mage::log($collection->getSelect()) without the problematic line (because otherwise I get the error and wouldn't reach Mage::log) 'special_price' has the following occurrences from top to bottom:

Varien_Db_Select Object
(
    [[...]]
    [_adapter:protected] => Magento_Db_Adapter_Pdo_Mysql Object
        (
            [[...]]
            [_ddlCache:protected] => Array
                (
                    [1] => Array
                        (
                            [catalog_product_flat_3] => Array
                                (
                                    [[...]]
                                    [special_price] => Array
                                        (
                                            [SCHEMA_NAME] => 
                                            [TABLE_NAME] => catalog_product_flat_3
                                            [COLUMN_NAME] => special_price
                                            [COLUMN_POSITION] => 33
                                            [DATA_TYPE] => decimal
                                            [DEFAULT] => 
                                            [NULLABLE] => 1
                                            [LENGTH] => 
                                            [SCALE] => 4
                                            [PRECISION] => 12
                                            [UNSIGNED] => 
                                            [PRIMARY] => 
                                            [PRIMARY_POSITION] => 
                                            [IDENTITY] => 
                                        )
                                    [[...]]
                                )
                        )
                )
        )
    [[...]]
    [_parts:protected] => Array
        (
            [straightjoin] => 
            [distinct] => 
            [columns] => Array
                (
                    [[...]]
                    [37] => Array
                        (
                            [0] => e
                            [1] => special_price
                            [2] => 
                        )
                    [[...]]
                    [65] => Array
                        (
                            [0] => at_main_table.special_price
                            [1] => Zend_Db_Expr Object
                                (
                                    [_expression:protected] => IF(at_main_table.special_price.value_id > 0, at_main_table.special_price.main_table.special_price, at_main_table.special_price_default.main_table.special_price)
                                )

                            [2] => main_table.special_price
                        )
                    [[...]]
                )
            [[...]]
            [from] => Array
                (
                    [[...]]
                    [at_main_table.special_price_default] => Array
                        (
                            [joinType] => inner join
                            [schema] => 
                            [tableName] => catalog_product_entity
                            [joinCondition] => (`at_main_table`.`special_price`.`entity_id` = `e`.`entity_id`) AND `at_main_table`.`special_price_default`.`store_id` = 0
                        )

                    [at_main_table.special_price] => Array
                        (
                            [joinType] => left join
                            [schema] => 
                            [tableName] => catalog_product_entity
                            [joinCondition] => (`at_main_table`.`special_price`.`entity_id` = `e`.`entity_id`) AND (`at_main_table`.`special_price`.`store_id` = 3)
                        )
                    [[...]]
                )
            [where] => Array
                (
                    [0] => ((IF(at_main_table.special_price.value_id > 0, at_main_table.special_price.main_table.special_price, at_main_table.special_price_default.main_table.special_price) > 0))
                )
            [[...]]
        )
    [[...]]
)

On the first sight I would have guessed this should mean e.special_price should be a known column as it is present in columns at index 37. However the error remains the same, Unknown column 'e.special_price' in 'where clause'.
When I try to use 'main_table.special_price' instead I don't get the error but the query doesn't work/has no effect at all.
And by the way it doesn't matter if I use ->addFieldToFilter() or ->addAttributeToFilter(); I'v tried all variants of these I know and the error is always the exact same.

1
source | link

How to addFieldToFilter for attribute special_price with flat product tables?

In a class which extends Mage_Catalog_Model_Layer I have the following code:

Mage::getModel('catalog/category')
    ->load($rootCategoryId)
    ->getProductCollection()
    ->addAttributeToSelect('*')
    ->addFieldToFilter(array(array('attribute' => 'special_price', 'gt' => 0)))
    ->addStoreFilter()
    ->setPageSize($this->getProductsLimit())
    ->setCurPage($page)
    ->addAttributeToFilter('status', 1);

This code works as long as I have the flat product table disabled (and it passes all products with a special price greate 0 to a custom product list with a layered navigation - note that "special_price > 0" alone isn't very useful and I have more filters but to explain the problem this is enough).
It also works when I remove the line ->addFieldToFilter(array(array('attribute' => 'special_price', 'gt' => 0))) but now the code is obviously useless as it passes all products.

As soon as I enable the flat products table with the problematic line/filter I get the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.special_price' in 'where clause'

However "special_price" also is a column of catalog_product_flat_x so I'm not really sure why this problem comes with enabling flat tables. And I also have no idea how to work around it other than disabling flat tables which I'd like to avoid as the product catalog is very big and I guess it would impact the performance. So how can I make this work with flat tables enabled?