How to print collection mysql query in magento

Go To StackoverFlow.com

11

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?

2012-04-04 16:34
by Luca Borrione
This is the correct answer, right here. echo $collection->getSelectSql(true);Shawn Abramson 2016-05-15 07:11
Correction to my original comment. It should be echo $collection->load()->getSelectSql(true)Shawn Abramson 2016-08-10 01:54


20

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'));
2012-04-04 19:19
by Nick McCormack
The problem with your answer Nick is that when you start adding attributes or custom columns or other things, echoing $collection->getSelect() does not take those additions into account. You have to load the collection first like this echo $collection->load()->getSelect(). Its cleaner to use echo $collection->getSelectSql(true)Shawn Abramson 2016-05-15 07:13
@ShawnAbramson, it still doesn't include other attributes from the query - user1240207 2016-08-10 01:51
@user1240207 - yes, i just realized you need to call ->load() before running $collection->getSelectSql(true). The attributes get added to the query when the collection is loaded - Shawn Abramson 2016-08-10 01:53
@ShawnAbramson, hmm, that's odd. I've made a few tests, $collection = Mage::getModel('catalog/product')->getCollection()->addAttributeToSelect('price')->addAttributeToSelect('cost')->addAttributeToSelect('specialprice')->addAttributeToFilter('specialprice', array('notnull' => true))->addAttributeToFilter('specialprice', array('neq' => '')); $collection->load(); printr($collection->getSelectSql(true)."\n"); Only the special_price made it to the query - user1240207 2016-08-10 01:57
@user1240207 I just ran a little test. You either need to use $collection->addAttributeToSelect('*') or $collection->joinAttribute( 'price', 'catalogproduct/price', 'entityid', null, 'left', $storeId ) - Shawn Abramson 2016-08-10 02:14


12

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.

2012-08-29 05:22
by Kul


5

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.

2014-12-09 09:12
by Doug McLean


2

You can print

$products->getSelect()->assemble();
2012-04-04 16:35
by Luca Borrione
I don't even think you need the assemble(), just echo $products->getSelect();Max 2012-04-04 16:44


2

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

$products = Mage::getModel('catalog/product')
            ->getCollection()
            ...
           ->load(true);
2015-03-17 17:46
by wesleywmd


2

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

echo $collection->getSelectSql(true);

2016-05-15 07:09
by Shawn Abramson


0

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 .

2015-05-28 17:21
by P. Prakash


0

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.

2016-05-15 04:54
by Dallas Clarke


0

Try following code.

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

    echo $products->getSelect();
2016-05-16 05:14
by Sagar U


-3

$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

2015-06-17 06:16
by sunil rana
Could you please elaborate more your answer adding a little more description about the solution you provide - abarisone 2015-06-17 06:35
This reminds me of the proverb "give a man a fish, feed him for a day; teach him to fish; feed him for life." The asker wants to know how to fish and you're just giving them a fish. Plus, this code isn't portable. It's using attribute IDs and category IDs that are specific to your system. And the formatting, dude... If you'd just indented each line by 4 spaces the above would be much more readable and would still contain backticks in all the right places - Doug McLean 2016-02-08 09:49
Ads