To find out the SQL query Magento uses to retrieve a specific collection, just do the following:

Let's say you would like to know what query is used to get a product collection - go to your debugging area (or maybe some phtml file), and paste following code:

$_productCollection = Mage::getResourceModel("catalog/product_collection");
$_productCollection->load(true);

Passing "true" as a parameter to the load function, will print out the query that Magento uses to retrieve the items for the collection.

Lets observe at the following example:

For some reason you want to show the products whose price is lower than 100$.
If you go to list.phtml at app/design/frontend/base/default/template/catalog/product, you will see the following line:

$_productCollection=$this->getLoadedProductCollection();

$_productCollection is instance of Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection, and of course this is already loaded collection (collection that has items). resource collections in Magento - once the collection is loaded, you cannot add anything to the "select" and then call "load" again - this will not change the items of a collection. The solution is:

$_productCollection->clear();
$_productCollection->getSelect()->where('price_index.price < 100'); $_productCollection->load();

In line 1 we called to "clear" function that deleted all items in the collection, but preserved the current select. So in the line 2 we added stuff to the select, and called "load". This gave us the desired result.

So how actually does Magento Layered Navigation work?

It may sound surprising, but a chunk of code that triggers all the functionality of the layered navigation, is placed in "Mage_Catalog_Block_Layer_View", inside the function "_prepareLayout()".

When is the function _prepareLayout being called?

The function "_prepareLayout()" exists in every block, and it's being called each time the block is being created.
Therefore, each time the block "Mage_Catalog_Block_Layer_View" is created, it's function "_prepareLayout()" is being called.

Here we loop over all the filterable attributes and filter the current product collection by each of them:
We create an appropriate block for each attribute (for example, for dropdown attribute, we create block from class "Mage_Catalog_Block_Layer_Filter_Attribute") and call its "init" function.

/* FILE: code/core/Mage/Catalog/Block/Layer/View.php Function:_prepareLayout */
/**
     * Prepare child blocks
     *
     * @return Mage_Catalog_Block_Layer_View
     */
    protected function _prepareLayout()
    {
        $stateBlock = $this->getLayout()->createBlock($this->_stateBlockName)
            ->setLayer($this->getLayer());

        $categoryBlock = $this->getLayout()->createBlock($this->_categoryBlockName)
            ->setLayer($this->getLayer())
            ->init();

        $this->setChild('layer_state', $stateBlock);
        $this->setChild('category_filter', $categoryBlock);

        $filterableAttributes = $this->_getFilterableAttributes();
        foreach ($filterableAttributes as $attribute) {
            if ($attribute->getAttributeCode() == 'price') {
                $filterBlockName = $this->_priceFilterBlockName;
            }
            elseif ($attribute->getBackendType() == 'decimal') {
                $filterBlockName = $this->_decimalFilterBlockName;
            }
            else {
                $filterBlockName = $this->_attributeFilterBlockName;
            }

            $this->setChild($attribute->getAttributeCode() . '_filter',
                $this->getLayout()->createBlock($filterBlockName)
                    ->setLayer($this->getLayer())
                    ->setAttributeModel($attribute)
                    ->init());
        }

        $this->getLayer()->apply();

        return parent::_prepareLayout();
    }

Inside the "init" function we have a call to "_initFilter":

Here (line 15) we call the apply function on the model that regards the filter block. (e.g:  for the block "Mage_Catalog_Block_Layer_Filter_Attribute", the model will be "Mage_Catalog_Model_Layer_Filter_Attribute")

/* FILE: code/core/Mage/Catalog/Block/Layer/Filter/Abstract.php Function:_initFilter */
/**
     * Init filter model object
     *
     * @return Mage_Catalog_Block_Layer_Filter_Abstract
     */
    protected function _initFilter()
    {
        if (!$this->_filterModelName) {
            Mage::throwException(Mage::helper('catalog')->__('Filter model name must be declared.'
            ));
        }
        $this->_filter = Mage::getModel($this->_filterModelName)
            ->setLayer($this->getLayer());
        $this->_prepareFilter();

        $this->_filter->apply($this->getRequest(), $this);
        return $this;
    }

 /* FILE: code/core/Mage/Catalog/Model/Layer/Filter/Attribute.php Function:apply */

So finally, the filter is applied to product collection if it exists and has non-empty value in
the request URI.

The apply is performed by:
$this->_getResource()->applyFilterToCollection($this, $filter);
(in the following code chunk).
/**

* Apply attribute option filter to product collection

*

* @param   Zend_Controller_Request_Abstract $request

* @param   Varien_Object $filterBlock

* @return  Mage_Catalog_Model_Layer_Filter_Attribute

*/

public function apply(Zend_Controller_Request_Abstract $request, $filterBlock)

{

$filter = $request->getParam($this->_requestVar);

if (is_array($filter)) {

return $this;

}

$text = $this->_getOptionText($filter);

if ($filter && $text) {

$this->_getResource()->applyFilterToCollection($this, $filter);

$this->getLayer()->getState()->addFilter($this->_createItem($text, $filter));

$this->_items = array();

}

return $this;

}

While "_getResource" returns a resource model of this class (in our example for the class "Mage_Catalog_Model_Layer_Filter_Attribute" "_getResource" will return "Mage_Catalog_Model_Resource_Eav_Mysql4_Layer_Filter_Attribute" object), and there it calls "applyFilterToCollection" that builds appropriate SQL query according to the attribute and its value in URI.

And thats all,when the loop ends running we have a filtered product collection. And if we go now to "Mage_Catalog_Block_Product_List" (the block that shows the product collection),we will see that the collection is retrieved from layer ("Mage_Catalog_Model_Layer") - this is the object which we have loaded the filtered product collection to it after looping over all filterable attributes.

Summary

As we have seen, the architecture of Magento Layered Navigation is simple - the creation of "Mage_Catalog_Block_Layer_View" block triggers looping on models that regards the filterable attributes, and save the filtered product collection back to the layer.

After understanding the architecture of Magento Layered Navigation we have the power to customize Magento Layered Navigation to our own needs.
Read the next posts of this series, to see the examples of our new capabilities.