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 table1, 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.
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
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):