Home » Magento, Magento 229 August 2016

Magento 2: Run Custom SQL Query

This article shows how to write/run/execute custom SQL queries in Magento 2. We need to instantiate class Magento\Framework\App\ResourceConnection for this. You have to inject this resource class in your module’s Block/Model/Controller class constructor. After that, you can use that object to run custom sql queries.

In this example, I will simply be using object manager to instantiate the resource class.

Suppose, I have a table named mytest with fields id, name, age, & email.

The following example code will insert, update, delete and select records from the table mytest.

In the above code, when we run the select query using fetchAll function, the following output is obtained. It returns a multi-dimentional array with table field name and value.

Instead of writing the whole select query by ourselves, we can also use the SQL SELECT Query generation functions that are already provided by Magento and Zend_Db_Select. Here’s an example code:

You can see in detail about this in class Magento\Framework\DB\Select present in vendor/magento/framework/DB/Select.php.

/**
* Class for SQL SELECT generation and results.
*
* @method \Magento\Framework\DB\Select from($name, $cols = ‘*’, $schema = null)
* @method \Magento\Framework\DB\Select join($name, $cond, $cols = ‘*’, $schema = null)
* @method \Magento\Framework\DB\Select joinInner($name, $cond, $cols = ‘*’, $schema = null)
* @method \Magento\Framework\DB\Select joinLeft($name, $cond, $cols = ‘*’, $schema = null)
* @method \Magento\Framework\DB\Select joinNatural($name, $cond, $cols = ‘*’, $schema = null)
* @method \Magento\Framework\DB\Select joinFull($name, $cond, $cols = ‘*’, $schema = null)
* @method \Magento\Framework\DB\Select joinRight($name, $cond, $cols = ‘*’, $schema = null)
* @method \Magento\Framework\DB\Select joinCross($name, $cols = ‘*’, $schema = null)
* @method \Magento\Framework\DB\Select orWhere($cond, $value = null, $type = null)
* @method \Magento\Framework\DB\Select group($spec)
* @method \Magento\Framework\DB\Select order($spec)
* @method \Magento\Framework\DB\Select limitPage($page, $rowCount)
* @method \Magento\Framework\DB\Select forUpdate($flag = true)
* @method \Magento\Framework\DB\Select distinct($flag = true)
* @method \Magento\Framework\DB\Select reset($part = null)
* @method \Magento\Framework\DB\Select columns($cols = ‘*’, $correlationName = null)
*
*/

Note: If you are running your script in custom external file then you need to write the following code to instantiate ObjectManager. Otherwise, you might get the following error:

Fatal error: Class ‘Magento\Framework\App\ObjectManager’ not found

So, here is the solution to it. In the code below, I have selected data from table eav_entity_type using custom sql script.

Hope this helps. Thanks.

Magento 2

Get New Post by Email

Find me on

FacebookTwitterGoogle+LinkedInRSS Feed