Hello, passionate developers of the e-commerce world! 

 

Today, we're going to delve into the world of Magento 2, focusing on the execution of direct raw SQL queries. Prepare yourself to unearth a reliable, optimized Magento get collection query technique that promises faster, uncomplicated data updating.

 

Why Opt for Direct SQL Queries? At times, having to save each entity in a large data set can become remarkably time-consuming; having to deal with resource-hungry processes that also reduce your system’s usability. To navigate this issue, we can opt to issue a direct SQL query that enables us to update a vast set of entity data swiftly, hence enhancing performance. With Direct SQL queries, the requirement for Model/Factory Pattern is eliminated, saving you some precious coding time.

 

Establishing a Connection: By default, Magento establishes an automatic connection to its database using the Magento\Framework\App\ResourceConnection class and the getConnection() function. You'll need to use the $this->resourceConnection object to execute any Direct SQL query.

 

Fetching Database Table Name: It's essential to fetch the name of the database table you wish to work with, including any prefix. With the aid of the Resource Connection object and the getTableName() method, we can directly query the table name. This can be done using the following code snippet:

 

namespace Path\To\ClassDirectory; 

use Magento\Framework\App\ResourceConnection; 

 

class DirectSqlQuery { 

    private ResourceConnection $resourceConnection; 

 

    public function __construct( ResourceConnection $resourceConnection ) { 

        $this->resourceConnection = $resourceConnection; 

    } 

 

    public function getTablename($tableName): string { 

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

        return $connection->getTableName($tableName); 

    } 

}

 

Different Types of Direct SQL Queries: Magento 2 supports several types of Direct SQL Queries:

 

  1. Select Query
  2. InsertMultiple SQL Query
  3. Fetch Pairs SQL Query
  4. FetchAssoc SQL Query
  5. FetchRow SQL Query
  6. InsertOnDuplicate SQL Query
  7. quoteInto() SQL Query
  8. Prefix table name SQL Query
  9. Left Join Query

Reading Records from Database: fetchAll() is a handy method that fetches all SQL result rows as a sequential array. With fetchAll, you can retrieve all the records of a database table using the following snippet:

 

$tableName = $this->getTableName('customer_entity');

$query = 'SELECT * FROM ' . $tableName; 

$results = $this->resourceConnection->getConnection()->fetchAll($query);

echo "<pre>";

print_r($results);

 

Running this will yield all the records of 'customer_entity' as an array.

Updating the Database: We can also update records using an Update Query. This can be done like so:

 

$tableName = $this->getTableName('customer_entity');

$sql = "UPDATE $tableName SET `suffix` = 'Mr' WHERE $tableName.`entity_id` = 1"; 

$this->resourceConnection->getConnection()->query($sql);

 

This updates the suffix of a specific entity to 'Mr.'

Deleting a Record: You can execute the deletion of specific rows from the database by using a Delete Query, as below:

 

$tableName = $this->getTableName('customer_entity');

$sql = "DELETE FROM $tableName WHERE `entity_id` = 1";

$this->resourceConnection->getConnection()->query($sql;

 

This enables a deletion of the entity found at 'entity_id' = 1.

In conclusion, deploying Direct SQL Queries offers a streamlined approach to database management tasks in Magento 2, providing a faster and less resource-intensive solution. Hopefully, this walkthrough has illuminated how to implement Direct SQL Queries. 

 

Happy Magento 2 coding!