Magento Product Collection GROUP BY minimum price having Lowest Price Product ID?

Question

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

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

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');

I find out lowest of selling price , special price , mrp of a dealer.

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

Yosra Hamza Yosra Hamza · Accepted Answer · 2016-06-16T10:40:56

You can try to get all the products ordered by the price without using min, this will make the first row of each product be the one with min price, then use group by in the outer select like this:

Select * from ( 
   Select product.id, product_name, price, .... 
          //your query with joins
          order by price
   )
group by product.id

Sorry for not giving a Magento code, as I have a basic knowledge with it

Magento Product Collection GROUP BY minimum price having Lowest Price Product ID?

1 Answers