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

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.

  • Have u tried with $collection->getSelect()->__toString()
    – Amit Bera
    Commented Mar 26, 2017 at 14:19
  • Varien_Db_Select::__toString() method use assemble() method... The result is identical, of course.
    – sergei.sss
    Commented Mar 26, 2017 at 14:24

2 Answers 2


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

Try to load the collection and then print:

$collection = Mage::getModel('catalog/product')->getCollection();
echo $collection->getSelect()->__toString();
  • I wrote earlier that the collection shan't load.
    – sergei.sss
    Commented Mar 26, 2017 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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