Magento 2 – call SQL statements
In this tutorial I show you how to call SQL statements directly to database with Magento 2. This is NOT the recommended solution, you should use models and collections as long as it is possible. For some very special tasks a direct SQL statement call may be a solution, so this is how it works.
Magento 2 – call SQL statements
A special task is for example, if you want to get all products with a sku that starts with given string. The following example shows a basic usage of how you can call select statements to magento database. All connection data comes directly from you Magento 2 setting.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | class CallSQLStatementExample { protected $_resource; public function __construct( \Magento\Framework\App\Helper\Context $context, \Magento\Framework\App\ResourceConnection $resource ) { parent::__construct($context); $this->_resource = $resource; $this->_helper = $helper->create(); } public function getSkus($baseSku) { $connection = $this->_resource->getConnection(\Magento\Framework\App\ResourceConnection::DEFAULT_CONNECTION); $tblCatalogProduct = $connection->getTableName('catalog_product_entity'); //only needed if we need also to link all simple products $result = $connection->fetchAll("SELECT sku FROM `".$tblCatalogProduct."` WHERE sku like '".$baseSku."%'"); $return = []; foreach($result as $element) $return[] = $element['sku']; } } |
As you can see, you only need to inject \Magento\Framework\App\ResourceConnection. With such a resource object you can get a connection to run for example select statements by calling fetchAll() function. As return value, you will get an array of that result set.
Conclusion
Call SQL statements directly in Magento 2 is very easy. Magento 1 solution was a bit more complex. Now you can run all your customized and complicated select statements to get your needed data. You may also use insert or update statements to write data to database.
