Categories
Magento 1

Returning an Array of Values from a Collection

Magento collections contain the protected method _toOptionArray which can be exposed to public methods of the collection. The method accepts two arguments, the first becomes the key of the resultant array, and the second becomes the value.

<?php
public function toOptionArray(){
return $this->_toOptionArray('business_type_id', 'title');
}
Categories
Magento 1

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');

Getting part of a SELECT

$collection->getSelect()->getPart(Zend_Db_Select::FROM);

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