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.
-
$collection = Mage::getModel(‘catalog/category’)->load($categoryId)
-
->getProductCollection()
-
->addAttributeToSort(‘name’, ‘ASC’);
To sort using multiple Fields, you can chain calls to the Collection’s method addAttributeToSort(preferred)
-
$collection = Mage::getModel(‘module/model_name’)->getCollection()
-
->addAttributeToSort(‘order’, ‘ASC’)
-
->addAttributeToSort(‘last_name’, ‘ASC’)
-
->addAttributeToSort(‘first_name’, ‘ASC’)
-
;
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.
-
$collection = Mage::getModel(‘module/model_name’)->getCollection();
-
$collection->getSelect()->order( array(‘IF(`order`>0, `order`, 9999) ASC’,
-
‘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
-
$collection = Mage::getModel(‘module/model_name’)->getCollection();
-
$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.
Collection Manipulation Methods |
Varien_Data_Collection |
| addFilter($field, $value, $type = ‘and’) | |
| addItem(Varien_Object $item) | |
| clear() | |
| count() | |
| distinct($flag) | |
| each($obj_method, $args=array()) | |
| getAllIds() | |
| getCacheKey() | |
| getCacheLifetime() | |
| getCacheTags() | |
| getColumnValues($colName) | |
| getCurPage($displacement = 0) | |
| getFirstItem() | |
| getFlag($flag) | |
| getItemByColumnValue($column, $value) | |
| getItemById($idValue) | |
| getItems() | |
| getItemsByColumnValue($column, $value) | |
| getIterator() | |
| getLastItem() | |
| getLastPageNumber() | |
| getNewEmptyItem() | |
| getPageSize() | |
| getSize() | |
| hasFlag($flag) | |
| isLoaded() | |
| loadData($printQuery = false, $logQuery = false) | |
| load($printQuery = false, $logQuery = false) | |
| removeItemByKey($key) | |
| setCacheKey($key) | |
| setCacheTags($tags) | |
| setCurPage($page) | |
| setDataToAll($key, $value=null) | |
| setFlag($flag, $value = null) | |
| setOrder($field, $direction = self::SORT_ORDER_DESC) | |
| setPageSize($size) | |
| toArray($arrRequiredFields = array()) | |
| toOptionArray() | |
| toOptionHash() | |
| toXml() | |
| walk($callback, array $args=array()) |
Varien_Data_Collection_Db |
Inherits above methods for Varien_Data_Collection, and in addition (re)defines the following:
| addBindParam($name, $value) | |
| addFieldToFilter($field, $condition=null) | |
| addOrder($field, $direction = self::SORT_ORDER_DESC) | |
| distinct($flag) | |
| getConnection() | |
| getData() | |
| getIdFieldName() | |
| getSelect() | |
| getSelectCountSql() | |
| getSize() | |
| initCache($object, $idPrefix, $tags) | |
| loadData($printQuery = false, $logQuery = false) | |
| load($printQuery = false, $logQuery = false) | |
| printLogQuery($printQuery = false, $logQuery = false, $sql = null) | |
| resetData() | |
| setConnection($conn) | |
| setOrder($field, $direction = self::SORT_ORDER_DESC) | |
| unshiftOrder($field, $direction = self::SORT_ORDER_DESC) |
Filter Condition Codes |
| 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.
E.g.
-
$collection->addAttributeToFilter(‘field_name’, array(
-
‘in’ => array(1, 2, 3),
-
));
-
$collection->addAttributeToFilter(‘date_field’, array(
-
‘from’ => ‘2000-09-10’,
-
));
-
$collection->addAttributeToFilter(‘date_field’, array(
-
‘from’ => ’10 September 2000′,
-
‘to’ => ’11 September 2000′,
-
‘date’ => true, // specifies conversion of comparison values
-
));
-
$collection->addAttributeToFilter(‘field_name’, array(
-
‘notnull’ => true,
-
));
-
$collection->addAttributeToFilter(‘field_name’, 1); // tests for equality
-
// Add OR condition:
-
$collection->addAttributeToFilter(array(
-
array(
-
‘attribute’ => ‘field_name’,
-
‘in’ => array(1, 2, 3),
-
),
-
array(
-
‘attribute’ => ‘date_field’,
-
‘from’ => ‘2000-09-10’,
-
),
-
));
Mage_Eav_Model_Entity_Collection |
Mage/Eav/Model/Entity/Collection/Abstract.php
Inherits above methods for Varien_Data_Collection_Db, and in addition (re)defines the following:
| addAttributeToFilter($attribute, $condition=null, $joinType=’inner’) | adds WHERE clause on $attribute specified by $condition |
| addAttributeToSelect($attribute, $joinType=false) | gets the value for $attribute in the SELECT clause;specify * to get all attributes (i.e. to execute SELECT *) |
| addAttributeToSort($attribute, $dir=’asc’) | adds ORDER BY clause on $attribute |
| addEntityTypeToSelect($entityType, $prefix) | doesn’t seem to do anything – don’t use |
| addExpressionAttributeToSelect($alias, $expression, string|array $attribute) | adds SQL expression $expression, using $alias, to SELECT clause (typically containing aggregate functions such as SUM(), COUNT());when $attribute specifies a single attribute as a string, $expression can reference the attribute as simply {{attribute}}, but when passing an array of attributes, each attribute must be referenced in $expression by the name of the specific attribute;N.B. use with groupByAttribute() when including aggregate functions q.v. |
| addFieldToFilter($attribute, $condition=null) | alias for addAttributeToFilter() |
| addItem(Varien_Object $object) | adds an object to the collection |
| addStaticField($field) | specifies $field as being static, i.e. as existing on the entity table itself, rather than in the EAV table |
| delete() | deletes all entities in the collection |
| exportToArray() | returns collection data as a 2D array |
| getAllIds($limit=null, $offset=null) | returns array of all entity IDs selected by current WHERE clause (optionally specifying $limit and $offset) |
| getAllIdsSql() | not recommended – don’t use |
| getAttribute($attributeCode) | for specified $attributeCode, returnsMage_Eav_Model_Entity_Attribute_Abstract object of the entity object used by the collection (i.e. calls getAttribute($attributeCode) on the entity object and returns the result) |
| getEntity() | returns Mage_Eav_Model_Entity_Abstract object used by collection for attributes |
| getLoadedIds() | returns array of IDs of currently loaded entities |
| getResource() | returns Mage_Core_Model_Resource_Abstract instance; actually an alias for getEntity() q.v. (an entity is a special type of resource, that is, Mage_Eav_Model_Entity_Abstract extendsMage_Core_Model_Resource_Abstract) |
| getRowIdFieldName() | returns field name of ID attribute for entities in the collection |
| getTable($table) | alias for Mage::getSingleton(‘core/resource’)->getTableName($table) |
| groupByAttribute($attribute) | adds $attribute to GROUP BY clause |
| importFromArray($arr) | imports 2D array into collection as objects |
| joinAttribute($alias, Mage_Eav_Model_Entity_Attribute_Abstract|string $attribute, string $bind, string $filter=null, string $joinType=’inner’, $storeId=null) | joins another entity and adds attribute from joined entity, using $alias, toSELECT clause;$attribute can specify attribute object, or string in format[entity]/[attribute]; $bind specifies attribute of the main entity on which to make join; $filter specifies primary key of the joined entity on which to make join (normally defaults to entity_id);$joinType should be inner or left |
| joinField($alias, $table, $field, $bind, $cond=null, $joinType=’inner’) | joins regular table field using an attribute as foreign key |
| joinTable($table, $bind, $fields=null, $cond=null, $joinType=’inner’) | joins table $table |
| load($printQuery = false, $logQuery = false) | runs query and load data into collection; specify $printQuery as true to print SQL for debugging |
| removeAttributeToSelect($attribute=null) | removes $attribute from SELECT clause;specify null to remove all attributes |
| save() | saves all entities in the collection |
| setEntity(string|object $entity) | sets entity object (i.e. Mage_Eav_Model_Entity_Abstract) used by collection for attributes |
| setObject(Varien_Object $object=null) | sets template object for the collection |
| setOrder(string| $attribute, $dir=’desc’) | alias for addAttributeToSort() q.v., identical except that it can accept array of attributes, and default $dir is desc;N.B. overrides parent function |
| setPage($pageNum, $pageSize) | sets LIMIT clause by specifying page number (one-indexed) and number of records per page;N.B. equivalent to calling setCurPage($pageNum) andsetPageSize($pageSize) q.q.v. |
| setRowIdFieldName($fieldName) | returns field name of ID attribute for entities in the collection |
| toArray(array $arrAttributes=array()) | calls toArray($attAttributes) on each entity in collection, and returns result as array |