Raw sql for product collection with addAttributeForSelect() method without loading collection

Question

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.

Answer 1

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

Answer 2

Try to load the collection and then print:

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