:::: MENU ::::

Magento 2 use custom query

Magento 2 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']
    );
Author: Qaisar Satti
Category: Magento 2
Last Modified: July 12, 2018


One Comment

So, what do you think ?