We can load Magento collections a couple of ways. As an example, if we take a look at the loading the catalog product collection, the following two lines would bring back the collection.
$collection = Mage::getModel('catalog/product')->getCollection();
$collection = Mage::getResourceModel('catalog/product_collection');
Collection classes inherit from Mage_Core_Model_Resource_Db_Collection_Abstract that in turn inherits from Varien_Data_Collection_Db that inherits from Varien_Data_Collection
The Varien_Data_Collection_Db class contains some useful methods.
public function getSize()
{
if (is_null($this->_totalRecords)) {
$sql = $this->getSelectCountSql();
$this->_totalRecords = $this->getConnection()->fetchOne($sql, $this->_bindParams);
}
return intval($this->_totalRecords);
}
/**
* Get SQL for get record count
*
* @return Varien_Db_Select
*/
public function getSelectCountSql()
{
$this->_renderFilters();
$countSelect = clone $this->getSelect();
$countSelect->reset(Zend_Db_Select::ORDER);
$countSelect->reset(Zend_Db_Select::LIMIT_COUNT);
$countSelect->reset(Zend_Db_Select::LIMIT_OFFSET);
$countSelect->reset(Zend_Db_Select::COLUMNS);
$countSelect->columns('COUNT(*)');
return $countSelect;
}
And the Varien_Data_Collection class contains some methods such as getFirstItem() and getLastItem().
public function getFirstItem()
{
$this->load();
if (count($this->_items)) {
reset($this->_items);
return current($this->_items);
}
return new $this->_itemObjectClass();
}
/**
* Retrieve collection last item
*
* @return Varien_Object
*/
public function getLastItem()
{
$this->load();
if (count($this->_items)) {
return end($this->_items);
}
return new $this->_itemObjectClass();
}
We can use the following methods to show us the SQL query to get the collection.
$collection->getSelect(); $collection->printLogQuery(true);
So to view the catalog product collection SQL query, we could write the following.
$collection = Mage::getModel('catalog/product')->getCollection();
echo $collection->getSelect();
That prints out the below.
SELECT `e`.* FROM `catalog_product_entity` AS `e`
It’s worth noting that additional database queries will not be made until you attempt to access an item in the Collection. So we could use the addAttributeToSelect() method to only bring back selected attributes rather than everything.
The below would select the weight attribute to be used in the query and would successfully print out the product’s weight in the foreach loop.
$collection = Mage::getModel('catalog/product')->getCollection()
->addAttributeToSelect('weight');
foreach ($collection as $product){
echo $product->getWeight();
}
However the below code would not work.
$collection = Mage::getModel('catalog/product')->getCollection()
->addAttributeToSelect('weight');
foreach ($collection as $product){
echo $product->getName();
}
To see additional queries run by Magento, it is worth enabling query logging in the lib/Varien/Db/Adapter/Pdo/Mysql.php and setting the $_debug and $_logAllQueries properties to true.
protected $_debug = false; /** * Minimum query duration time to be logged * * @var float */ protected $_logQueryTime = 0.05; /** * Log all queries (ignored minimum query duration time) * * @var bool */ protected $_logAllQueries = false;
As well as the addAttributeToSelect() method, there is also an addAttributeToFilter() method.
$collection = Mage::getModel('catalog/product')->getCollection()
->addAttributeToFilter('sku', array('eq' => '001'));
This essentially will get us the products that has an SKU equal to 001, like an SQL statement.
SELECT * FROM catalog_product_entity WHERE SKU = '001'
Note the eq operator used here. Other operators exist that could be used.
neq – Not equallike – Likenlike – Not likein – Innin – Not ingt – Greater thangteq – Greater than or equal tolt – Less thanlteq – Less than or equal tonull – Nullnotnull – Not nullWe can use multiple addAttributeToFilter() methods should we need to get specific data from the collection. The below acts like an AND statement.
$collection->addAttributeToFilter('sku', array('like' => '%ch%'));
$collection->addAttributeToFilter('status', array('eq' => '1'));
As well as AND statements, we can also use syntax similar to an OR statement.
$collection->addAttributeToFilter(
array(
array('attribute'=> 'someattribute','like' => 'value'),
array('attribute'=> 'otherattribute','eq' => 'value'),
array('attribute'=> 'anotherattribute','gt' => 'value'),
)
);
The addAttributeToFilter() method only works with EAV entity types in Magento. To use the same functionality with non-eav entity models, change the addAttributeToFilter() method with the addFieldToFilter() method. This method works in the exact same way and takes the same parameters, however it works on non-EAV entities.
$collection->addFieldToFilter(
array(
array('attribute'=> 'someattribute','like' => 'value'),
array('attribute'=> 'otherattribute','eq' => 'value'),
array('attribute'=> 'anotherattribute','gt' => 'value'),
)
);
As we saw in the Varien_Data_Collection class, we can get the first and last items of a collection by using the getFirstItem() and getLastItem() methods.
$collection = Mage::getModel('catalog/product')->getCollection();
print_r($collection->getFirstItem()->getData());
What if we wanted to join collections? The join() method takes three parameters: The table name, the condition and the columns to join.
public function join($table, $cond, $cols = '*')
{
if (is_array($table)) {
foreach ($table as $k => $v) {
$alias = $k;
$table = $v;
break;
}
} else {
$alias = $table;
}
if (!isset($this->_joinedTables[$alias])) {
$this->getSelect()->join(
array($alias => $this->getTable($table)),
$cond,
$cols
);
$this->_joinedTables[$alias] = true;
}
return $this;
}
So if we wanted to join the sales_flat_order_item table with the sales_flat_order table, we could write something like the below.
$collection = Mage::getModel('sales/order')->getCollection();
$collection
->join(
array('order_item'=> sales_flat_order_item),
'order_item.order_id = main_table.entity_id',
array('order_item.sku')
);
Note: This article is based on Magento Community/Open Source version 1.9.