I am using the following code for getting products in the beforeToHtml() function of a block, but when I get the products in the template file using $this->getProductCollection() it returns only 15 items, while running the query in mysql directly returns 120 items.


SELECT `e`.*, `at_inventory_in_stock`.`is_in_stock` AS `inventory_in_stock`, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `cat_index1`.`position`, SUM(sfoi.qty_ordered) AS `ordered_qty` FROM `catalog_product_entity` AS `e` INNER JOIN `cataloginventory_stock_item` AS `at_inventory_in_stock` ON (at_inventory_in_stock.`product_id`=e.entity_id) AND ((at_inventory_in_stock.use_config_manage_stock = 0 AND at_inventory_in_stock.manage_stock=1 AND at_inventory_in_stock.is_in_stock=1) OR (at_inventory_in_stock.use_config_manage_stock = 0 AND at_inventory_in_stock.manage_stock=0) OR (at_inventory_in_stock.use_config_manage_stock = 1 AND at_inventory_in_stock.is_in_stock=1)) INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id='1' AND cat_index.visibility IN(2, 4) AND cat_index.category_id='2' INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 INNER JOIN `catalog_category_product_index` AS `cat_index1` ON cat_index1.product_id=e.entity_id AND cat_index1.store_id=1 AND cat_index1.category_id IN (43,42,71,50,44,45,54,46,47,51,48,49,52,55) INNER JOIN `sales_flat_order_item` AS `sfoi` ON e.sku = sfoi.sku GROUP BY `e`.`entity_id` ORDER BY `ordered_qty` DESC 


$_category = Mage::getModel('catalog/category')->loadByAttribute('name', 'Phones');
$collection = Mage::getResourceModel('catalog/product_collection');

$collection = $this->_addProductAttributesAndPrices($collection)

if(!is_numeric($price) && $price != "-1"){
    $price = explode(',', mysql_escape_string($price));
    //Mage::log(print_r($price,true), null, 'phonefinder.log');
    if(count($price) == 2){
        $start = $price[0];
        if($start != 0) {
            //Mage::log("Adding gteq expression for start amount of : " . $start, null, 'phonefinder.log');
            $collection->addAttributeToFilter('price', array('gteq' => $start));
        $end = $price[1];
        if($end != '-1') {
            //Mage::log("Adding lteq expression for end amount of : " . $end, null, 'phonefinder.log');
            $collection->addAttributeToFilter('price', array('lteq' => $end));
if($sortBy == 1)
    $collection->addAttributeToSort('price', 'desc');
else if($sortBy == 0)
    $collection->addAttributeToSort('price', 'asc');

if($sortBy == 2){
        ->joinLeft('sales_flat_order_item AS sfoi',
        'e.sku = sfoi.sku',
        'SUM(sfoi.qty_ordered) AS ordered_qty')->group('e.entity_id')->order('ordered_qty DESC');


echo $collection->getSelect();
return parent::_beforeToHtml();
  • Hey Tariq, can you update or close your question? thank you! – Sander Mangel Oct 17 '13 at 7:27

It's probably due to the $this->getProductsCount() having a value of 15. This causes the query to only return 15 records.

Do a var_dump on this method to see if it is indeed 15.

  • yes it is 15, how to replace it, so all products returns? – Tariq Aziz Oct 14 '13 at 10:04
  • remove setPageSize method call form your collection – user487772 Oct 14 '13 at 10:07
  • I tried it. but another error appears in the template file. – Tariq Aziz Oct 14 '13 at 10:11
  • please update your question with the error – Sander Mangel Oct 14 '13 at 10:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.