Direct SQL Query in Magento 2

There are many situations where executing direct raw SQL queries would be simple and much quicker leading to a more optimized Magento get collection query as a performance basis.

On a large data set of an entity, Saving each individual entity can take a long time with resource-hungry and therefore make the system unusable.

Overcome this issue it is possible to issue a direct SQL query that could update a large set of data of an entity in fewer seconds.

When you use Direct SQL Query you don’t need to worry about Model/Factory Pattern.

By default, Magento will automatically connect to its database using the class Magento\Framework\App\ResourceConnection with the getConnection() function.

Code snippet to fetch database table name with a prefix,

<?php
namespace Path\To\ClassDirectory;

use Magento\Framework\App\ResourceConnection;

class DirectSqlQuery {

    private ResourceConnection $resourceConnection;

    public function __construct(
        ResourceConnection $resourceConnection
    ) {
        $this->resourceConnection = $resourceConnection;
    }

    /**
     * Get Table name using direct query
     * First Argument is $tableName. Like 'catalog_product_entity'
     */
    public function getTablename($tableName): string
    {
        $connection  = $this->resourceConnection->getConnection();
        return $connection->getTableName($tableName);
    }
}

You need to use the $this->resourceConnection object to run any Direct SQL Query.

Reading all the records from the Database,

fetchAll() => This method Fetches all SQL result rows as a sequential array.
This method takes a query as its first parameter executes it, and then returns all of the results as an array.

By using the fetchAll method, we can get all the records of a database table.

<?php
$tableName = $this->getTableName('customer_entity');
$query = 'SELECT * FROM ' . $tableName;

/**
 * Execute the query and fetch All the records from the table.
 */
$results = $this->resourceConnection->getConnection()->fetchAll($query);
echo "<pre>";print_r($results);

The result will be all the records of a customer entity as an array.

fetchCol() => This method fetches the given only column from the table with rows as an array.

<?php
$tableName = $this->getTableName('customer_entity');
$query = 'SELECT email FROM ' . $tableName;

/**
 * Execute the query and fetch first email column only.
 */
$results = $this->resourceConnection->getConnection()->fetchCol($query);

echo "<pre>";print_r($results);

The result looks like this,

[0] => Array
    (
        [email] => abc@abcdz.com
    )
[1] => Array
    (
        [email] => helloworld@testtest.com
    )
    ....

fetchOne() => This method Fetches the first column of the first row of the SQL result.

<?php

$tableName = $this->getTableName('customer_entity');
$query = 'SELECT email FROM ' . $tableName;
	
$results = $this->resourceConnection->getConnection()->fetchOne($query);
echo $results; // result will be just value

Output: abc@abcdz.com

Using fetchOne you can get only a single result value. In the above query, you can get an email id as a result.

Update Query: Update Records in a Database table that already exists.

$tableName = $this->getTableName('customer_entity');
$sql = "UPDATE $tableName SET `suffix` = 'Mr' WHERE $tableName.`entity_id` = 1";

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

Delete Query: Delete a specific row from a database table.

$tableName = $this->getTableName('customer_entity');
$sql = "DELETE FROM $tableName WHERE `entity_id` = 1";
$this->resourceConnection->getConnection()->query($sqls);

Insert Query: Insert a single record in the database table using the row query.

The first Parameter is a table name
In the given example, We will add a record to the review table.

$tableName = $block->getTableName('review');
$newdate = date("Y-m-d H:i:s");
$review = [
            'created_at' =>$newdate,
            'entity_id' => 1,
            'entity_pk_value' => 1,
            'status_id' => 2
        ];
$this->resourceConnection->getConnection()->insert($tableName, $review);

New records will be inserted in a review table.

insertArray Query: Insert array into a table based on the column defined in array data.

Pass only those columns in which the value you want to insert in the second parameter column and the third parameter is data of the specific column.

In the below example, We will add the record to a review table.

$tableName = $block->getTableName('review');
$newdate = date("Y-m-d H:i:s");
$reviewColumn = ['entity_id','entity_pk_value','status_id'];
$reviewData[] = [$newdate,1,4,2];

$this->resourceConnection->getConnection()->insertArray($tableName, $reviewColumn,$reviewData);

The above query inserts the new record in the table column-wise.

There are many other row query functions related to the database. You can get all the SQL functions from the file Magento/framework/DB/Adapter/AdapterInterface.php

You can explore many direct SQL queries which will be available in other articles on this site.

Share this:

Related