How To: Magento Direct MySQL Queries

While developing custom solutions for Magento, developers should always think about performance. There are many areas that we can focus on: block caching and removing unnecessary blocks from layout, disabling unused modules, database-related optimisation. Let’s talk about the database-related optimisation, about direct SQL queries in particular.

Using collections and models in Magento is a nice way to get data from the database. But sometimes you need to get or update just a few small items (for example, you need to get a number of records in an AJAX call). Using a Magento model or collection might work for this, but this is not an effective approach for this kind of task. The best way to do this is to use direct SQL queries. In our article we will work with Magento 1.7.0.2, which uses components of Zend Framework 1.11.1

Selecting Data

For example, we want to display the count of all active products that are visible in catalog and search in the current store.

/** @var $coreResource Mage_Core_Model_Resource */
$coreResource = Mage::getSingleton('core/resource');

/** @var $conn Varien_Db_Adapter_Pdo_Mysql */
$conn = $coreResource->getConnection('core_read');

/** @var $select Varien_Db_Select */
$select = $conn->select()
    ->from(array('p' => $coreResource->getTableName('catalog/product')), new Zend_Db_Expr('COUNT(*)'))
    ->join(
    array(
        'st' => $coreResource->getTableName('catalog/product_enabled_index')),
        'st.product_id = p.entity_id',
        array()
    )
    ->where('st.visibility = ?', Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH)
    ->where('st.store_id = ?', Mage::app()->getStore()->getId());
 
$count = $conn->fetchOne($select);

This will run the following query:

SELECT COUNT(*) FROM `catalog_product_entity` AS `p`
 INNER JOIN `catalog_product_enabled_index` AS `st`
 ON st.product_id = p.entity_id
 WHERE (st.visibility = 4) AND (st.store_id = '1')

Let’s go step by step through this code snippet.

/** @var $conn Varien_Db_Adapter_Pdo_Mysql */
$conn = $coreResource->getConnection('core_read');
Mage_Core_Model_Resource - this class is a resources factory that keeps connections in it.
/** @var $conn Varien_Db_Adapter_Pdo_Mysql */
$conn = $coreResource->getConnection('core_read');

Here we get a connection object. It extends a chain of classes, and the base class is Zend_Db_Adapter_Abstract.

/** @var $select Varien_Db_Select */
$select = $conn->select()
    ->from(array('p' => $coreResource->getTableName('catalog/product')), new Zend_Db_Expr('COUNT(*)'))
    ->join(
        array('st' => $coreResource->getTableName('catalog/product_enabled_index')),
        'st.product_id = p.entity_id',
        array()
    )
    ->where('st.visibility = ?', Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH)
    ->where('st.store_id = ?', Mage::app()->getStore()->getId());

The select object is used for constructing the query. This class extends Zend_Db_Select. We passed the table name and column name in the method. Pay attention to how the table name is being passed to the method using the Mage_Core_Model_Resource::getTableName() method. You should never use raw table names, because Magento tables can be created with a custom prefix.

join method is used for joining the enabled products index table. The second parameter is a condition for joining, and the third parameter contains the columns that we need to select from the joined table (in our example no columns are required from that table).

where is used to specify the condition for the query. This method also performs quoting to avoid SQL injections.

$count = $conn->fetchOne($select);

This line fetches the result from the database, based on our constructed query. Zend Db adapter has a set of different useful methods for fetching the data, so you can choose the one that suits you:

  • fetchOne – fetches one record from result (usually used for fetching counts etc)
  • fetchRow – get only one row from result (associative array)
  • fetchCol – get non-associative array from result (flat array, usually used for getting a list of values, for example – entity id’s)
  • fetchAll – fetches all records as an array of associative arrays
  • fetchPairs – returns data in an array of key-value pairs, as an associative array with a single entry per row (example: selecting two fields like id and name)

Updating data

For updating data you can use the Zend_Db_Adapter_Abstract::update() method. It requires three parameters: table name, data to update (associative array) and condition.

Don’t forget to specify the condition for updating, because it has a default value of an empty string, so if you run update with an empty condition it will update all records.

If you want to specify multiple conditions, you can pass them as an array:

$conn->update(
    $coreResource->getTableName('your/table'),
    array('field_to_update' => 'new_value'),
    array(
        'field1 = ?' => $value1,
        'field2 = ?' => $value2,
    )
);

Inserting data

Zend_Db_Adapter_Abstract::insert() is responsible for inserting data. It requires the table name and an associative array of data to insert.

There is also additional method, that is implemented by Varien Varien_Db_Adapter_Pdo_Mysql::insertMultiple(). It allows you to insert multiple rows with one query. It is useful for inserting large amounts of data with one query.

$conn->insert(
   $coreResource->getTableName('your/table'), 
   array('field1' => 'value1', 'field2' => 'value2')
);

To get the id of the last inserted row you can use Zend_Db_Adapter_Abstract::lastInsertId()

Deleting data

It is similar to update method Zend_Db_Adapter_Abstract::delete().

The method expects a table name and a condition. Be careful – do not forget to specify condition.

$conn->delete(
   $coreResource->getTableName('your/table'), 
   array('id IN(?)' => array(1, 2, 3))
);

Transactions

When using several queries for modifying data you should use transactions. Transactions are handled by Zend Db adapter.

$conn->beginTransaction();
try {
    // Your db manipulations here
   $conn->commit();
} catch (Exception $e) {
  $conn->rollBack();
}

This is a basic overview of components responsible for communicating with database. For more information visit the official Zend Framework documentation.


LATEST POSTS

WIN a Two-Day Intro to Web Design and WordPress Course!WIN a Two-Day Intro to Web Design and WordPress...

3 weeks ago READ

Vortex Shortlisted for ‘Big Chip Transformation Award’ with AKW!Vortex Shortlisted for ‘Big Chip Transformation...

2 months ago READ

Vortex is 5!Vortex is 5!

2 months ago READ
all posts