Magento: Direct SQL Queries

To start with, Magento uses data models which provide a great way to access and modify data. Varien uses aptly named methods and clever abstraction to hides away the complex SQL, which is needed when performing data operations.

While this makes learning models easier, it often impacts the operation speed and therefore, the responsiveness of your site. This is especially true when saving models that use the EAV architecture. More often than not, this cannot be avoided, but in some cases, executing direct SQL queries would be simpler and much quicker.

Let’s take updating product price globally in Magento for example. It would be easy enough to write some Magento codes that loop through all products and modify the price. On a large data set, saving each individual product can take a long time and result in making the system unusable. Is it possible to prevent this? The answer is yes, with issue a direct SQL query which could update 1000 of products in 1 or 2 seconds.

Database Connections in Magento

By default, Magento will automatically connect to its database and provide two separate resources to access data: core_read and core_write. As you can probably guess, core_read is for reading from the database while core_write is for writing to the database. It is important to ensure that you use the correct resource when reading or writing to the database, especially when writingcustom Magento extension that will be released into the wild.

    <?php
        /**
         * Get the resource model
         */
        $resource = Mage::getSingleton(‘core/resource’);
     
        /**
         * Retrieve the read connection
         */
        $readConnection = $resource->getConnection(‘core_read’);
     
        /**
         * Retrieve the write connection
         */
        $writeConnection = $resource->getConnection(‘core_write’);
       ?>

Table Names and Table Prefixes

When installing Magento, you are given an option to use a table prefix. A table prefix is a string of characters that is added to the start of every table name in your database. It is useful if you are installing multiple systems into 1 database as it helps to distinguish each application’s data from other. Fortunately, Magento has a simple built in function which allows you to add the prefix to a given table name.

Get a table name from a string

    <?php
        /**
         * Get the resource model
         */
        $resource = Mage::getSingleton(‘core/resource’);
     
        /**
         * Get the table name
         */
        $tableName = $resource->getTableName(‘catalog_product_entity’);
     
        /**
         * if prefix was ’mage_’ then the below statement
         * would print out mage_catalog_product_entity
         */
        echo $tableName;
        ?>

Get a table name from an entity name

    <?php
     
        /**
         * Get the resource model
         */
        $resource = Mage::getSingleton(‘core/resource’);
     
        /**
         * Get the table name
         */
        $tableName = $resource->getTableName(‘catalog/product’);
     
        /**
         * if prefix was ’mage_’ then the below statement
         * would print out mage_catalog_product_entity
         */
        echo $tableName;
        ?>

Reading from The Database

While Magento models hide the complexity of the EAV system, they sometimes request more data than is needed. For example, if you have a product ID and want its SKU, running a single query to obtain this value would be much quicker than loading in a whole product model (the inverse of this operation is available via the product resource class).

Varien_Db_Select::fetchAll

This method takes a query as its parameter, executes it and then returns all results as an array. In the example code below, we use Varien_Db_Select::fetchAll to return all of the records to the catalog_product_entity table.

    <?php
     
        /**
         * Get the resource model
         */
        $resource = Mage::getSingleton(‘core/resource’);
     
        /**
         * Retrieve the read connection
         */
        $readConnection = $resource->getConnection(‘core_read’);
     
        $query = ‘SELECT * FROM ’ . $resource->getTableName(‘catalog/product’);
     
        /**
         * Execute the query and store the results in $results
         */
        $results = $readConnection->fetchAll($query);
     
        /**
         * Print out the results
         */
        echo sprintf(‘%s’ print_r($results, true));
        ?>

Varien_Db_Select::fetchCol

This method is similar to fetchAll except that instead of returning all results, it returns the first column from each result row. In the code example below, we use Varien_Db_Select::fetchCol to retrieve all of the SKU’s from our database in an array.

    <?php
        /**
          * Get the resource model
          */
        $resource = Mage::getSingleton(‘core/resource’);
     
        /**
         * Retrieve the read connection
         */
        $readConnection = $resource->getConnection(‘core_read’);
     
        /**
         * Retrieve our table name
         */
        $table = $resource->getTableName(‘catalog/product’);
     
        /**
         * Execute the query and store the results in $results
         */
        $sku = $readConnection->fetchCol(‘SELECT sku FROM ’ . $table . ‘);
     
        /**
         * Print out the results
         */
        echo sprintf(‘%s‘ print_r($results, true));
        ?>

