How to print sql query in Magento 2?

by raven_corwin , in category: PHP CMS , 2 years ago

How to print sql query in Magento 2?

Facebook Twitter LinkedIn Telegram Whatsapp

3 answers

by herminia_bruen , a year ago

@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.

Member

by lottie , 9 months ago

@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.

D

Member

by Dan , 4 months ago

@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.