Magento 1.9 – Raw SQL for Product Collection with addAttributeForSelect() Method Without Loading Collection

eavmagento-1.9product-collection

How can I get raw full sql query from non-loaded product collection with addAttributeToSelect() method without load collection ?

This code:

$collection = Mage::getModel('catalog/product')->getCollection();
$collection->addAttributeToSelect('name');
$collection->getSelect()->assemble();

retrieve only

SELECT `e`.* FROM `catalog_product_entity` AS `e`

If it is impossible, why?

P.S. There is no problem to get the full sql code if addAttributeToFilter() method was used.

Best Answer

It is impossible (using magento methods), because magento load products (EAV entity more correct) early then load attributes for them. 

public function load($printQuery = false, $logQuery = false) {
     ...
     1. $this->_renderFilters();
     2. $this->_renderOrders();
     3. $this->_loadEntities($printQuery, $logQuery);
     4. $this->_loadAttributes($printQuery, $logQuery);
     ...
}

I assumed that parametr $printQuery = true allow log EACH internal query in bulding collection process, but it does not use in both _loadEntities() and _loadAttributes(). I was confused by it.

Edit: For non-static attributes you can use joinType param - in this case it is possible get sql code without collection load: 

$collection->addAttributeToSelect('name', 'left');

So, if you want to get raw collection data as fast as possible, here the safe methods:

/** @var Mage_Catalog_Model_Resource_Product_Collection $collection */
$collection = Mage::getModel('catalog/product')->getCollection();

//It will be reflected in a Varien_Db_Select
$collection->addAttributeToFilter('sku', ['like' => 'm%']);

//It will be reflected in Varien_Db_Select
$collection->addAttributeToSelect('name', 'left');

//$collection->setOrder('name') - It won't be reflected in Varien_Db_Select
$collection->getSelect()->order('name');

//$collection->setPageSize(5)->setCurPage(1); - It won't be reflected.
$collection->getSelect()->limitPage(1, 5);

/** @var Mage_Core_Model_Resource $res */
$res = Mage::getSingleton('core/resource');

/** @var Magento_Db_Adapter_Pdo_Mysql $conn */
$conn = $res->getConnection('core_read');
$res = $conn->fetchAll($select, Zend_Db::FETCH_ASSOC);
var_dump($res);
Related Topic