Running Arbitrary SQL in Magento 2. Like an animal.

While phrases like “should we be be doing this in 2016” abound in engineering discussions, down in the trenches an arbitrary SQL query is often the fastest, most expedient way to get at the data you want. While I still regularly encourage developers new to Magento to embrace using the models and API resource models when they’re not sure where to find a piece of data, for more experienced developers sometimes a good old fashioned SQL query is the right choice.

If you’re in that situation, something like the following should serve you well

<?php namespace PackageNamespaceHelper

class Someclass
{
    protected $resourceConnection;
    public function __construct(
        MagentoFrameworkAppResourceConnection $resourceConnection            
    )
    {
        $this->resourceConnection = $resourceConnection;
    }

    public function working()
    {
        $db     = $this->resourceConnection->getConnection();
        $result = $db->query('SELECT * 
        FROM catalog_category_entity_varchar');

        while($row = $result->fetch())
        {
            var_dump($row);
        }
        exit;        
    }    
}

Above, we’ve injected a MagentoFrameworkAppResourceConnection object into our class. This is Magento’s global, singleton-like resource connection object. Calling this object’s getConnection method without any arguments will return the default database adapter for this Magento system. For a stock system this is going to be a MagentoFrameworkDBAdapterPdoMysql object.

The stock Magento database classes are still based on the Zend Framework – MagentoFrameworkDBAdapterPdoMysql extends a Zend_Db_Adapter_Pdo_Mysql class, calling the query method returns a statement object which will also extend a Zend framework object (MagentoFrameworkDBStatementPdoMysql and Zend_Db_Statement_Pdo for a stock system.). In other words, all the objects and methods you’re already familiar with will are there.

If injecting an object is a foreign concept for you, my object manager series may be able to clear things up.