how to get mysql query of collection for fetching report details - magento 2

Question

Agenda: convert mysql query result to json then I can visualize by using fluentd + elasticsearch + kibana.

I have successfully visualize sample mysql query.

I am looking mysql query for getting below reports.

1. Last Orders
2. Last Search Terms
3. Top Search Terms
4. Best Seller
5. Most Viewed Products
6. Customer Count
7. Visitor Count Based on date
8. Admin -> Report -> I need all reports from here

is there any shortcut to find mysql query of above all? or is there any cheet sheet available?

Answer 1

It is achieve from mysql query logging

in <magento-root>\app\etc\di.xml change below line

<preference for="Magento\Framework\DB\LoggerInterface" type="Magento\Framework\DB\Logger\Quiet"/>

to

<preference for="Magento\Framework\DB\LoggerInterface" type="Magento\Framework\DB\Logger\File"/>
<type name="Magento\Framework\DB\Logger\File">
    <arguments>
        <argument name="logAllQueries" xsi:type="boolean">true</argument>
        <argument name="debugFile" xsi:type="string">query.log</argument>
    </arguments>
</type>

then run

php bin/magento setup:upgrade

check <magento-root>\var\query.log. Whenever we load page, collections of that pages are printed in sql query format in query.log file.

Full credits goes this link author @Gihan Kavinga

If any way is better than this, please post your answer.