How can I get SQL query from repository?

Question

Lets asume that I have code like this:

$searchCriteria = $this->searchCriteriaBuilder
    ->addFilter('increment_id', $incrementId, 'eq')
    ->create();

$orderList = $this->orderRepository
    ->getList($searchCriteria);

Is it possible to see SQL query in a way like it was done in Magento 1 with model object:

echo Mage::getModel('module/model')->getCollection()->getSelect()->__toString();

Answer 1

You can simply do the following -:

print_r($orderList->getSelect()->assemble());

This will output the actual query with all where clauses and joins. Hope this helps!

Answer 2

I just stumbled about this question by trying to find out the same on v.2.2

Unfortunately the collection of a repository isn't public accessible from within objects using it. For me the best way - for now - was to use xdebug (https://xdebug.org/) and the corresponding watcher feature of my IDE (Intellj Ultimate => see https://www.jetbrains.com/idea/). In my case I had done some filtering on the cms page repository and wanted to see the query, when it is done. I did this by adding a brakpoint in \Magento\Cms\Model\PageRepository::getList (161). When the debugger stopped there, I was able to watch the query using the watcher by adding the following expression to the watchlist:

$collection->getSelect()->assemble()

That is the known Magento 1 style.

I hope this helps a little bit to get further. If someone wanted to know how to use xdebug with IntelliJ or PHPStorm, have a look at https://www.ask-sheldon.com/php-xdebug/.

Answer 3

I would say that is because the order repository getList method returns a different object that isn't a collection ( \Magento\Sales\Api\Data\OrderSearchResultInterface ). This object returns a collection of orders inside a wrapper, with other details such as page number, total count, as well as a collection called items.

You need to create a sales order collection and then you can do

$collection->getSelect()->_toString();

The order searchResultsInterface has a getItems method - which returns an array of order collection items:

https://github.com/magento/magento2/blob/2.2-develop/app/code/Magento/Sales/Api/Data/OrderItemSearchResultInterface.php

All of the collection processing is done in this class:

https://github.com/magento/magento2/blob/2.2-develop/lib/internal/Magento/Framework/Api/SearchCriteria/CollectionProcessor.php

You might want to test if you can log SQL from here.