15

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
37

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()
|improve this answer|||||
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();
|improve this answer|||||
  • Perfect. I haven't same for M2. its work perfect! – Kul Mar 28 '17 at 11:22
8

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

$collection = "Your Query";

echo $collection->getSelect()->__toString();
|improve this answer|||||
3

Or, simply use

echo $collection->getSelect()
|improve this answer|||||
1

i hope you want to print query to identify the issue of getsize count wrong. whenever you use group by query then call getSize function means it will give first record data so it will give wrong count. for this before embed group by query. you should call getSize function. so it will set before execute group by query.

|improve this answer|||||
1

var_dump($collection->getSelect()->__toString());

|improve this answer|||||

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.