How to Use Custom Queries in Magento 2?

  • Posted by Envision Ecommerce
  • /
  • July 13, 2016
How to use custom queries in Magento2

Welcome to the new series of Magento2 filters. In the previous blog post, we covered some DIY steps for controller override in Magento 2. Now in this post, we provide Magento users a great and simple way to update queries in Magento 2 by using the custom query.

Magento2 has come up with a more flexible feature that allows you manipulate data with the Database tables. Though all data have given by default by Magento 2, but if you want to work with the custom queries, then you really need to insert or get some data into the database. In this blog post, we just want to explain how it is possible to update queries by using the custom query.

Let’s take an example of table ‘student’ with fields std_id, std_name, std_section and std_class.

Now use the following code for Select, Delete, Insert and Update queries.

// Instance of object manager $objectManager = \Magento\Framework\App\ObjectManager::getInstance();

 

$resource = $objectManager->get(‘Magento\Framework\App\ResourceConnection’); $connectionDb = $resource->getConnection();

 

//gives table name with prefix $tableName = $resource->getTableName(‘student’); //Select Data from table $query = “Select * FROM ” . $tableName; $result = $connectionDb->fetchAll($query); //Delete Data from table $query = “Delete FROM ” . $tableName.” Where std_id = 12″; $connectionDb->query($query); //Insert Data into table $query = “Insert Into ” . $tableName . ” (std_id, std_name, std_section, std_class) Values (”,’ABC’,’B’,’II’)”; $connectionDb->query($query); //Update Data into table $query = “Update ” . $tableName . “Set std_class = ‘III’ where std_id = 13”; $connectionDb->query($query);

Hope this Magento 2 coding will be helpful for you, Thanks and Enjoy!