When building a product collection, is there a way to only include products with a category sort position greater/less/is than (x)?

I've tried searching but couldn't find a case of where anyone had asked this before.

$collection = Mage::getModel('catalog/product')->getCollection()
    ->addAttributeToFilter('visibility', array('neq' => 1))
    ->addAttributeToSort('price', 'asc')

In the above product collection, we only want to include products if their sort position in the category is 0. We are forcing a large amount of products to appear at the end of the category by setting their position to 1000.

I suppose I could create a custom attribute with something like a Yes/No value and set all the products to Yes that we want to include in this product collection including it with a addAttributeToFilter but was hoping for a less fiddly/time-consuming way if possible.

  • Had you time to test my suggestion? I'm bit piss** off b/c of downvote and just want to have some feedback. (For me this works with flat table on and off)
    – sv3n
    Commented Feb 28, 2018 at 19:05
  • 1
    Actually, sorry for delay, we were literally playing around with this and had to make some extra customisation to get it to what we want and I was impressed with your solution, at the time of you answering, I was almost certain this was what we needed but just needed some time to work with it (which we have now). I can assure you that I didn't downvote your answer below and have no idea why someone would have. I am just going to check what we implemented yesterday to your answer.
    – zigojacko
    Commented Mar 1, 2018 at 8:35
  • 1
    I know that you haven't downvoted - you were not online at this time :) Nice to to help you. And thanks for this question ... this is useful for us too :)
    – sv3n
    Commented Mar 1, 2018 at 10:29

1 Answer 1


When you check items data, you'll find something like this 'cat_index_position' => '0'. This data is added through an INNER JOIN 'catalog_category_product_index' AS 'cat_index' and isn't an product EAV attribute, so addAttributeToFilter('cat_index_position', 0) will not work, but you can do it in this way:

$collection->getSelect()->where('cat_index.position = ?', 0);

Edit: you have call this before load() the collection ...

  • Any reasons for downvote?
    – sv3n
    Commented Feb 27, 2018 at 19:06
  • Not sure but I didn't downvote it. I replied to your comment above on my question.
    – zigojacko
    Commented Mar 1, 2018 at 8:36
  • I upvoted and marked as correct answer. We used exactly what you suggested to do what we were after, amongst the rest of the product collection e.g. $productColl->getSelect()->where('cat_index.position = ?', 0); - Thanks!
    – zigojacko
    Commented Mar 1, 2018 at 8:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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