14

There is getSelect()->__toString(); is available in Magento 1 for print query of collection. like below example

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

Is there any method available in magento 2? I have found this ->printLogQuery(true); but doesn't work for me.

Update: below is the code. I am trying to get bestseller products. its work perfect but i want to print query for debug.

$this->_collection->getSelect()
                  ->joinLeft(
                'sales_order_item',
                'e.entity_id = sales_order_item.product_id',
                array('qty_ordered'=>'SUM(sales_order_item.qty_ordered)')) 
                ->group('e.entity_id') 
                ->order('qty_ordered '.$this->getCurrentDirectionReverse());
  • 1
    Please post the full code you're testing printLogQuery with – Raphael at Digital Pianism Mar 28 '17 at 10:55
  • Thanks @RaphaelatDigitalPianism for quick comment. I have revise question with code. – Kul Mar 28 '17 at 10:59
  • 1
    you can try with $this->_collection->getSelect(); – Rakesh Jesadiya Mar 28 '17 at 11:15
33

The answers above are correct, but some collections only assemble the select in the _beforeLoad() method, as opposed to initialising it in the constructor. This means you'll get an empty string if you try to output the SQL query before loading the collection.

An example of this is \Magento\Sales\Model\ResourceModel\Report\Bestsellers\Collection. So if you're getting unexpected results then load the collection (this will build the final select query) and then output the query.

$collection->load();

// the following statements are equivalent
$collection->getSelect()->assemble();
$collection->getSelect()->__toString();
echo $collection->getSelect(); // will call magic method __toString() behind the scenes which in turn calls assemble()
11

You can use same as magento 1 to print query in magento 2.

$collection = $this->_collection->getSelect()
                      ->joinLeft(
                    'sales_order_item',
                    'e.entity_id = sales_order_item.product_id',
                    array('qty_ordered'=>'SUM(sales_order_item.qty_ordered)')) 
                    ->group('e.entity_id') 
                    ->order('qty_ordered '.$this->getCurrentDirectionReverse());

$collection->getSelect()->__toString();
  • Perfect. I haven't same for M2. its work perfect! – Kul Mar 28 '17 at 11:22
7

You can use __toString() function to print query in Magento 2

$collection = "Your Query";

echo $collection->getSelect()->__toString();
2

Or, simply use

echo $collection->getSelect()

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.