21

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());
3
  • 1
    Please post the full code you're testing printLogQuery with Mar 28, 2017 at 10:55
  • Thanks @RaphaelatDigitalPianism for quick comment. I have revise question with code.
    – Kul
    Mar 28, 2017 at 10:59
  • 1
    you can try with $this->_collection->getSelect(); Mar 28, 2017 at 11:15

7 Answers 7

57

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

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

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

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();
1
  • Perfect. I haven't same for M2. its work perfect!
    – Kul
    Mar 28, 2017 at 11:22
6

Or, simply use

echo $collection->getSelect()
2

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

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.

0

Or, simply you can use

echo $collection->getSelectSql(true)
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.