How to Write SQL Query in Magento 2 With Standard Way

In this tutorial, Today I will explain to you how to write SQL Query in Magento 2 with standard way. There are many times we need to require to perform operation on data by SQL Query. But, the developers are write direct that sql query in their code which is not proper way. It will caused security issue as well.

You may also like this :

Magento 2 : Generate Product URL Rewrites Programmatically

Here, I am explain to you how to write sql query with standard way. For example, you want to execute this below query :

Copy Code


SELECT * FROM `catalog_product_entity` WHERE `sku` = '24-MB01’ AND `entity_id` = 1

Instead of this above query direct injected into code, You need to use this below query to follow standard way as well :

Copy Code


<?php
/**
 * Created By : Rohan Hapani
 */
namespace RH\Helloworld\Model\ResourceModel;

use Magento\Framework\App\ResourceConnection;

class FetchProductData
{
    private $resourceConnection;

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

    /**
     * Select query to fetch product data
     *
     * @return array
     */
    public function selectQuery()
    {
        $tableName = $this->resourceConnection->getTableName('catalog_product_entity');
        $connection = $this->resourceConnection->getConnection();
        $sku = '24-MB01';
        $entityId = 1;

        $select = $connection->select()
            ->from(
                ['c' => $tableName],
                ['*']
            )
            ->where(
                "c.sku = :sku"
            )->where(
                "c.entity_id = :entity_id"
            );
        $bind = ['sku' => $sku, 'entity_id'=> $entityId];

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

In the above query, [‘*’] means Fetching all the fields of the table. If you want to fetch specific field then, you just need to add fields with comma separated.

Copy Code


->where(
    "c.sku = :sku"
)->where(
    "c.entity_id = :entity_id"
);

$bind = ['sku' => $sku, 'entity_id'=> $entityId];

Here, $sku and $entityId Value to the bind array instead of directly passing the value to the given where conditions.

So, You can use this type of format to follow standard way with SQL query.

That’s it !!!

I hope this blog is easy to understand about how to write SQL Query in Magento 2 with standard way. In case, I missed anything or need to add some information, always feel free to leave a comment in this blog, I’ll get back with proper solution.

Keep liking and sharing !!

Post Views: 2,870