How To Create a Custom Query in Magento 2?

May 4, 2022

Magento

Let’s see how to run a custom query in Magento 2.

Magento 2 model is not enough to get data from database as per our requirements. So we need to use custom query to fulfill this requirement. Below in example, we will learn how to use custom select query, custom insert query, custom update query and custom delete query.

So let’s start with an example.

<?php
namespace Mageants\Blog\Helper;
use Magento\Framework\App\Helper\AbstractHelper;
use Magento\Framework\App\Helper\Context;
use Magento\Framework\App\ResourceConnection;
class Data extends AbstractHelper
{
protected $resourceConnection;
public function __construct(Context $context,
ResourceConnection $resourceConnection)
{
$this->resourceConnection = $resourceConnection;
parent::__construct($context);
}
public function customSqlQuery($table)
{
//Create Connection
$connection = $this->resourceConnection->getConnection();
// get table name
$table = $connection->getTableName(‘custom_table’);
// Select query
$selectquery = “SELECT * FROM ” . $table;
$result = $connection->fetchAll($selectquery);
echo ”; print_r($result); echo ”;

In the above code, when we run the select query and use `fetchAll()` function, the following output is given. It returns a multidimensional array with table field name and field value.

Array
(
[0] => Array
(
[id] => 1
[name] => rock
[email]=> rock@example.com
)
[1] => Array
(
[id] => 2
[name] => mageants
[email] => mageants@example.com
)
)

// Insert query
$columnfields = [‘id’, ‘name’, ’email’];
$fieldsdata[] = [3, ‘mageants’, ‘mageants@gmail.com’];
$connection->insertArray($table, $columnfields, $fieldsdata);
$insertquery = “INSERT INTO ” . $table . “(‘id’, ‘name’,’email’)VALUES
( 5,’rock’, ’rock@gmail.com’)”;
$connection->query($insertquery);

// Update query
$id = 1;
$updatequery = “UPDATE ” . $table . ” SET name= ‘test’
WHERE id = $id “;
$connection->query($updatequery);
$updatequery1= “UPDATE ” . $table . ” SET name= ‘test’,
email=‘test@gmail.com’
WHERE id = $id “;
$connection->query($updatequery1);

// Delete query
$id = 1;
$deletequery = “DELETE FROM ” . $table . ” WHERE id = $id “;
$connection->query($deletequery);
}
}

Conclusion :

We hope the above blog helps you to clearly understand how we can create custom query in Magento 2. In case of any kind of problem with the above code implementation, you can contact us or let us know in comment section below.

Also, Learn: How to Get Product Salable Quantity in Magento 2?

Create CMS Page in Magento 2 Programmatically

What is schema patch in Magento 2 and How to Apply that?