Magento 2 use custom query

Today we will discuss how in Magento 2 custom query works. Sometime magento 2 model is not enough to get data from database as per our requirements. So to fulfill this requirement we use custom query. In this example we will use select custom query, custom delete query, custom insert query and custom update query. So let’s start with example.

Get connection

First of all we will establish a connection to execute the query.

$this->_resources = \Magento\Framework\App\ObjectManager::getInstance()->get('Magento\Framework\App\ResourceConnection');
$connection= $this->_resources->getConnection();

Insert Query

It is simple to use insert query just set the values for columns. Use the inset method.

$array=array(‘column1’=>'column1','column2'=>'column2');

$connection->insert($this->_resources->getTableName(test_table), $array);

Select Query

Selecting table data in custom query you can use following code.

$select = $connection->select()->from(['o' => $this->_resources->getTableName(test_table)]);
$result = $connection->fetchAll($select);

foreach ($result as $data) {
echo $data['id'];
}

Update Query

Updating table data in custom query you can use following code.

$id = 1;
$sql = "Update " . $this->_resources->getTableName('test_table') . " Set `title` ='test title' where `id` = ".$id;
$connection->query($sql);

Delete Query

To delete table data in custom query you can use following code.

$id = 1;
$condition = $this->getConnection()->quoteInto('id = ?', $id);
$this->getConnection()->delete($this->getTable('test_table'), $condition);

Join Table

To join table in custom query you can use following code.

$select = $connection->select()->from(['o' => $this->_resources->getTableName('test_table')])->joinleft(
['test_table1' => $this->_resources->getTableName('test_table1')],
'o.id = test_table1.id',
['column1','column2']
);

Qaisar Satti

Hi, I'm Qaisar Satti! I've been a developer for over 20 years, and now I love sharing what I've learned through tutorials and guides. Whether you're working with Magento, PrestaShop, or WooCommerce, my goal is to make your development journey a bit easier and more fun. When I'm not coding or writing, you can find me exploring new tech trends and hanging out with the amazing developer community. Thanks for stopping by, and happy coding!