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');
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());
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); |
- 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, or post a comment and I'll see if I can help.
Resource: - http://www.vortexcommerce.com/blog/magento-direct-sql/
No comments:
Post a Comment