2

Prior to my store allowing back orders, when browsing all products that were "In Stock" were displayed with a higher priority over products that were "Out of stock".

Since allowing back orders on my website, now the logic for this functionality is flawed. Now products with 0 stock are mixed with products with stock making it difficult for visitors to find products that are available for immediate purchase.

What I would like to do is show products with the most quantity first and not just "In Stock"

I'm finding plenty of information about sorting products by "in stock" but nothing about product quantities.

What is the best method to achieve sorting products by the largest quantities first when browsing?

5
+50

Here is what I got.
The following code works for simple products with manageable stock only. It all falls over if configurable, grouped or products for which you don't manage the stock are involved.
You will need a new extension, just like Richard also suggested that rewrites the product collection resource model.
Here is the full extension. Let's name it Easylife_Sorting.
You will need the following files:

app/etc/modules/Easylife_Sorting.xml - the declaration file

<?xml version="1.0"?>
<config>
    <modules>
        <Easylife_Sorting>
            <codePool>local</codePool>
            <active>true</active>
            <depends>
                <Mage_Catalog />
            </depends>
        </Easylife_Sorting>
    </modules>
</config>

app/code/local/Easylife/Sorting/etc/config.xml - the configuration file:

<?xml version="1.0"?>
<config>
    <modules>
        <Easylife_Sorting>
            <version>1.0.0</version>
        </Easylife_Sorting>
    </modules>
    <global>
        <models>
            <catalog_resource>
                <rewrite>
                    <product_collection>Easylife_Sorting_Model_Resource_Product_Collection</product_collection>
                </rewrite>
            </catalog_resource>
        </models>
    </global>
</config>

app/code/local/Easylife/Sorting/Model/Resource/Product/Collection.php - the rewritten collection model

<?php
class Easylife_Sorting_Model_Resource_Product_Collection
    extends Mage_Catalog_Model_Resource_Product_Collection {
    public function addAttributeToSort($attribute, $dir = self::SORT_ORDER_ASC){
        //don't screw up the admin sorting
        if (Mage::app()->getStore()->getId() == Mage_Core_Model_App::ADMIN_STORE_ID){
            return parent::addAttributeToSort($attribute, $dir);
        }
        //the stock sorting should apply only when sorting by position. Leave the price and name alone
        if ($attribute == 'position') {
            //join the stock status index table for the current website and check if the product is saleable and the qtu.
            $select = $this->getSelect();
            $select->joinLeft(
                array('stock_qty' => $this->getTable('cataloginventory/stock_status')),
                'e.entity_id = stock_qty.product_id AND stock_qty.website_id='.Mage::app()->getWebsite()->getId(),
                array(
                    'salable' => 'stock_qty.stock_status',
                    'qty' => 'stock_qty.qty'
                )
            );
            //get the reversed sorting
            //if by position ascending, then you need to sort by qty descending and the other way around
            $reverseDir = ($dir == 'ASC') ? 'DESC' : 'ASC';
            //this is optional - it shows the in stock products above the out of stock ones independent if sorting by position ascending or descending
            $this->getSelect()->order('salable DESC');
            //sort by qty
            $this->getSelect()->order('qty '.$reverseDir);
            return $this;
        }
        //if not sorting by position, let magento do its magic.
        return parent::addAttributeToSort($attribute, $dir);
    }
}
  • Hey, here you're directly manipulating the select object, rather than using the collection methods, would you explain why? Is related to things like this magentocommerce.com/bug-tracking/issue/index/id/339 ? – Richard Nov 19 '14 at 12:02
  • @Richard. The idea is not actually mine. Magento does the same thing to hide the out of stock products. Take a look at the method Mage_CatalogInventory_Model_Resource_Stock_Status::addStockStatusToSelect. I found that by mistake and realized that is a good approach to filter products by stock status or sort them by stock status. It also applies on this case. – Marius Nov 19 '14 at 12:11
  • Oh, well if it's ok to beat the Select up manually, I've revised mine to count up the stock in configurable. :) – Richard Nov 19 '14 at 14:37
  • @Richar...well, you kind of do the same thing...manipulate the select with $this->getSelect()->columns :). – Marius Nov 19 '14 at 15:05
  • 1
    I see what you are saying; From what I've observed in my scenario is that visitors do not purchase products without stock. So if I present them only products with stock then it should help conversion. Here is your bounty :) candyaddict.com/blog/candy_images/bounty_bar_wrapped.jpg – SR_Magento Nov 20 '14 at 20:26
4

Try this code it will sort the product collection based on the inventory Quantity.

$collection = Mage::getResourceModel('catalog/product_collection');
$collection->getSelect()->joinLeft(
            array('_inventory_table'=>'cataloginventory_stock_item'),
            "_inventory_table.product_id = e.entity_id ",
            array('qty')
        )->order(array('_inventory_table.qty DESC'));

You can change the DESC to ASC to do the reverse

1

