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)
*
*/

Hope this helps. Thanks.

Magento 2

Get New Post by Email

Find me on

FacebookTwitterGoogle+LinkedInRSS Feed

Comments are closed.