Custom SQL Query in Magento 2 With Standard Way

In this tutorial, “Custom SQL Query in Magento 2 With Standard Way,” I will guide you on adopting a standardized approach to writing SQL queries in Magento 2. Developers often need to manipulate data using SQL queries. However, embedding these queries directly into the code is considered improper and can pose security.

Let me elucidate how to write an SQL query in a standard manner. Suppose you want to execute the following query:

SELECT * FROM your_table WHERE condition = 'some_value';

It is not recommended to use direct sql query. I would suggest to use below standardize format.

<?php
/**
 * @Module         : VendorName ModuleName
 * @Package        : VendorName_ModuleName
 * @Description    : Testing custom script output
 * @Developer      : Jyotiranjan Biswal<biswal@jyotiranjan.in>
 * @Copyright      : https://www.jyotiranjan.in/
 */

namespace VendorName\ModuleName\Model;

use Magento\Framework\Model\AbstractModel;
use Magento\Framework\App\ResourceConnection;

class YourClass extends AbstractModel
{

    protected $logger;

    /**
     * Resource
     *
     * @var ResourceConnection
     */
    protected $resourceConnection;

    /**
     * Constructor
     *
     * @param \Psr\Log\LoggerInterface $logger
     */
    public function __construct(
        \Psr\Log\LoggerInterface $logger,
        ResourceConnection $resourceConnection,
    )
    {
        $this->logger = $logger;
        $this->resourceConnection = $resourceConnection;
    }

    /**
     * Execute the custom script
     *
     * @return void
     */
    public function execute()
    {
        $storeId = 1;
        $customerData = $this->getAllRecords($storeId);

        print_r($customerData);
    }

    public function getAllRecords($id){
        $connection = $this->resourceConnection->getConnection();
        $tableName = $this->resourceConnection->getTableName('your_table');

        $bind = ['store_id' => $id];
        $query = $connection
            ->select()
            ->from(
                ['main_table' => $tableName],
                ['*']
            )
            ->where($connection->quoteInto('column_name = ?', 'some_value'));

        return $connection->fetchAll($query, $bind);
    }
}

Above query I have mentioned the * for all records, if you would like to specific column then you can add simply columns names with comma separated like below.

    public function getAllRecords($id){
        $connection = $this->resourceConnection->getConnection();
        $tableName = $this->resourceConnection->getTableName('your_table');

        $bind = ['store_id' => $id];
        $query = $connection
            ->select()
            ->from(
                ['main_table' => $tableName],
                ['column_one','column_two']
            )
            ->where($connection->quoteInto('column_name = ?', 'some_value'));

        return $connection->fetchAll($query, $bind);
    }

This format ensures a standardized and secure method for managing SQL queries. Let’s consider examples for select, update, and delete operations.

<?php
/**
 * @Module         : Jrb Demomodule
 * @Package        : Jrb_Demomodule
 * @Description    : Testing custom script output
 * @Developer      : Jyotiranjan Biswal<biswal@jyotiranjan.in>
 * @Copyright      : https://www.jyotiranjan.in/
 */

namespace Jrb\DemoModule\Model;

use Magento\Framework\Model\AbstractModel;
use Magento\Framework\App\ResourceConnection;

class CustomScript extends AbstractModel
{

    protected $logger;

    /**
     * Resource
     *
     * @var ResourceConnection
     */
    protected $resourceConnection;

    /**
     * Constructor
     *
     * @param \Psr\Log\LoggerInterface $logger
     */
    public function __construct(
        \Psr\Log\LoggerInterface $logger,
        ResourceConnection $resourceConnection,
    )
    {
        $this->logger = $logger;
        $this->resourceConnection = $resourceConnection;
    }

    /**
     * Execute the custom script
     *
     * @return void
     */
    public function execute()
    {
        // get the list of record having store id 1
        $storeId = 1;
        $customerData = $this->getAllRecords($storeId);
        print_r($customerData);
        $this->logger->info('customer_data',$customerData);

        // update customer data
        $customerId = 2;
        $data = ['firstname' => 'Jyoti', 'lastname' => 'Mishra', 'is_active' => 0];
        $this->updateRecordById($customerId,$data);
        
        // delete customer data by id 
        $customerId = 3;
        $this->deleteRecordById($customerId);
    }

    /**
     * @param int $id
     * 
     * @return array
     */
    public function getAllRecords($id){
        $connection = $this->resourceConnection->getConnection();
        $tableName = $this->resourceConnection->getTableName('customer_entity');

        $bind = ['store_id' => $id];
        $query = $connection
            ->select()
            ->from(
                ['main_table' => $tableName],
                ['entity_id','email','firstname']
            )
            ->where('store_id = :store_id');

        return $connection->fetchAll($query, $bind);
    }

    /**
     * @param int $id
     * @param array $data
     * 
     * @return void
     */
    public function updateRecordById($id, $data){
        $connection = $this->resourceConnection->getConnection();
        $tableName = $this->resourceConnection->getTableName('customer_entity');
        $condition = $connection->quoteInto('entity_id = ?', $id);
        $connection->update(
            $tableName,
            $data,
            $condition
        );
    }

    /**
     * @param int $id
     * 
     * @return void
     */
    public function deleteRecordById($id){
        $connection = $this->resourceConnection->getConnection();
        $tableName = $this->resourceConnection->getTableName('customer_entity');
        $condition = $connection->quoteInto('entity_id = ?', $id);
        $connection->delete(
            $tableName,
            $condition
        );
    }
}

You can also refer the some example from magento2 core files like Magento\Framework\DB\Tree::getChildren($nodeId, $startLevel = 0, $endLevel = 0)

If you would like to get the complete sample module then download from here

I believe that this article, titled “Custom SQL Query in Magento 2 With Standard Way,” simplifies the understanding of crafting SQL queries in Magento 2 through the standard approach. If I missed details or if you have additional information to contribute. I’ll promptly respond with the appropriate solution.

Keep appreciating and sharing!

Related posts:

How to use fetchOne() method with Sql query in Magento 2 Write a fetchCol() Sql query in Magento 2 How to use fetchPairs method in Magento 2 How to write a fetchRow method of MySql query in Magento 2