Magento 2 – How to Print Collection MySQL Query

collection;magento2query

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

Best Answer

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

Related Solutions

Magento Collection addFieldToFilter – Complex AND/OR Query

You should not limit your start and end dates. Try this:

$collection = Mage::getModel('mymodule_events/details')->getCollection();
$collection->addFieldToFilter( 'status', '1' );
$collection->addFieldToFilter(
    'start_date',
    array(
        'lteq'=>$last_day
    )
);    
$collection->addFieldToFilter(
    'end_date',
    array(
        'gteq'=>$first_day
    )
);

So start_date should be lighter/equal $last_day and end_date should be greater/equal $first_day.

Magento – How to print sql query for order collection factory in magento 2

$this->_orderCollectionFactory is an factory class so you need to create this first.

$this->orders = $this->_orderCollectionFactory->create();
$this->orders->addFieldToSelect(
    '*'
)
->addFieldToFilter('customer_id', $customerId)
->addFieldToFilter(
    'status',
    ['in' => $this->_orderConfig->getVisibleOnFrontStatuses()]
)->setOrder(
    'created_at',
    'desc'
)->setPageSize(1)
    ->setCurPage(1)
    ->load();
$this->orders->printLogQuery(true);

[Update]

Replace following code:

protected $orders;

public function __construct(
    \Magento\Framework\View\Element\Template\Context $context,
    \Magento\Customer\Model\SessionFactory $customerSession,
    \Magento\Sales\Model\ResourceModel\Order\CollectionFactory $orderCollectionFactory,
    \Magento\Sales\Model\Order\Config $orderConfig,
    array $data = []
){
    $this->_orderCollectionFactory = $orderCollectionFactory;
    $this->_customerSession = $customerSession;
    $this->_orderConfig = $orderConfig;
    parent::__construct($context, $data);
}

public function getOrders()
{
    $customer = $this->_customerSession->create();
    $customerId = $customer->getCustomer()->getId();

    if (!($customerId)) {
        return false;
    }
    if (!$this->orders) {
        $this->orders = $this->_orderCollectionFactory->create();
        $this->orders->addFieldToSelect(
            '*'
        )
            ->addFieldToFilter('customer_id', $customerId)
            ->addFieldToFilter(
                'status',
                ['in' => $this->_orderConfig->getVisibleOnFrontStatuses()]
            )->setOrder(
                'created_at',
                'desc'
            )->setPageSize(1)
            ->setCurPage(1)
            ->load();
        $this->orders->printLogQuery(true);
    }

    return $this->orders;
}

Make sure you are login user and clear cache.

Best Answer

Related Solutions

Magento Collection addFieldToFilter – Complex AND/OR Query

Magento – How to print sql query for order collection factory in magento 2

Related Topic