SQL in Magento

Although Magento’s collections and models are convenient, they can often be cumbersome and awkward to get to perform certain tasks. Fortunately, through the connection (Magento_Db_Adapter_Pdo_Mysql, and ultimately Zend’s MySql connection stack down to Zend_Db_Adapter_Abstract) and select (Varien_Db_Select, and ultimately Zend’s Zend_Db_Select) allow us perform database queries without too much hassle.

Getting the Connection Object

A connection object can be used from the following places:

<?php
$connection = Mage::getSingleton('core/resource')->getConnection('core_read');
$connection = Mage::getSingleton('core/resource')->getConnection('core_write');

$collection->getConnection();
$model->getResource()->getReadConnection();

Getting a Table’s Name

<?php
$table = Mage::getSingleton('core/resource')->getTableName('llapgoch_banners/banner');

Fetch Col

<?php
$connection->fetchCol('SELECT sku from ' . $resource->getTableName('catalog/product'));

– This puts the columns into a single array. I.e. all skus will be in a one dimensional array (array(‘sku1’, ‘sku2’, ‘sku3’) etc.

Fetch One

<?php
$connection->fetchOne('SELECT * FROM ' . $resource->getTableName('llapgoch_banners/banner'));

– Returns a single row for the query

Fetch All

<?php
$connection->fetchAll('SELECT * FROM ' . $resource->getTableName('llapgoch_banners/banner'));

– Returns all rows for the query

Combining with a Select Object

Writing direct SQL is all well and good, but it’s often neater and safer to write such queries using a select object, and passing that to the connection:

<?php
$resource = Mage::getSingleton('core/resource');
$connection = $resource->getConnection('core_read');
		
$select = new Varien_Db_Select($connection);
$select->from($resource->getTableName('llapgoch_banners/banner'), array('title', 'is_active'))
     ->where('banner_id > 1');
		
$results = $connection->fetchAll($select);

Binding Values

Binding values is useful for automatic value escaping. By referencing variables using :variable_name, we can pass in arrays of replacement variables into the connection’s fetch methods:

<?php
$resource = Mage::getSingleton('core/resource');
$connection = $resource->getConnection('core_read');
		
$select = new Varien_Db_Select($connection);
$select->from($resource->getTableName('llapgoch_banners/banner'))
	->where('banner_id > :num_banners');
		
$results = $connection->fetchAll($select, array(
	'num_banners' => 1
));

Select Object Methods

Where

 <?php $select->where($condition, $value = null);

– where provides the option of passing a single value into the condition to be replaced, but can also be used like the above example using binds. To use the value in the condition, we use a question mark:

 <?php $select->where('banner_id > ?', 1);

Limit

 <?php $select->limit($number, $offset);

OrderRand

 <?php $select->orderRand();

From

 <?php $select->from($tableName, $cols = array());

Columns

 <?php $select->columns($cols = "*", $correlationName = null);?>

– Specifies the columns to be brought in the select. Can contain aggregate queries.

Union

 <?php $select->union($select = array(), $type = self::SQL_UNION); 

Join

 <?php $select->join($name, $cond, $cols = self::SQL_WILDCARD, $schema = null);

– This is the same as the joinInner method.

Join Left

 <?php $select->joinLeft($name, $cond, $cols = self::SQL_WILDCARD, $schema = null);

Join Right

 <?php $select->joinRight($name, $cond, $cols = self::SQL_WILDCARD, $schema = null); 

OR Where

 <?php $select->orWhere($cond, $value = null, $type = null); 

Group

 <?php $select->group($spec) 

– $spec can be an array or a string

Having

 <?php $select->having($cond, $value = null, $type = null); 

– $value will be replaced into $cond using a question mark in the query.

OR Having

 <?php $select->orHaving($cond, $value = null, $type = null);

– $value will be replaced into $cond using a question mark in the query.

Order

 <?php $select->order($spec);

– $spec can be a string or an array of columns.

Query

 <?php $select->query($fetchMode = null, $bind = array()); 

– $fetchMode is an integer. This is optional to using the connection’s fetchAll, fetchOne, and fetchCols methods

Reset

 <?php $select->reset($part = null);

– Allows parts of the query to be reset

getAdapter

 <?php $select->getAdapter();

– Returns the adapter (connection object) passed into the constructor.

TODO: Add info about the Zend Connection (adapter) methods in Zend_Db_Adapter_Abstract