Magento Product Collection Biblde

Magento Product Collection Bible

Any developer who uses Magento will have at some point found themselves deep in model collections, and none more so than the product resource collection.

In this post I cover all the angles with regards to loading your product collections, filtering, sorting and modifying them. This is the Magento Product Collection Bible. Amen to that.

Load Product Collection

There are two basic ways of loading your product collection. Either call method getCollection() on your product model instance or load collection class in the factory method ‘getResourceModel’.

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

Add Attributes To Collection

The product model is an enormous data set, and the more attributes we include in the load the longer it will take. By default, Magento will only load the basic data found in the ‘catalog_product_entity’ table (ID, SKU, Entity Type ID etc…)

Add All Product Attributes

$collection->getAttributeToSelect('*');

Add Individual Product Attributes

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

Add Specific Price Attributes

$collection->addMinimalPrice();
$collection->addFinalPrice();
$collection->addTaxPercents();

Add Category IDs to Products

$collection->addCategoryIds();

Add Tier Pricing Info

$collection->addTierPriceData();

Add Product URL Rewrites

$collection->addUrlRewrite();

Filter Collection

Being an EAV entity type means that we have access to the wonderful ‘addAttributeToFilter()’ method in addition to the standard ‘addFieldToFilter()’, which can take a larger range of arguments.

The following are basic conditional filters, like you’d use in a MySQL query:

Is Equal To

$collection->addAttributeToFilter('status', array('eq' => 1));

Is Not Equal To

$collection->addAttributeToFilter('visibility', array('neq' => 1));

Greater Than

$collection->addAttributeToFilter('price', array('gt' => 3));

Less Than

$collection->addAttributeToFilter('price', array('lt' => 3));

Greater Than or Equal To

$collection->addAttributeToFilter('price', array('gteq' => 4));

Less Than or Equal To

$collection->addAttributeToFilter('price', array('lteq' => 4));

Contains – with % wildcards

$collection->addAttributeToFilter('sku', array('like' => 'DVD%'));

Does Not Contain – with % wildcards

$collection->addAttributeToFilter('sku', array('nlike' => 'ABC%'));

In Array

$collection->addAttributeToFilter('entity_id', array('in' => array(1,3,12)));

Not In Array

$collection->addAttributeToFilter('entity_id', array('nin' => array(1,2,12)));

Is NULL

$collection->addAttributeToFilter('description', 'null');

Is Not NULL

$collection->addAttributeToFilter('description', 'notnull');

Further Filtering

There are also more general filters that can be applied:

Filter by Product IDs

$collection->addIdFilter(array(4,5,6));

Filter by Current Store

$collection->addStoreFilter();

Filter by Current Website

$collection->addWebsiteFilter();

Filter by Category

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

Sort Collection

Again, as with MySQL you can sort your collection by a chosen attribute in either ascending or descending order.

Order by Attribute Ascending

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

Order by Attribute Descending

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

Random Order

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

Limit Collection

If you’re in a situation where you want your product collection, but want to limit the number of results to a certain amount you can use the ‘setPageSize()’ method, and simply pass in your limit.

$collection->setPageSize(10);

Count Results

Always useful when checking if any results have been returned.

$collection->count();

Return Only Product IDs

Sometimes you’ll just need to get product IDs from your collection, rather than the rest of the product data. By Using ‘getAllIds’ you will receive a nice little array of product IDs for you to do with as you wish!

$collection->getAllIds();

Debug Collection

Want to know why your collection keeps returning results? Using the ‘getSelect()’ method on your collection you can print the MySQL query that is getting sent to the database.

$collection->getSelect();

The model-collection classes in Magento are extremely powerful and make data querying and filtering very easy, without any MySQL knowledge. Thanks for reading!

  • picksumipsum

    Excellent, thanks for this!

  • Christian

    Cool post, but I have some trouble.

    This is the code:

    $_product_collection = Mage::getModel(‘catalog/category’)->load(SOMECATEGORYID)->getProductCollection();
    $_product_collection->addAttributeToFilter(‘sku’, array(‘like’ => ‘%’.$_somePartiolSku.’%’));

    $_productWithColorOfLastProduct_id = $_product_collection->addFieldToFilter(‘is_active’, 1);
    //$_productWithColorOfLastProduct_id = $_product_collection->addAttributeToFilter(‘is_active’, 1);

    The third line causes is both versions the following error:
    Fatal error: Call to a member function getBackend() on a non-object in PATH/app/code/core/Mage/Eav/Model/Entity/Abstract.php on line 816

    This accurs after the Magento Update from 1.5.1 to 1.8.1.

    Can you help me? I also tried to add
    $_product_collection->addAttributeToSelect(‘is_active’); before the third line without any luck.
    $_product_collection->addAttributeToSelect(‘is_active’);

    • Christian

      never mind, I solved it myself. A simple reindex of the all indexes fixed this.

  • Richard Parnaby-King

    Very cool post. Learned a couple of new things :)

    Just in case it’s needed:

    To get the store id:

    $id = Mage::app()->getStore()->getStoreId();

    To get the category object:

    $cat_id = 8; // set desired category id
    $category = Mage::getModel(‘catalog/category’)->load($cat_id);

    • Rob Stanley

      I see you -.-

  • karthikphp

    Hi

    I used to display relavent product below coding,

    $collection = Mage::getResourceModel(‘catalog/product_collection’);
    $collection->getAttributeToSelect(‘*’);
    print_r($collection);
    result:
    Call to undefined method Mage_Catalog_Model_Resource_Product_Collection::getAttributeToSelect()

    • james

      i think it should be addAttributeToSelect(*);

  • tim_bezhashvyly

    There’s a typo in

    $collection->addAttributeToSelect(‘name’, ‘description’, ‘brand’);

    The array enclosure is missing.

  • agonzalez

    Hello, someone know how to split the collection?… i mean in the collection have 500 products but i need that in two chunks each chunk with 250 products

    • http://maxime-bongiorno.fr Maxime

      $numberOfProductNeeded = 250;
      $_collection->setPageSize($numberOfProductNeeded)->setCurPage(1);

      The setPageSize Method allows you to have X records.
      The setCurPage Method allows you to select which page you want to display.

      In your case, the first chunk will be called with the code above, if you want the second one, just set your curPage at 2.

      Hope this helps.

  • https://minicrm.pl/ Maksymilian Ĺšleziak z miniCRM

    Thank you!