Let’s try this code and look at the results. Do you notice how all of the SKU’s are in a single array, rather than each row having its own array? If you don’t understand, try changing fetchCol for fetchAll and compare the differences.

Varien_Db_Select::fetchOne

Unlike the previous two methods, Varien_Db_Select::fetchOne returns one value from the first row only. This value is returned on its own and not wrapped in an array. In the example code below, we take a product ID of 44 and return its SKU.

    <?php
     
        /**
         * Get the resource model
         */
        $resource = Mage::getSingleton(‘core/resource’);
     
        /**
         * Retrieve the read connection
         */
        $readConnection = $resource->getConnection(‘core_read’);
     
        /**
         * Retrieve our table name
         */
        $table = $resource->getTableName(‘catalog/product’);
     
        /**
         * Set the product ID
         */
        $productId = 44;
     
        $query = ‘SELECT sku FROM ’ . $table . ‘ WHERE entity_id = ’
                 . (int)$productId . ‘ LIMIT 1′;
     
        /**
         * Execute the query and store the result in $sku
         */
        $sku = $readConnection->fetchOne($query);
     
        /**
         * Print the SKU to the screen
         */
        echo ‘SKU: ’ . $sku;
          ?>

Please bear in mind when trying this example, make sure you changed the product ID to an ID that exists in your database!

Many people may think that fetchOne works the same as fetchCol or fetchAll if you only add 1 column to the SELECT query and add a ‘LIMIT 1′; however, they could be wrong. The main difference is that the value returned is the actual value, which Varien_Db_Select::fetchCol and Varien_Db_Select::fetchAll would wrap the value in an array. To understand it more thoroughly, try swapping the methods and comparing the results.

Writing to the Database

When saving a Magento model, probably a lot of background data are saved as well but you aren’t even aware of. For example, saving a product model can take several seconds due to the amount of related data and indexing that needs to take place. This is okay if you want all data be saved, but if you want to update only the SKU of a product, it can be wasteful.

The example code below will show you how to alter the SKU when given a product ID. This is a trivial example but should illustrate how to execute writing queries against your Magento database.

    <?php
     
        /**
         * Get the resource model
         */
        $resource = Mage::getSingleton(‘core/resource’);
     
        /**
         * Retrieve the write connection
         */
        $writeConnection = $resource->getConnection(‘core_write’);
     
        /**
         * Retrieve our table name
         */
        $table = $resource->getTableName(‘catalog/product’);     
        /**
         * Set the product ID
         */
        $productId = 44;
     
        /**
         * Set the new SKU
         * It is assumed that you are hard coding the new SKU in
         * If the input is not dynamic, consider using the
         * Varien_Db_Select object to insert data
         */
        $newSku = ‘new-sku’;
     
        $query = “UPDATE {$table} SET sku = ’{$sku}’ WHERE entity_id = ”
                 . (int)$productId;
     
        /**
         * Execute the query
         */
        $writeConnection->query($query);
        ?>

To test whether this has worked, please review the knowledge in the first part of this tutorial to write a query to extract the SKU that has just been changed.

Varien_Db_Select

The Varien_Db_Select, which has been mentioned on in this article, is a far better option for extracting/writing information. It not only is easy to use but also provides a layered security, which is impenetrable if used correctly. I will write more about Varien_Db_Select (a.k.a. Zend_Db_Select) in a future article.

Sometimes, it is necessary to execute direct SQL queries in Magento, however, please be careful! The Magento models are there for a reason and provide a layer of security which you have to manually add to your own direct SQL queries. Be sure to escape any user input and when possible, stick to the Magento model methods! If the Magento models couldn’t meet your requirement, consider using Varien_Db_Select instead. It won’t stop you having errors but it will add an almost impenetrable layer of security to your database queries.