CODEWITHSUNDEEP
debuggingmagento
Luca Borrione
Luca Borrione

Reputation: 17002

How to print collection mysql query in magento

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?

Upvotes: 13

Views: 67984

Answers (9)

samumaretiya
samumaretiya

Reputation: 1175

Try following code. 

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

    echo $products->getSelect();

Upvotes: 0

Shawn Abramson
Shawn Abramson

Reputation: 701

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

echo $collection->getSelectSql(true);

Upvotes: 3

Dallas Clarke
Dallas Clarke

Reputation: 269

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.

Upvotes: 1

Kul
Kul

Reputation: 378

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.

Upvotes: 14

P. Prakash
P. Prakash

Reputation: 1

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 .

Upvotes: 0

Doug McLean
Doug McLean

Reputation: 1309

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.

Upvotes: 7

wesleywmd
wesleywmd

Reputation: 605

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

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

Upvotes: 2

Nick McCormack
Nick McCormack

Reputation: 530

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

Upvotes: 22

Luca Borrione
Luca Borrione

Reputation: 17002

You can print

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

Upvotes: 3

Related Questions