Magento 2 – Run SQL Query Using Model

In this code snippet, we will see how to run SQL query using model. We will write a query for select, insert, update, delete operations, different Where clause operators like IN, LIKE, =, != etc, ORDER BY clause, GROUP BY clause and different JOINS.

Magento 2 recommended the use of the model to do SQL operation. So it is very important that your custom module should define the model class. As an example here we are using model class Codextblog\Test\Model\Test and database table called ‘test’.

Below is the screenshot of ‘test’ database table fields with demo rows

 

Note: For the demonstrated purpose we have used Objectmanager.Codextblog never recommend the direct use of ObjectManager.One should always use a constructor method to instant an object.

Select, Insert, Update and Delete operations

Select query

<?php
$testquery = $objectManager->create('Codextblog\Test\Model\Test');
$collection = $testquery->getCollection();

/*
SELECT `main_table`.* FROM `test` AS `main_table`
*/

Insert query

<?php
$data = array('product_id'=>24,'name'=>'Addidas Backpack','is_active'=>'1');
$testquery= $objectManager->create('Codextblog\Test\Model\Test');
$testquery->setData($data);
$testquery->save();

Update query

<?php
$data = array('product_id'=>24,'name'=>'Black Addidas Backpack','is_active'=>'1');
$id = 5;
$testquery= $objectManager->create('Codextblog\Test\Model\Test')->load($id);
$testquery->addData($data);
$testquery->setId($id)->save();

Delete query

<?php
$id = 5;
$testquery = $objectManager->create('Codextblog\Test\Model\Test')->setId($id)->delete();

Where Clause

eqaul to

$testquery = $objectManager->create('Codextblog\Test\Model\Test')
             ->getCollection()
             ->addFieldToFilter('is_active',1);
);

/*
SELECT `main_table`.* FROM `test` AS `main_table` WHERE (`is_active` = 1)
*/

not eqaul to

$testquery = $objectManager->create('Codextblog\Test\Model\Test')
             ->getCollection()
             ->addFieldToFilter('is_active',array('neq' => 1);
);

/*
SELECT `main_table`.* FROM `test` AS `main_table` WHERE (`is_active` != 1)
*/

LIKE

$testquery = $objectManager->create('Codextblog\Test\Model\Test')
             ->getCollection()
             ->addFieldToFilter('name',array('like' => '%Bag%'));
);
/*
SELECT `main_table`.* FROM `test` AS `main_table` WHERE (`name` LIKE '%Bag%')
*/

IN

$testquery = $objectManager->create('Codextblog\Test\Model\Test')
             ->getCollection()
             ->addFieldToFilter('product_id',array('in' => array('24','25')));
);
/*
SELECT `main_table`.* FROM `test` AS `main_table` WHERE (`id` IN('24','25'))
*/

ORDER By

ORDER BY ASCENDING

<?php
$testquery = $objectManager->create('Codextblog\Test\Model\Test')
             ->getCollection()
             ->addFieldToFilter('is_active',1);
$testquery->getSelect()
          ->order('product_id');

/*
SELECT `main_table`.* FROM `test` AS `main_table` WHERE (`is_active` = 1) ORDER BY `product_id` ASC
*/

ORDER BY DESCENDING

<?php
$testquery = $objectManager->create('Codextblog\Test\Model\Test')
             ->getCollection()
             ->addFieldToFilter('is_active',1);
$testquery->getSelect()
          ->order('product_id' .' '. \Magento\Framework\DB\Select::SQL_DESC);

/*
SELECT `main_table`.* FROM `test` AS `main_table` WHERE (`is_active` = 1) ORDER BY `product_id` DESC
*/

GROUP By

<?php
$testquery = $objectManager->create('Codextblog\Test\Model\Test')
             ->getCollection()
             ->addFieldToFilter('is_active',1);
$testquery->getSelect()
          ->group('product_id');

/*
SELECT `main_table`.* FROM `test` AS `main_table` WHERE (`is_active` = 1) GROUP BY `product_id`
*/

JOINS

Inner Join

<?php
$testquery= $objectManager->create('Codextblog\Test\Model\Test')->getCollection();
$testquery ->getSelect()
           ->join('sales_order_item as item','main_table.product_id = item.product_id',array('*'));

/*
SELECT `main_table`.*, `item`.* FROM `test` AS `main_table` JOIN `sales_flat_order_item` AS `item` ON main_table.product_id = item.product_id
*/

Left Join

<?php
$testquery= $objectManager->create('Codextblog\Test\Model\Test')->getCollection();
$testquery ->getSelect()
           ->joinLeft('sales_order_item as item','main_table.product_id = item.product_id',array('*'));

/*
SELECT `main_table`.*, `item`.* FROM `test` AS `main_table` LEFT JOIN `sales_flat_order_item` AS `item` ON main_table.product_id = item.product_id
*/

Right Join

<?php
$testquery= $objectManager->create('Codextblog\Test\Model\Test')->getCollection();
$testquery ->getSelect()
           ->joinRight('sales_order_item as item','main_table.product_id = item.product_id',array('*'));

/*
SELECT `main_table`.*, `item`.* FROM `test` AS `main_table` RIGHT JOIN `sales_flat_order_item` AS `item` ON main_table.product_id = item.product_id
*/

If you liked this post, then please like us on Facebook and follow us on Twitter.