Using Collections in Magento

Last modified by houtsnip on Tue, March 20, 2012 06:55
Source|Old Revisions  

This is an old revision of the document!

This Wiki Article is under construction.

A collection is a Model type containing other Models, it is basically used in Magento to handle product lists (ie. from a category or a bundle option), but not only.

TO DO: Explain how Magento Implements a collection - Use this to explain how Magento implements a collection by looking at code in a model, so that people can learn to write their own collections

This is a simple example of loading some product collection from a category and ordering them on their product name using Magento’s API.

  1. $collection = Mage::getModel('catalog/category')->load($categoryId)
  2.     ->getProductCollection()
  3.     ->addAttributeToSort('name', 'ASC');

To sort using multiple Fields, you can chain calls to the Collection’s method addAttributeToSort(preferred)

  1. $collection = Mage::getModel('module/model_name')->getCollection()
  2.     ->addAttributeToSort('order', 'ASC')
  3.     ->addAttributeToSort('last_name', 'ASC')
  4.     ->addAttributeToSort('first_name', 'ASC')
  5. ;

TODO: use Magento’s API use cases, not Zend_Db_Select ones.

You can also pass IF/THEN statements, but be sure to use the proper quotation of your table’s fields.

  1. $collection = Mage::getModel('module/model_name')->getCollection();
  2. $collection->getSelect()->order( array('IF(`order`>0, `order`, 9999) ASC',
  3.      'last_name ASC', 'first_name ASC') );

In this example, the table will be sorted by the order field, then by last name, then by first name, where order is greater than zero, followed by order being equal to or less than zero, all ascending.

Joining Tables

To add SQL joins to a select

  1. $collection = Mage::getModel('module/model_name')->getCollection();
  2. $collection->getSelect()->join( array('table_alias'=>$this->getTable('module/table_name')), 'main_table.foreign_id = table_alias.primary_key', array('table_alias.*'), 'schema_name_if_different');

In this example the join method takes an array of alias⇒table_name key pairs, then a standard join clause using `main_table` to refer to the original select, then an array of fields to be retrieved in the join (defaults to *), a different schema can be specified as a last parameter.

→join defaults to an inner join, others can be used:

→joinInner() →joinLeft() →joinRight() →joinFull() →joinCross() →joinNatural()

See lib/Zend/Db/Select.php for source.

Field Filters

Attribute Code SQL Equivalent Description
eq =
neq !=
like LIKE
nlike NOT LIKE
in IN ()
nin NOT IN ()
is IS
notnull IS NOT NULL
null IS NULL
moreq >=
gt >
lt <
gteq >=
lteq <=
finset FIND_IN_SET()
from >= for use with dates
to <= for use with dates
date optional flag for use with from/to to specify that comparison value should first be converted to a date
datetime optional flag for use with from/to to specify that comparison value should first be converted to a datetime

If an array is passed but no attribute code specified, it will be interpreted as a group of OR conditions that will be processed in the same way.

If no attribute code is specified, it defaults to eq.


  1. $collection->addAttributeToFilter('field_name', array(
  2.     'in' => array(1, 2, 3),
  3.     ));
  5. $collection->addAttributeToFilter('date_field', array(
  6.    'from' => '2000-09-10',
  7.     ));
  9. $collection->addAttributeToFilter('date_field', array(
  10.     'from' => '10 September 2000',
  11.     'from' => '11 September 2000',
  12.     'date' => true, // specifies conversion of comparison values
  13.     ));
  15. $collection->addAttributeToFilter('field_name', 1); // tests for equality
  17. // Add OR condition:
  18. $collection->addAttributeToFilter(array(
  19.     array(
  20.         'attribute' => 'field_name',
  21.         'in'        => array(1, 2, 3),
  22.         ),
  23.     array(
  24.         'attribute' => 'date_field',
  25.         'from'      => '2000-09-10',
  26.         ),
  27.     ));


addAttributeToFilter($attribute, $condition=null, $joinType=’inner’)
addAttributeToSelect($attribute, $joinType=false)
addAttributeToSort($attribute, $dir=’asc’)
addEntityTypeToSelect($entityType, $prefix)
addExpressionAttributeToSelect($alias, $expression, $attribute)
addFieldToFilter($attribute, $condition=null)
addItem(Varien_Object $object)
getAllIds($limit=null, $offset=null)
joinAttribute($alias, $attribute, $bind, $filter=null, $joinType=’inner’, $storeId=null)
joinField($alias, $table, $field, $bind, $cond=null, $joinType=’inner’)
joinTable($table, $bind, $fields=null, $cond=null, $joinType=’inner’)
_loadAttributes($printQuery = false, $logQuery = false)
_loadEntities($printQuery = false, $logQuery = false)
load($printQuery = false, $logQuery = false)
setOrder($attribute, $dir=’desc’)
setPage($pageNum, $pageSize)
toArray($arrAttributes = array())