In the previous post, we had seen the how to do various sql query in a mysql resource file. In this blog post we will see how to work with magento collection and doing various operations with collections.
In this section, we will look in all the various sql operations on collections. Collections are also very important when working on admin grids.
Collection
Collections in magento is like an array of model object but which many more features. You can think of collections as result of select query in a table, and each row stored in collection as an array of models. Collections are used to do many database queries, basically select query with where, count, and other sql operators.
Before beginning, there is an important thing to note about collections in magento. There are 2 types of parent classes for magento collection
1. Mage_Core_Model_Mysql4_Collection_Abstract
2. Mage_Eav_Model_Entity_Collection_Abstract
First class, is used in our normal collection tables but the second collection is used in table which has EAV type of database model. Modules like Product, Category, Customer, Order etc use EAV table structure. We will first go through the first type of collection.
Collection Type 1
Here is a simple select query with where condition on a collection.
PHP Code:
$collection = Mage::getModel('aws/aws')->getCollection();
$collection->addFieldToFilter('file_id',3);
$collection->addFieldToFilter('list_id',array('in'=>array(1,2,3)));
$collection->addFieldToFilter('list_id',array('eq'=>'test'));
And here is the mysql output
Code:
SELECT `main_table`.* FROM `aws` AS `main_table` WHERE (file_id = '3') AND (list_id in (1, 2, 3)) AND (list_id = 'test')
The complete list of things you can use in the array is ‘in’,’eq’,’ neq’,’like’,’nlike’,’in’,’nin’,’is’,’notnull’,’nul l’,’moreq’,’gt’,’lt’,’gteq’,’lteq’,’finset’
PHP Code:
$collection = Mage::getModel('aws/aws')->getCollection();
$collection->addFieldToFilter('file_id',array('from'=>3,'to'=>10));
echo $collection->getSelect();
Output:
Code:
SELECT `main_table`.* FROM `aws` AS `main_table` WHERE (file_id >= 3 and file_id <= 10)
Using join in collection is similar to what is done in the select object in the previous tutorial. The syntax is same. Here is the magneto function for join
PHP Code:
public function join($table, $cond, $cols='*')
{
if (!isset($this->_joinedTables[$table])) {
$this->getSelect()->join(array($table=>$this->getTable($table)), $cond, $cols);
$this->_joinedTables[$table] = true;
}
return $this;
}
As you see, magento simply calls the getSelect() function on the collection to get the Select Object.
Important thing to note here is that, you can work on the select object to do all operations as seen in the previous tutorial by using the getSelect() function on the collection. $collection->getSelect() returns the select object. All the operations we did in the previous tutorial are operations on the collection now using getSelect() function.
So, most of the operations on this collection is done getSelect() function itself.
Collection Type2
The collection type2, is used in all model which has EAV tables. Will explain the magento EAV model in detail later, but for now we know the Product, Category, Order, Customer and many more models follow the EAV structure. These collection classes extend Mage_Eav_Model_Entity_Collection_Abstract which intern extends Mage_Eav_Model_Entity_Collection_Abstract, so all the previous function are still valid here. We will look at some special functions here
PHP Code:
$collection = Mage::getModel('catalog/product')->getCollection();
$collection->addAttributeToFilter('created_at','2011-02-31');
$collection = Mage::getModel('catalog/product')->getCollection();
// select all attributes
$collection->addAttributeToSelect('*');
// select specific attributes
$collection->addAttributeToSelect(array('name', 'url_key', 'type_id'));
// select only those items whose status = 1
$collection->addAttributeToFilter('status', 1);
// alternative to select only those items whose status = 1
$collection->addAttributeToFilter('status', array('eq' => 1));
// using LIKE statement
$collection->addAttributeToFilter('sku', array('like' => '%CH%'));
// using IN statement,
// i.e. selecting only those items whose ID fall in the given array
$collection->addAttributeToFilter('id', array('in' => array(1, 14, 51, 52)));
// selecting only those items whose ID is greater than the given value
$collection->addAttributeToFilter('id', array('gt' => 5));
// select by date range
$collection->addAttributeToFilter('date_field', array(
'from' => '10 September 2010',
'from' => '21 September 2010',
'date' => true, // specifies conversion of comparison values
));
// Add OR condition:
$collection->addAttributeToFilter(array(
array(
'attribute' => 'field_name',
'in' => array(1, 2, 3),
),
array(
'attribute' => 'date_field',
'from' => '2010-09-10',
),
));
JoinField in Collection
Here is magento’s description on the function
PHP Code:
/**
* Join regular table field and use an attribute as fk
*
* Examples:
* ('country_name', 'directory/country_name', 'name', 'country_id=shipping_country',
* "{{table}}.language_code='en'", 'left')
*
* @param string $alias 'country_name'
* @param string $table 'directory/country_name'
* @param string $field 'name'
* @param string $bind 'PK(country_id)=FK(shipping_country_id)'
* @param string|array $cond "{{table}}.language_code='en'" OR array('language_code'=>'en')
* @param string $joinType 'left'
* @return Mage_Eav_Model_Entity_Collection_Abstract
*/
public function joinField($alias, $table, $field, $bind, $cond=null, $joinType='inner'){}
So, the above function is used to join tables in eav collection. There are more functions used only for eav collection
View more threads in the same category:
Magento Module Development - Part 9 - Collection and SQL
Reply With Quote
Bookmarks