Magento 2 – Run SQL Query Using Model

run_sql_query_using_model_magento2

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

magento2_database_table
magento2_database_table

 

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.

Leave a Comment

(0 Comments)

All the comments are goes into moderation before approval. Please do not spam. Your email address will not be published. Required fields are marked *

Enjoy this post? Please support Us!

Like our social page for daily new updates….


Want to become
a Magento 2 Expert?

If Yes! Then Subscribe to our newsletter and get weekly article to you email id.
Subscribe Here
SUBSCRIBE NOW
close-link

To Avoid Spam Downloads, We Want Your Email

We will send you download link right
away. Please submit form below.
SEND ME DOWNLOAD LINK
Close