Working with the Product Collection in Magento

Practice

Any developer working with the Magento CMS sooner or later has to dive into model collections. Very often you have to work with the product resource collection. In this article we'll go over the basics of loading, filtering, sorting, and modifying this collection.

Loading the product collection
There are two main ways to load the product collection. Using the getCollection() method from a product model instance, or loading the collection class via the getResourceModel() factory method.

1 2	$collection = Mage::getModel('catalog/product')->getCollection(); $collection = Mage::getResourceModel('catalog/product_collection');

Adding attributes to the collection
A product model is a huge set of data. The more model attributes are used in a selection, the longer it will take to execute. By default, using the methods mentioned above, Magento CMS only loads the base data found in the table

1	'catalog_product_entity' (ID, SKU, Entity Type ID и т.д.).

To add all attributes, use:

1	$collection->addAttributeToSelect('*');

For individual attributes:

1	$collection->addAttributeToSelect('name', 'description', 'sku');

For specific attributes:

$collection->addFinalPrice();

$collection->addMinimalPrice();

$collection->addTaxPercents();

Add category ID to products:

1	$collection->addCategoryIds();

Tier pricing:

1	$collection->addTierPriceData();

Add URL Rewrites to products:

1	$collection->addUrlRewrite();

Filtering results
Using the EAV entity type of the system, you can access the amazing 'addAttributeToFilter()' method. For example:

1	$collection->addAttributeToFilter('sku', array('eq' => '0,7л_34567'));

you can also write it as:

1	$collection->addAttributeToFilter('sku', '0,7л_34567');

This method is used as a supplement to the standard 'addFieldToFilter()' method from the Zend library Varien_Data_Collection_Db and accepts a large number of arguments. These arguments are represented as expressions and are similar in nature to SQL queries. Here is an example of such expressions in Magento and their equivalent use of data selection (SQL) with the 'WHERE' keyword:

array("eq" => '0,7л_34567') WHERE (sku = '0,7л_34567')

array("neq" => '0,7л_34567') WHERE (sku <> '0,7л_34567')

array("like" => '0,7л_34567') WHERE (sku LIKE '0,7л_34567')

array("nlike" => '0,7л') WHERE (sku NOT LIKE '0,7л')

array("is" => '0,7л') WHERE (sku IS '0,7л')

array("in" => array('0,7л', '34567')) WHERE (sku IN ('0,7л', '34567'))

array("nin" => array('0,7л', '34567')) WHERE (sku NOT IN ('0,7л', '34567'))

array("notnull" => true) WHERE (sku IS NOT NULL)

array("null" => true) WHERE (sku IS NULL)

array("gt" => 3) WHERE (sku > 3)

array("lt" => 5) WHERE (sku < 5) array("gteq" => 4) WHERE (sku >= 4)

array("lteq" => 2) WHERE (sku =< 2) array("finset" => array('_34567')) WHERE (find_in_set('_34567', sku))

array('from' => 2, 'to' => 10) WHERE (sku >= '2' AND sku <= '10') For more details, you should read up on the standard 'addFieldToFilter()' method. Filter results by product ID: $collection->addIdFilter(array(4,5,6));

Filter by current store:

1	$collection->addStoreFilter();

Filter by current website:

1	$collection->addWebsiteFilter();

Filter by category:

1	$collection->setStoreId($id)->addCategoryFilter($category);

Sorting the collection
Again, as with SQL, you can sort the collection by a chosen attribute in ascending or descending order.
Ascending order:

1	$collection->setOrder('price', 'ASC');

Descending order:

1	$collection->setOrder('name', 'DESC');

Random order:

1	$collection->setOrder('rand()');

Limiting the selection
If a situation arises where you need to limit the number of products in the loaded collection, you can use the 'setPageSize()' method:

1	$collection->setPageSize(10);

Number of products
It's always useful to check whether any results came back after loading the collection and how many products are in the collection:

$collection->count();

Selecting a list of product identifiers

Sometimes you just need to get product identifiers from the collection, not all the other attributes. This is easy to do by calling the method:

1	$collection->getAllIds();

Debugging the collection
Want to know why and how the collection returns results? By calling the 'getSelect()' method on the collection, you can output all the MySQL queries that are sent to the database.

1	$collection->getSelect();

Model collection classes in Magento are extremely powerful tools that significantly simplify running queries and filtering data, even without any knowledge of MySQL.