Cron context changes collection filters

Question

I'm building a collection of products with a few filters. I select a custom attribute, price data, and add a filter for the custom attribute.

$pCollection = Mage::getModel('catalog/product')->getCollection()->setStoreId(1)
    ->addAttributeToSelect('name')
    ->addAttributeToSelect('custom')
    ->addAttributeToFilter('custom',array('in' => array(1,2,3,4)))
    ->addMinimalPrice()
    ->addFinalPrice()
    ->addTaxPercents();

This code is in a method in an observer model, and will be called on a cron schedule. If I call it through a shell script to test (i.e. Mage::getModel('my/observer')->method()) it generates a proper SQL query for the collection, as follows:

SELECT 
    `e`.*, 
    IF(at_custom.value_id > 0, at_custom.value, at_custom_default.value) AS `custom`, 
    `price_index`.`price`, 
    `price_index`.`tax_class_id`, 
    `price_index`.`final_price`, 
    IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), 
    price_index.min_price) AS `minimal_price`, 
    `price_index`.`min_price`, 
    `price_index`.`max_price`, 
    `price_index`.`tier_price` FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_product_entity_varchar` AS `at_custom_default` ON (`at_custom_default`.`entity_id` = `e`.`entity_id`) AND (`at_custom_default`.`attribute_id` = '464') AND `at_custom_default`.`store_id` = 0
LEFT JOIN `catalog_product_entity_varchar` AS `at_custom` ON (`at_custom`.`entity_id` = `e`.`entity_id`) AND (`at_custom`.`attribute_id` = '464') AND (`at_custom`.`store_id` = 1)
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 (IF(at_custom.value_id > 0, at_custom.value, at_custom_default.value) IN(1,2,3,4))

This brings back the expected results. However, when it runs due to the cron schedule triggering it, a different set of results is returned. I debugged it and found that the same method when run by cron produces the following SQL:

SELECT 
    1 AS `status`, 
    `e`.`entity_id`, 
    `e`.`type_id`, 
    `e`.`attribute_set_id`, 
    `e`.`name`, 
    `price_index`.`price`, 
    `price_index`.`tax_class_id`, 
    `price_index`.`final_price`, 
    IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, 
    `price_index`.`min_price`, 
    `price_index`.`max_price`, 
    `price_index`.`tier_price` FROM `catalog_product_flat_1` AS `e`
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

There's no mention or filter on the custom attribute. The attribute is setup properly to be visible on the frontend and collections, and filterable. Not sure what I'm missing, or why just changing what calls the method alters the result so much.

Answer 1

It looks in first example Magento runs query on EAV tables but in second one it uses flat tables. If attribute you use for addAttributeToFilter() is not set to be used in flat tables, call you are using won't work. Please try change this call:

->addAttributeToFilter('custom',array('in' => array(1,2,3,4)))

To this one:

 ->addAttributeToFilter(array(array('attribute' => 'custom', 'in' => array(1,2,3,4))))

It's core Magento behavior.

Answer 2

This happens because you have the flat catalog setting turned on.
And when on the cron app, magento seams to read from those tables.
You can disabled the flat catalog, but this is not a very good idea or you can tell magento to run your script using the admin store view. Add this at the top of your script

Mage::app()->setCurrentStore(Mage::getModel('core/store')->load(0));