How to print collection mysql query in magento 2?

Question

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 the best seller products. It works perfect but I want to print a query for debugging.

$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());

Answer 1

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()

Answer 2

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

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

Answer 3

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

Answer 4

Or, simply use

echo $collection->getSelect()

Answer 5

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

Answer 6

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.

Answer 7

Or, simply you can use

echo $collection->getSelectSql(true)