How to print sql query in Magento 2?

@raven_corwin 

To print an SQL query in Magento 2, you can use the following methods:

1. Use the Zend_Db_Profiler class:

1
2
3
4
5
6
7

$profiler = Mage::getSingleton('core/resource')->getConnection('core_read')->getProfiler();
$profiler->setEnabled(true);

// Perform the SQL query

$query = $profiler->getLastQueryProfile();
echo $query->getQuery();

1. Use the Varien_Db_Adapter_Pdo_Mysql class:

1
2
3

$connection = Mage::getSingleton('core/resource')->getConnection('core_read');
$sql = $connection->select()->from('table_name', array('*'));
echo $sql->__toString();

1. Use the Mage::log() function:

1
2
3

$collection = Mage::getModel('catalog/product')->getCollection();
$sql = $collection->getSelect();
Mage::log($sql->__toString());

Note that the Mage::log() function will write the SQL query to the Magento log file, which is typically located at var/log/system.log.

I hope this helps! Let me know if you have any questions.

@raven_corwin 

To print an SQL query in Magento 2, you can follow these steps:

1. Open the file where you want to print the SQL query. It can be a model, a controller, or any other PHP file within your Magento module.
2. Inject the MagentoFrameworkAppResourceConnection class in the constructor of your class like this:

1
2
3
4
5
6
7
8

protected $resourceConnection;

public function __construct(
    MagentoFrameworkAppResourceConnection $resourceConnection
) {
    $this->resourceConnection = $resourceConnection;
    parent::__construct($context);
}

1. Use the getConnection() method of the $resourceConnection object to get the database connection. Then, create a new instance of MagentoFrameworkDBAdapterAdapterInterface class like this:

1

$connection = $this->resourceConnection->getConnection();

1. Build your SQL query using the appropriate methods of $connection. For example:

1
2
3
4

$query = $connection->select()
    ->from($tableName, ['column1', 'column2'])
    ->where('column1 = ?', $variableValue)
    ->order('column2', 'ASC');

1. Echo the query to print it:

1

echo $query->__toString();

1. Execute the query and fetch the results if needed:

1

$results = $connection->fetchAll($query);

Remember to remove or comment out the echo statement after you are done debugging, as printing the SQL query in a production environment can expose sensitive information.

That's it! You can now print the SQL query in Magento 2 to debug or analyze your database queries.

@raven_corwin Try this free module. https://github.com/Freento/sqllog. 

It makes it simple to view and analyze queries from the admin, and you can set rules for specific pages, requests, or commands.