How to print collection mysql query in magento

Question

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?

Answer 1

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.