Changing The Select After The Collection Has Been Loaded

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.