How to addFieldToFilter for attribute special_price with flat product tables?

Question

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

$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);

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?


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.


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...

Answer 1

I've found a workaround which looks feasible in my case. But I guess what's happening here is not exactly the intended behaviour of Magento collections in (custom) layered models with flat tables enabled. The workaround is far away from fixing the general problem but it works in the specific situation:

I overwrote Mage_Catalog_Model_Resource_Layer_Filter_Price by placing a copy of it in app\code\local\Mage\Catalog\Model\Resource\Layer\Filter.
In _replaceTableAlias() right before the return I added the following code

if (Mage::helper('catalog/product_flat')->isEnabled()) {
            $activePrefix = "";
            if (strpos($conditionString, 'AND') !== false) {
                $activePrefix = "AND ";
            }
            if (strpos($conditionString, 'special_price') !== false) {
                return $activePrefix."1 = 1";
            }
            if (strpos($conditionString, 'special_to_date') !== false) {
                return $activePrefix."1 = 1";
            }
            if (strpos($conditionString, 'special_from_date') !== false) {
                return $activePrefix."1 = 1";
            }
        }

Kind of ugly but it does what it should and I don't expect complications as I don't think 'special_price' is something that should ever come up in the condition string other than in the case where I want to replace it. First tests seem to confirm that.

Answer 2

I may be quite late, but you could try using the following:

   <?php
   /** @var $collection Mage_Catalog_Model_Resource_Category_Collection */
   $collection = Mage::getModel('catalog/category', array('disable_flat' => true))->getCollection();
   // ... custom stuff

This will switch from FLAT to EAV, and your filters would work as expected.