1 
$sale_products = Mage::getModel('catalog/product')->getCollection();
$sale_products->addAttributeToSelect(array(
                               'image',
                               'name',
                               'short_description', 
               ))
               ->addFieldToFilter('visibility', array(
                           Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH,
                               Mage_Catalog_Model_Product_Visibility::VISIBILITY_IN_CATALOG
               ))
               ->addFieldToFilter('feature', array('eq' => 1))
               ->addAttributeToSort('position', 'ASC')
               ->addFinalPrice()
               ->getSelect()
               ->where('price_index.final_price < price_index.price')

Right now I only know how to sort by position. and I believe the position is sorted by the date item become sale.

If I sort by ->addAttributeToSort('category_id', 'ASC') I am not sure if it have any effect. b/c product have many category ids. and I can't find a pattern that match.

How to sort Product by it's parent category position?

Improve this question
4
  • Did you try something like this: Mage::getResourceModel('catalog/product_collection')->addCategoryFilter($category)->addAttributeToSort('position','asc'); ?
    – Raphael at Digital Pianism
    Jan 18 '16 at 15:38
  • No, but I think what you did was filter out the category. so it's is only displaying items in that category. what I want is item in all category but sorted in the position the category position as listed in admin
    – wlin
    Jan 18 '16 at 15:51
  • There's one big problem with what you want to achieve. It's the fact that the same product can belong to several categories with a different position. The position itself is stored under the catalog_category_product table if you want to check the structure is very simple but in your case, it makes the solution a little bit complex.
    – Raphael at Digital Pianism
    Jan 18 '16 at 15:56
  • @DigitalPianism I just want to filter it at a level2 ( one directly under root, which each product only have one cat assigned to ). is there a way to do that? with out loop through 2 foreach
    – wlin
    Jan 18 '16 at 15:59
0

Assuming your products are only assigned to one category, I reckon you could do something like this, you may have to make modifications to get it working with your current code, but that gives you a good idea on how to do it:

$sale_products = Mage::getModel('catalog/product')->getCollection()
$sale_products->getSelect()->joinLeft(
'ccp'=>'catalog_category_product',
'main_table.category_id = ccp.category_id and main_table.entity_id = ccp.product_id',
array('ccp.position'));
$sale_products->getSelect()->order('ccp.position asc');
Improve this answer

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.