We have an un elegant solution but it works for us. We have created a new attribute called category_sort_score. We then run a nightly cron that updates this value based on the products stock level , backorder value , popularity , etc. We then use this attribute as the default sort for categories. This is not an ideal solution but it seems to do the trick. The complications are however , grouped products , configurable products and bundled products as their stock level is dependant on their children.

  • That's an interesting solution and I will consider it but for now I'll wait to see if anyone else chimes in. You would think that this would be a feature that went a long with the back order's. – SR_Magento Nov 17 '14 at 12:17
  • Creating a new attribute just to sort the products isn't really a good way to go. – Tarun Nov 19 '14 at 7:59
1

Edit 3

This now works for configurable products, if by works you mean counts up the total for all the child products

We redefined position sort to do this. We created a subcless of Mage_Catalog_Model_Resource_Product_Collection and overrode addAttributeToSort in an extension, I won't go into creating your own extension and using your subclass instead of the normal one here, but our replacement addAttributeToSort is this.

public function addAttributeToSort($attribute, $dir = self::SORT_ORDER_ASC)
{
    if ($attribute == 'position') {
        $orig_dir = $dir;
        $dir = ($dir == 'ASC') ? 'DESC' : 'ASC';
        $atts = array();
        if (isset($this->_joinFields[$attribute])) {
            $this->getSelect()->order($this->_getAttributeFieldName($attribute) . ' ' . $dir);
            return $this;
        }
        $entity_type_id = Mage::getModel('eav/config')->getEntityType('catalog_product')->getEntityTypeId();
        $attribute_id = Mage::getModel('eav/entity_attribute')->getIdByCode('catalog_product', 'status');
        $this->getSelect()->joinLeft(
            array(
                'css1' => $this->getTable('cataloginventory/stock_status')
            ),
            'e.entity_id = css1.product_id and css1.stock_id =1 AND css1.website_id='.Mage::app()->getWebsite()->getId(),
            ''
        )->joinLeft(
            array(
                'cpsl' => $this->getTable('catalog/product_super_link')
            ),
            'cpsl.parent_id = e.entity_id',
            ''
        )->joinLeft(
            array(
                'css2' => $this->getTable('cataloginventory/stock_status')
            ),
            'css2.product_id = cpsl.product_id and css1.stock_id =1 AND css1.website_id='.Mage::app()->getWebsite()->getId(),
            ''
        )->joinLeft(
            array(
                'cpei' => 'catalog_product_entity_int'
            ),
            "cpei.entity_id = cpsl.product_id AND cpei.entity_type_id = $entity_type_id and attribute_id = $attribute_id",
            ''
        )->where(
            '(cpei.value IS NULL or cpei.value=' . Mage_Catalog_Model_Product_Status::STATUS_ENABLED . ')'
        )->group(
            'e.entity_id'
        );

        if ($this->isEnabledFlat()) {
            $atts[] = "qty_position {$dir}";
        } else {
            // optimize if using cat index
            $this->addPriceData();
            $filters = $this->_productLimitationFilters;
            if (isset($filters['category_id']) || isset($filters['visibility'])) {
                $atts[] = 'qty_position ' . $dir;
            } else {
                $atts[] = 'e.entity_id ' . $dir;
            }
        }
        $atts[] = "max_price {$orig_dir}";
        $this->getSelect()->columns(array('qty_position' => new Zend_Db_Expr ("IF(`e`.`type_id` = 'simple', `css1`.qty + position, sum(css2.qty) + position)")));
        $this->getSelect()->order($atts);
    } elseif ($attribute == 'price' && Mage::app()->getStore()->getId() != 0) {
        $this->addPriceData();
        $this->getSelect()->order("price_index.max_price {$dir}");
    } else {
        parent::addAttributeToSort($attribute, $dir);
    }

    return $this;
}

It's slightly more complicated than you asked for, it sorts by (position + qty) in reverse, and then max_price forwards. Eg. If you chose position asc in the list page tool bar the collection is sorted by (position + qty) in descending order, and then max price asc.

We use position in our categories backwards, so 0 appears last and 1000 would appear first, and we let this combine with qty so we can use position to keep products with no stock at the top if we need to.

We sort by max_price not min_price because we didn't like products being ordered by their tier pricing, but if you're making heavy use of special price you may want to cut that bit.

The only bit I can't remember the why for is {{table}}.stock_id=1 which you might need to investigate.

  • But does this work for configurable products? They don't have a qty on their own. – Marius Nov 19 '14 at 10:00
  • No. Sorry, I saw you comment to the question and did mean to qualify that it was only for simples, making it work for configurables is on our TODO list – Richard Nov 19 '14 at 10:02
  • @Richard where do I insert this code? – SR_Magento Nov 19 '14 at 11:43
  • Create an extension, create a model that extends Mage_Catalog_Model_Resource_Product_Collection and overrides addAttributeToSort with the code, there's quite a few answers on here that deal with that aspect of your question. – Richard Nov 19 '14 at 11:47
  • Small fix; add 'cpei' in front of attribute_id, so and attribute_id = becomes and cpei.attribute_id = , otherwise you'll get ambiguous attribute_id in query warnings. – Peter Jaap Blaakmeer Jun 22 '16 at 12:29

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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