Magento 2 Core read write Query

Magento 2 Core read write Query– We sometimes need to run core queries in Magento 2 to perform some specific task. To run custom query we can use object manager or dependency injection. Here in this tutorial, we are going to cover the both method to run the raw query.

Magento 2 Core read write Query | Run Direct SQL Example

Magento 2 allows us to run the core query which can be used to read or write data. –

Method 1

Import resource(\Magento\Framework\App\ResourceConnection) in class simply as below –

protected  _resource;
public function __construct(Context $context,
\Magento\Framework\App\ResourceConnection $resource)
  {
    $this->_resource = $resource;
    parent::__construct($context);

  }

Get resource connection-

$connection = $this->_resource->getConnection(\Magento\Framework\App\ResourceConnection::DEFAULT_CONNECTION);

Now run your custom query-

Read Query

Fetch All Result

You can select all data from table using custom query simply as below-

$connection = $this->_resource->getConnection(\Magento\Framework\App\ResourceConnection::DEFAULT_CONNECTION)
$orderTable = $connection->getTableName('sales_order');
$result1 = $connection->fetchAll('SELECT * FROM '.$orderTable.' WHERE entity_id='.$orderId);

Run raw Query– If you want to run the raw query you can run it simply as below-

$connection = $this->_resource->getConnection(\Magento\Framework\App\ResourceConnection::DEFAULT_CONNECTION)
$result1 = $connection->query('SELECT * from users where user_id = 100');

Write Query

You can insert/update/delete the data using core query simply as below-

Insert

$connection = $this->_resource->getConnection(\Magento\Framework\App\ResourceConnection::DEFAULT_CONNECTION)
 $connection->query('INSERT into users(1, 'test user', 'test@yopmail.com')');

Update

$connection = $this->_resource->getConnection(\Magento\Framework\App\ResourceConnection::DEFAULT_CONNECTION)
 $connection->query(' UPDATE users set name="Jhon" where id="10"');

Delete

$connection = $this->_resource->getConnection(\Magento\Framework\App\ResourceConnection::DEFAULT_CONNECTION)
 $connection->query(' DELETE from users where id="10"');

Method 2- Using Object Manager

Using Object Manager You can run custom query simply as below-

$objectManager = \Magento\Framework\App\ObjectManager::getInstance(); 
$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();
$table = $resource->getTableName('users');
 
//Select Query Example
$result = $connection->fetchAll("Select * FROM " . $table); // returns array.
//Insert Query
$connection->query("Insert Into " . $table . " (name, email, phone) values ('Jhon','jhon@example.com','123123123')");
//Update Query
$sql = "Update " . $table . "Set email = 'abc@example.com' where id = 19";
$connection->query($sql);
//Delete Query Example
$connection->query("Delete FROM " . $table." Where id = 99");

On the same way you can perform read/write ie you can run direct sql query using any one method in Magento 2.

Add Comment