Let's say I have a collection like:

$products = Mage::getModel('catalog/product')
            ->getCollection()
            ...
            ->load();

How do I print the actual MySQL code that gets executed?

• debugging
• magento

You can always view your sql query at a certain point by echoing getSelect as shown:

$products = Mage::getModel('catalog/product')
        ->getCollection();
echo $products->getSelect();

To change query parameters you want to check out methods like:

$products->addAttributeToSelect('someattribute');
$products->addAttributeToFilter('someattribute', array('eq'=>'1'));

You can print collection using below code: We can print query of collection using getSelect()->__toString()

$products = Mage::getModel(‘catalog/product’)
 ->addAttributeToFilter(‘status’, array(‘eq’ => 1));
echo $products->getSelect()->__toString();

Have you seen http://kuldipchudasama.wordpress.com/2012/07/16/magento-print-query-of-collection/? This works well.

Most other answers here say that $products->getSelect() will do it - this is fine if all you're going to do with it is echo, but in fact getSelect() doesn't just return a string, it returns a Varien_Db_Select object.

Invoking echo on that object automatically triggers its __toString() method, so you just get the SQL string, but try passing it to Mage::log() and you'll get a lot more than you expected.

If you just want to log the SQL, you can use:

Mage::log($products->getSelect()->__toString());

Or how about using the object's own:

$products->printLogQuery(false, true); // don't echo, do log

printLogQuery is defined in lib/Varien/Data/Collection/Db.php.

You can print

$products->getSelect()->assemble();

If you simple set the first parameter of ->load() to true, like so:

$products = Mage::getModel('catalog/product')
            ->getCollection()
            ...
           ->load(true);

I work with collections every day. This is without a doubt the correct way.

echo $collection->getSelectSql(true);

Step 1-

$result_colletion = print_r($collection->getSelect()); 
Mage::log($$result_colletion, null, custom_collection.log,true);

Step 2-

After that Login into magento admin section and enable to log setting . Please see below .

System > Configuration > Developer > Log Settings

Step 3-

After that see the log file custom_collection.log in var/log/ folder .

In Magento 2:-

namespace <Company>\<Module>\Block\Adminhtml\Tab\Log;
class Grid 
extends \Magento\Backend\Block\Widget\Grid\Extended
{

    protected $_collectionFactory;

    /**
     * Constructor
     *
     * @param \Magento\Backend\Block\Template\Context $context
     * @param \Magento\Backend\Helper\Data $backendHelper
     * @param \<Company>\<Module>\Model\ResourceModel\Log\CollectionFactory $collectionFactory
     * @param Psr\Log\LoggerInterface $logger
     * @param array $data
     */
    public function __construct(
        \Magento\Backend\Block\Template\Context $context,
        \<Company>\<Module>\Model\ResourceModel\Log\CollectionFactory $collectionFactory,
        \Psr\Log\LoggerInterface $logger,
        array $data = []
    ) {
        $this->_logger = $logger;
        $this->_collectionFactory = $collectionFactory;
        parent::__construct($context, $backendHelper, $data);
    }

    /**
     * {@inheritdoc}
     */
    protected function _prepareCollection()
    {
        $collection = $this->_collectionFactory->create();
        $this->_logger->info($collection->getSelect()->__toString());
        $this->setCollection($collection);
        return parent::_prepareCollection();
    }
}

And remember that the collection factory is a magic class that can attaches to every class as Magento 1 wasn't complicated enough.

Try following code.

 $products = Mage::getModel('catalog/product')
                ->getCollection();

    echo $products->getSelect();

$collection = "Sql Query Here";
echo $collection->getSelect()->__toString();

Sql Query Products Catalog

select e.entity_id, IF(at_name.value_id > 0, at_name.value, at_name_default.value) AS name, pcategory_name.value AS 'PCat', eaov.value AS 'brand', stoke.stock_status AS 'inStoke', pcategory_name.entity_id AS 'cat_id', ccei.value AS 'is_active' FROM catalog_product_entity AS e INNER JOIN catalog_product_entity_varchar AS at_name_default ON (at_name_default.entity_id = e.entity_id) AND (at_name_default.attribute_id = (SELECT attribute_id FROM eav_attribute ea LEFT JOIN eav_entity_type et ON ea.entity_type_id = et.entity_type_id WHERE ea.attribute_code = 'name' AND et.entity_type_code = 'catalog_product')) AND at_name_default.store_id = 0 LEFT JOIN catalog_product_entity_varchar AS at_name ON (at_name.entity_id = e.entity_id) AND (at_name.attribute_id = (SELECT attribute_id FROM eav_attribute ea LEFT JOIN eav_entity_type et ON ea.entity_type_id = et.entity_type_id WHERE ea.attribute_code = 'name' AND et.entity_type_code = 'catalog_product')) AND (at_name.store_id = 1) LEFT JOIN cataloginventory_stock_status AS stoke ON (stoke.product_id = e.entity_id) LEFT JOIN catalog_product_entity_varchar AS key ON (key.entity_id = e.entity_id) AND key.attribute_id = 160 INNER JOIN catalog_category_entity AS pcategory ON pcategory.entity_id = (SELECT MAX(category_id) FROM catalog_category_product WHERE product_id = e.entity_id AND category_id != 2) INNER JOIN catalog_category_entity_int AS ccei ON ccei.entity_id=pcategory.entity_id AND ccei.attribute_id=34 LEFT JOIN catalog_category_entity_varchar AS pcategory_name ON pcategory_name.entity_id = pcategory.entity_id AND pcategory_name.attribute_id = 33 LEFT JOIN catalog_product_entity_int AS cpei ON cpei.entity_id = e.entity_id AND cpei.entity_type_id = 4 AND cpei.attribute_id = 70 LEFT JOIN catalog_product_entity_int AS cpeis ON cpeis.entity_id = e.entity_id AND cpeis.attribute_id = 155 LEFT JOIN eav_attribute_option_value AS eaov ON eaov.option_id = cpei.value WHERE ccei.value=1 AND pcategory.parent_id=2