Magento Custom Product Listing – Sorting by Position

collection;product-collectionproduct-listsorting

First, I've searched Google, SO, and magento SE to no avail. I've seen many results, but none that seemed to work.

I've got a "custom" product listing on the front-end, which uses the below code:

$magentoProduct = Mage::getModel('catalog/product');
$_helper = $this->helper('catalog/output');

$collection = $magentoProduct->getCollection()
            ->addAttributeToSelect('*')
            ->addAttributeToFilter('status', 1)
            ->setStoreId(Mage_Core_Model_App::ADMIN_STORE_ID);
// This doesn't have any effect
//          ->setOrder('position', 'ASC');

$collectionIds = $collection->getAllIds();
foreach ($collectionIds as $prodId):
    $magentoProduct->reset();
    $magentoProduct = $magentoProduct->load($prodId);
    $cats = $magentoProduct->getCategoryIds();
    // ... do stuff ...
endforeach;

I want the sort order to respond to the Product Position (as set in the "Catalog"=>"Manage Categories" interface).

I've tried a variety of things, to no avail.

Any insights? (I'm also open to a better / different approach to this, so feel free to suggest best practices).

Best Answer

First, you should know that the position is saved in the relation table catalog_category_product, so without filtering by category you will not have this data. In theory you could join the relation table without actually filtering by category, but which row will you use? Usually products belong to multiple categories.

Your product collection is independent of any category, so it will contain products from different categories. Does it really makes sense to sort these products by position within an arbitrary category? You decide. But I would recommend adding a product attribute. If you call it "position" and add it as static attribute, i.e. as field to the main table¹, you can use the code as it is. But to avoid conflicts with the position inside a category, I would choose a different code. Also, if you don't add it as static field, but as normal EAV attribute, you have to use addAttributeToSort('position') instead of setOrder('position').

A general recommendation: Instead of addAttributeToSelect('*') load only the attributes that you need. In product listing, the following is usually sufficient:

$collection
    ->addMinimalPrice()
    ->addFinalPrice()
    ->addTaxPercents()
    ->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())
    ->addUrlRewrite();

With this code, you should configure your new position attribute as "Used in product listing: Yes", so that it will be included in addAttributeToSelect() automatically.

^{1) Usually, you don't do that but if you use these custom collections a lot with many products, you should consider it, alongside with a MySQL index on the column, to improve the sorting performance.}

Related Solutions

Magento CE 1.7.0.2 – Custom Sorting for Product Collection

Ok. I should wait an hour more before posting a question.
This seams to work.
I've rewritten Mage_Catalog_Model_Resource_Product_Collection::addAttributeToSort() and made it look like this (not a pretty code but works, layered navigation and all):

<?php
class Namespace_Catalog_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);
        }
        if ($attribute == 'position') { //if should sort by position
            $newLimit = SOME code to get the limit date for products added after X days;
            $this->addExpressionAttributeToSelect(
                'sort_position',
                "(CASE WHEN {{created_at}} >= '{$newLimit}' THEN 0 ELSE 1 END)",
                array('created_at'=>'created_at')
            ); //select a 0|1 flag field. If the product is new then it has the value 0 else it's 1
            $this->addExpressionAttributeToSelect(
                'discount',
                '(CASE WHEN (price_index.price - price_index.min_price) > 0 THEN 1 ELSE 0 END)',
                array()
            );// select a 0|1 flag field. If the product has a discount then 1 else 0
            $this->getSelect()->order(array('sort_position '.$dir, 'discount '.$dir)); //sort by my new fields keeping the direction selected by the user
            return $this;
        }
        else { //if should sort by something else let Magento do it's magic.
            return parent::addAttributeToSort($attribute, $dir);
        }
    }
}

Magento 1.9 Product Collection – Custom Sorting Based on Position, Best Seller, and Created Date

I have fixed this issue.

1) Add new option by override Mage_Catalog_Model_Config , in this use getAttributeUsedForSortByArray function for add option

public function getAttributeUsedForSortByArray()
{
   foreach ($this->getAttributesUsedForSortBy() as $attribute) {
        $options[$attribute->getAttributeCode()] = $attribute->getStoreLabel();
    }
    $options['new_bestselling'] = Mage::helper('catalog')->__('New & Best Selling');

    return $options;
}

2) Add new option by override Mage_Adminhtml_Model_System_Config_Source_Catalog_ListSort in your custom module modify toOptionArray function

public function toOptionArray()
{
    $options = array();

    foreach ($this->_getCatalogConfig()->getAttributesUsedForSortBy() as $attribute) {
        $options[] = array(
            'label' => Mage::helper('catalog')->__($attribute['frontend_label']),
            'value' => $attribute['attribute_code']
        );
    }

    $options[] = array(
        'label' => Mage::helper('catalog')->__('New & Best Selling'),
        'value' => 'new_bestselling'
    );

    return $options;
}

3) Main logic is built in toolbar.php file for that override Mage_Catalog_Block_Product_List_Toolbar in your custom module and modify setCollection function .

public function setCollection($collection)
{
    parent::setCollection($collection);

    if ($this->getCurrentOrder()) {
        if($this->getCurrentOrder() == 'new_bestselling') {
              $this->getCollection()->getSelect()
                 ->joinLeft(
                        array('sfoi' => $collection->getResource()->getTable('sales/order_item')),
                         'e.entity_id = sfoi.product_id',
                         array('qty_ordered' => 'SUM(sfoi.qty_ordered)')
                     )
                 ->group('e.entity_id');
                 $this->getCollection()->getSelect()->Order(array(new Zend_Db_Expr('cat_index_position = 0,cat_index_position ASC')));
                 $this->getCollection()->getSelect()->Order(array(new Zend_Db_Expr('qty_ordered = NULL,qty_ordered DESC')));
                 $this->getCollection()->getSelect()->Order(array(new Zend_Db_Expr('e.created_at DESC')));
        } 

        else {
             $this->getCollection()
                 ->setOrder($this->getCurrentOrder(), $this->getCurrentDirection())->getSelect();
        }
    }

    return $this;
}

Best Answer

Related Solutions

Magento CE 1.7.0.2 – Custom Sorting for Product Collection

Magento 1.9 Product Collection – Custom Sorting Based on Position, Best Seller, and Created Date

Related Topic