Categories


Archives


Recent Posts


Categories


Running Arbitrary SQL in Magento 2. Like an animal.

astorm

Frustrated by Magento? Then you’ll love Commerce Bug, the must have debugging extension for anyone using Magento. Whether you’re just starting out or you’re a seasoned pro, Commerce Bug will save you and your team hours everyday. Grab a copy and start working with Magento instead of against it.

No Frills Magento Layout is the only Magento front end book you'll ever need. Get your copy today!

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.

Copyright © Alan Storm 1975 – 2017 All Rights Reserved

Originally Posted: 11th May 2016