Getting Product Collection Sorted by minimum price...

vikas_0007 · ‎06-17-2016

Summary of Work Environment -

I am working on a website where we have customer and dealers both. Each Dealer can have their own price for a product.

Production collection data is having another duplicate record (CLONING PRODUCT) for each product having price of that seller. For example if master catalog have IPHONE 6S . than 5 dealers who deal in Iphone 6s can have their own prices. Cloning product creates a new product ID related to Seller ID

Task Requirement -

I need to get the category wise product listing having lowest price of dealer. Also need to sort that listing according to lowest price.

what I tried so far -

Currently I can list out all the products having lowest price according to category.

$productCollection = Mage::getResourceModel('catalog/product_collection')
                    ->addAttributeToSelect('sellingprice')
                    ->setStoreId($storeId)
                    ->joinField('category_id', 'catalog/category_product', 'category_id', 'product_id=entity_id', null, 'left')
                    ->addAttributeToFilter('category_id', array('in' => $_POST['category_id']))
                    ->addAttributeToFilter('status', array('eq' => 1))
                    ->addAttributeToFilter('dis_continue', array('eq' => 0));



$productCollection->addAttributeToFilter('seller_id', array('in' => $seller_list));

$productCollection->addExpressionAttributeToSelect(
                    'lowest_price', 'IF(({{special_from_date}}<=now() AND {{special_to_date}}>=now() OR {{special_from_date}} IS NULL AND {{special_price}}>0),{{special_price}},IF({{sellingprice}}>0,{{sellingprice}},{{price}}))', array('special_from_date', 'special_to_date', 'special_price', 'sellingprice', 'price'));


$productCollection->getSelect()->columns('MIN(IF((IF(at_special_from_date.value_id > 0, at_special_from_date.value, at_special_from_date_default.value)<=now() AND IF(at_special_to_date.value_id > 0, at_special_to_date.value, at_special_to_date_default.value)>=now() OR IF(at_special_from_date.value_id > 0, at_special_from_date.value, at_special_from_date_default.value) IS NULL AND at_special_price.value>0),at_special_price.value,IF(at_sellingprice.value>0,at_sellingprice.value,at_price.value))) as l_price')->group('product_name');

Using Group By which groups all the data by Product Name , get MINIMUM of Lowest Price , SORTING that according to LOWEST Price.

PROBLEM : -

As I explained that I am Using GROUP BY Name so that I can have unique products but I am not able to get the PRODUCT ID of associated seller who is having lowest price. I need to get the Seller ID Of having LOWEST PRICE

GROUP BY always Returns the first ROW , but MIN() function gives the lowest of price. First ROW do not have the associated PRODUCT ID of lowest price.....

Getting Product Collection Sorted by minimum price - group by minimum..

Getting Product Collection Sorted by minimum price - group by minimum..