Magento2 Custom SQL Query

Sometimes we need to run custom SQL Query in Magento to fulfill our need which takes much time if we use the Model and Collection approach.

So in Today’s blog, I will explain how we can use Custom SQL Query in Magento for Select, Insert, Update and Delete easily

Magento\Framework\DB\Adapter\AdapterInterface this interface contain all the function which we can use to run custom Query in Magento and also contain some extra function like insert insertMultiple(), updateFromSelect().

To use the all function which is defined in AdapterInterface we will use Magento\Framework\App\ResourceConnection class.

For this blog I will use ObjectManger to get the Instance of the ResourceConnection, But it is not recommended by Magento to use ObjectManger Directly. So you have to add this class as a dependency of Block, Controller, Helper and after that, you can use ResourceConnectionclass Object

1. Select
2. Insert
3. Update
4. Delete

$objectManager = \Magento\Framework\App\ObjectManager::getInstance(); 
$resource = $objectManager->get('Magento\Framework\App\ResourceConnection'); 
$blogTable = $resource->getTableName('rk_blog_table');

1. Select

$id = 2;
$select = $connection->select()
->from($blogTable)
->where('id = ? ', $id);
$blogData = $connection->fetchAll($blogQuery);

You can pass optional parameter in from() to get only specific fileds.

$select = $connection->select()
->from($blogTable, ['title', 'status'])
->where('id = ? ', $id);

There is one other way to prepare this statement. You can directly write SQL query without any function and pass into the fetchAll() 

$selectQry = "Select * from rk_blog_table where id=$id";
$qryData = $connection->fetchAll($selectQry);

2. Insert

$data = ['status' => 1, 'title' => 'Magento2 Custom SQL Query', 'content' => 'This is blog content'];
$id = $connection->insert($blogTable, $data);

Or you can also use insert query like below.

$insert = "INSERT INTO " . $blogTable . "(status, title, content) values (1, 'Magento2 Custom SQL Query for Insert', 'This is blog content')";
$connection->query($insert);

3. Update

$id=4;
$where = ['id = ?' =>$id];
$data = ['status' =>0];
$connection->update($blogTable,$data,$where);

Or

$update = "UPDATE " . $blogTable . " SET status=0 where id=5";
$connection->query($update);

4. Delete

$id=7;
$where = ['id = ?' =>$id];

$connection->delete($blogTable,$where);

Or

$delete = "DELETE FROM " . $blogTable . " where id=8";
$connection->query($delete);

Hope you find this Tutorial is helpful. Do comment if you face any issue or Contact me If you want any help or customization in your existing Project or Extension.