Magento get products next by price

Question

I need to get a collection of products next by price for specific product in some category. For example: I have product White Shoes, it is located in Shoes category. I need to get next five products, that has higher price than White Shoes in Shoes category, and five products that has lower price.

Thanks for your help!

Answer 1

I had similar task, and here what I've done:

        $customerGroupId = Mage::helper('customer')->getCustomer()->getGroupId();
        $websiteId = Mage::app()->getStore()->getWebsiteId();
        $finalPrice = $product->getFinalPrice();

        $coreResource = Mage::getSingleton('core/resource');
        $adapter = $coreResource->getConnection('catalog_read');
        $prevSelect = $adapter->select()
            ->from(array('i' => $coreResource->getTableName('catalog/product_index_price')), array())
            ->join(
                array('p' => $coreResource->getTableName('catalog/category_product')),
                'p.product_id = i.entity_id',
                array('product_id')
            )
            ->where('p.category_id = ?', $categoryId)
            ->where('i.customer_group_id = ?', $customerGroupId)
            ->where('i.website_id = ?', $websiteId)
            ->where('p.product_id != ?', $product->getId())
            ->where('i.final_price < ?', $finalPrice)
            ->order('i.final_price DESC')
            ->limit(self::PRODUCTS_BLOCK_SIZE);

        $lowerIds = array_reverse($adapter->fetchCol($prevSelect));

        $nextSelect = $adapter->select()
            ->from(array('i' => $coreResource->getTableName('catalog/product_index_price')), array())
            ->join(
                array('p' => $coreResource->getTableName('catalog/category_product')),
                'p.product_id = i.entity_id',
                array('product_id')
            )
            ->where('p.category_id = ?', $categoryId)
            ->where('i.customer_group_id = ?', $customerGroupId)
            ->where('i.website_id = ?', $websiteId)
            ->where('p.product_id != ?', $product->getId())
            ->where('i.final_price > ?', $finalPrice)
            ->order('i.final_price ASC')
            ->limit(self::PRODUCTS_BLOCK_SIZE);

        $higherIds = $adapter->fetchCol($nextSelect);

        $lowerSliced = array_slice($lowerIds, -self::PRODUCTS_BLOCK_SIZE);
        $requiredFromHigher = self::PRODUCTS_BLOCK_SIZE - count($lowerSliced);
        $similarIds = array_merge(
            $lowerSliced,
            array_slice($higherIds, 0, $requiredFromHigher)
        );

        $collection = Mage::getResourceModel('catalog/product_collection')
            ->addAttributeToSelect('name')
            ->addAttributeToSelect('small_image')
            ->addAttributeToSelect('product_url')
            ->addAttributeToFilter('entity_id', array('in' => $similarIds))
            ->setPage(1, self::PRODUCTS_BLOCK_SIZE);