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
    Mar 26 '17 at 14:19
  • Varien_Db_Select::__toString() method use assemble() method... The result is identical, of course.
    – sergei.sss
    Mar 26 '17 at 14:24

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
    Mar 26 '17 at 14:52

Your Answer

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

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