1

I have a custom product resource collection, which I need to export to CSV. The collection is made up like this:

/** @var $collection Mage_Catalog_Model_Resource_Product_Collection */
$collection = Mage::getModel('catalog/product')->getCollection();
$collection->addPriceData();
$collection->addAttributeToFilter('status', 1);
$collection->addAttributeToSelect(array("product_id","sku","created_at","price","special_price","name","qty","row","shelf","level","single_copy","additional_information","condition","last_ordered"));
$collection->addExpressionAttributeToSelect(
    'last_ordered',
    '(SELECT created_at FROM sales_flat_order_item WHERE sales_flat_order_item.sku = e.sku ORDER BY created_at DESC LIMIT 1)',
    'last_ordered'
);
$collection->joinTable(
    array(
        'qbtp' => 'qb_tools_inventory_product'
    ),
    'product_id = entity_id',
    array(
        'qty' => 'qty',
        'row' => 'row',
        'shelf' => 'shelf',
        'level' => 'level',
    )
);
$collection->getSelect()->group('qbtp.product_id');
$collection->getSelect()->order(array('last_ordered'));

As a proof of concept I made up a quick solution in an external PHP file and initialized the Magento framework in it like this:

require_once('app/Mage.php'); //Path to Magento
umask(0);
Mage::app();

Now the POC works and I am building a separate module for the feature.

The strange thing: If I paste the same collection code into a helper or into a controller, I get an empty collection! The SQL query I get is also different:

From external script (works):

SELECT `e`.`entity_id`, `e`.`type_id`, `e`.`attribute_set_id`, `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`, `e`.`status`, `e`.`sku`, `e`.`created_at`, `e`.`price`, `e`.`special_price`, `e`.`name`, `e`.`condition`, `e`.`condition_value`, IF(at_last_ordered.value_id > 0, at_last_ordered.value, at_last_ordered_default.value) AS `last_ordered`, (SELECT created_at FROM sales_flat_order_item WHERE sales_flat_order_item.sku = e.sku ORDER BY created_at DESC LIMIT 1) AS `last_ordered`, `qbtp`.`qty`, `qbtp`.`row`, `qbtp`.`shelf`, `qbtp`.`level` 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 LEFT JOIN `catalog_product_entity_varchar` AS `at_last_ordered_default` ON (`at_last_ordered_default`.`entity_id` = `e`.`entity_id`) AND (`at_last_ordered_default`.`attribute_id` = '194') AND `at_last_ordered_default`.`store_id` = 0 LEFT JOIN `catalog_product_entity_varchar` AS `at_last_ordered` ON (`at_last_ordered`.`entity_id` = `e`.`entity_id`) AND (`at_last_ordered`.`attribute_id` = '194') AND (`at_last_ordered`.`store_id` = 1) INNER JOIN `qb_tools_inventory_product` AS `qbtp` ON (qbtp.product_id =e.entity_id) WHERE (e.status = 1) AND (e.status = '1') GROUP BY `qbtp`.`product_id` ORDER BY `last_ordered` ASC

From the helper or controller of the separate extension:

SELECT `e`.*, `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`, `at_status`.`value` AS `status`, `at_last_ordered`.`value` AS `last_ordered`, (SELECT created_at FROM sales_flat_order_item WHERE sales_flat_order_item.sku = e.sku ORDER BY created_at DESC LIMIT 1) AS `last_ordered`, `qbtp`.`qty`, `qbtp`.`row`, `qbtp`.`shelf`, `qbtp`.`level` FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '0' AND price_index.customer_group_id = 0 INNER JOIN `catalog_product_entity_int` AS `at_status` ON (`at_status`.`entity_id` = `e`.`entity_id`) AND (`at_status`.`attribute_id` = '96') AND (`at_status`.`store_id` = 0) LEFT JOIN `catalog_product_entity_varchar` AS `at_last_ordered` ON (`at_last_ordered`.`entity_id` = `e`.`entity_id`) AND (`at_last_ordered`.`attribute_id` = '194') AND (`at_last_ordered`.`store_id` = 0) INNER JOIN `qb_tools_inventory_product` AS `qbtp` ON (qbtp.product_id =e.entity_id) WHERE (at_status.value = '1') GROUP BY `qbtp`.`product_id` ORDER BY `last_ordered` ASC

Why is that???

Thanks a lot for any input!

Your Answer

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

Browse other questions tagged or ask your own question.