4

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

  • Try debugging using echo ...->getSelect() .. you may need to change 'special_price' to 'main_table.special_price' – Renon Stewart Oct 23 '14 at 18:42
  • Hi, or try ->addAttributeToFilter('special_price',array('gt' => 0)) – Stefan Bothner Oct 23 '14 at 18:46
  • Thanks a lot for the feedback. Unfortunately both tips do not work in the expected way. I've edited my post and added the relevant output of ->getSelect(). – Jey DWork Oct 23 '14 at 23:45
  • Another thing: When I run the whole plain SQL query returned by ->getSelect() as it is against the database I do get the expected products from the the flat table with no error. But I get the query from the Mage::log of the very same request where I see my Magento Exception with the too well known error "Unknown column 'e.special_price'". So I'm starting to think the problem has something to do with the layered navigation as well maybe? So when the SQL at that point is okay where does it get modified further... – Jey DWork Oct 24 '14 at 0:29
  • After some more debugging I now know the culprit (Edit2 in the original Post). But I don't get why this only happens with flat tables. And I also have no good workaround so far. – Jey DWork Oct 24 '14 at 1:47
2

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.

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.

  • 1
    Yeah, but this will be affecting the performance. – Beto Castillo Aug 17 '17 at 2:15
  • There will be nothing to perform if the collection is not full when flat is enabled. This is mandatory for the requested functionality, not for performance benefits. – Insan3 Nov 16 '17 at 15:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.