title: Magento : Getting Product Collection Sorted by minimum price
body: <p><strong>Summary of Work Environment</strong></p>
  
  <p>I am working on a website where we have customer and dealers both. Each Dealer can have their own price for a product.</p>
  
  <p>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</p>
  
  <p><strong>Requirement</strong></p>
  
  <p>I need to get the category wise product listing having lowest price of dealer. Also need to sort that listing according to lowest price.</p>
  
  <p><strong>what I tried</strong></p>
  
  <p>Currently I can list out all the products having lowest price according to category.</p>
  
  <pre><code>$productCollection = Mage::getResourceModel('catalog/product_collection')
                      -&gt;addAttributeToSelect('sellingprice')
                      -&gt;setStoreId($storeId)
                      -&gt;joinField('category_id', 'catalog/category_product', 'category_id', 'product_id=entity_id', null, 'left')
                      -&gt;addAttributeToFilter('category_id', array('in' =&gt; $_POST['category_id']))
                      -&gt;addAttributeToFilter('status', array('eq' =&gt; 1))
                      -&gt;addAttributeToFilter('dis_continue', array('eq' =&gt; 0));
  
  
  
  $productCollection-&gt;addAttributeToFilter('seller_id', array('in' =&gt; $seller_list));
  
  $productCollection-&gt;addExpressionAttributeToSelect(
                      'lowest_price', 'IF(({{special_from_date}}&lt;=now() AND {{special_to_date}}&gt;=now() OR {{special_from_date}} IS NULL AND {{special_price}}&gt;0),{{special_price}},IF({{sellingprice}}&gt;0,{{sellingprice}},{{price}}))', array('special_from_date', 'special_to_date', 'special_price', 'sellingprice', 'price'));
  
  
  $productCollection-&gt;getSelect()-&gt;columns('MIN(IF((IF(at_special_from_date.value_id &gt; 0, at_special_from_date.value, at_special_from_date_default.value)&lt;=now() AND IF(at_special_to_date.value_id &gt; 0, at_special_to_date.value, at_special_to_date_default.value)&gt;=now() OR IF(at_special_from_date.value_id &gt; 0, at_special_from_date.value, at_special_from_date_default.value) IS NULL AND at_special_price.value&gt;0),at_special_price.value,IF(at_sellingprice.value&gt;0,at_sellingprice.value,at_price.value))) as l_price')-&gt;group('product_name');
  </code></pre>
  
  <p>I find out lowest of selling price , special price , mrp of a dealer.</p>
  
  <p>Using Group By which groups all the data by Product Name , get MINIMUM of Lowest Price , SORTING that according to LOWEST Price.</p>
  
  <p><strong>PROBLEM</strong></p>
  
  <p>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</p>
  
  <p>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.....</p>
  
  <p><strong>EDIT - MYSQL QUERY</strong></p>
  
  <pre><code>SELECT `e`.*,
  `at_category_id`.`category_id`,
  IF(
    at_status.value_id &gt; 0,
    at_status.value,
    at_status_default.value
  ) AS `status`,
  `at_dis_continue`.`value` AS `dis_continue`,
  `at_seller_id`.`value` AS `seller_id`,
  `at_popular_product`.`value` AS `popular_product`,
  IF(
    at_special_from_date.value_id &gt; 0,
    at_special_from_date.value,
    at_special_from_date_default.value
  ) AS `special_from_date`,
  IF(
    at_special_to_date.value_id &gt; 0,
    at_special_to_date.value,
    at_special_to_date_default.value
  ) AS `special_to_date`,
  `at_special_price`.`value` AS `special_price`,
  `at_sellingprice`.`value` AS `sellingprice`,
  `at_price`.`value` AS `price`,
  IF(
    (
      IF(
        at_special_from_date.value_id &gt; 0,
        at_special_from_date.value,
        at_special_from_date_default.value
      ) &lt;= NOW() AND IF(
        at_special_to_date.value_id &gt; 0,
        at_special_to_date.value,
        at_special_to_date_default.value
      ) &gt;= NOW() OR IF(
        at_special_from_date.value_id &gt; 0,
        at_special_from_date.value,
        at_special_from_date_default.value
      ) IS NULL AND at_special_price.value &gt; 0
    ),
    at_special_price.value,
    IF(
      at_sellingprice.value &gt; 0,
      at_sellingprice.value,
      at_price.value
    )
  ) AS `lowest_price`,
  `at_name`.`value` AS `name`,
  `at_name`.`value` AS `product_name`,
  MIN(
    IF(
      (
        IF(
          at_special_from_date.value_id &gt; 0,
          at_special_from_date.value,
          at_special_from_date_default.value
        ) &lt;= NOW() AND IF(
          at_special_to_date.value_id &gt; 0,
          at_special_to_date.value,
          at_special_to_date_default.value
        ) &gt;= NOW() OR IF(
          at_special_from_date.value_id &gt; 0,
          at_special_from_date.value,
          at_special_from_date_default.value
        ) IS NULL AND at_special_price.value &gt; 0
      ),
      at_special_price.value,
      IF(
        at_sellingprice.value &gt; 0,
        at_sellingprice.value,
        at_price.value
      )
    )
  ) AS `l_price`
  FROM
    `catalog_product_entity` AS `e`
  LEFT JOIN
    `catalog_category_product` AS `at_category_id` ON(
      at_category_id.`product_id` = e.entity_id
    )
  INNER JOIN
    `catalog_product_entity_int` AS `at_status_default` ON(
      `at_status_default`.`entity_id` = `e`.`entity_id`
    ) AND(
      `at_status_default`.`attribute_id` = '96'
    ) AND `at_status_default`.`store_id` = 0
  LEFT JOIN
    `catalog_product_entity_int` AS `at_status` ON(
      `at_status`.`entity_id` = `e`.`entity_id`
    ) AND(`at_status`.`attribute_id` = '96') AND(`at_status`.`store_id` = 1)
  INNER JOIN
    `catalog_product_entity_int` AS `at_dis_continue` ON(
      `at_dis_continue`.`entity_id` = `e`.`entity_id`
    ) AND(
      `at_dis_continue`.`attribute_id` = '261'
    ) AND(`at_dis_continue`.`store_id` = 0)
  INNER JOIN
    `catalog_product_entity_varchar` AS `at_seller_id` ON(
      `at_seller_id`.`entity_id` = `e`.`entity_id`
    ) AND(
      `at_seller_id`.`attribute_id` = '134'
    ) AND(`at_seller_id`.`store_id` = 0)
  INNER JOIN
    `catalog_product_entity_varchar` AS `at_popular_product` ON(
      `at_popular_product`.`entity_id` = `e`.`entity_id`
    ) AND(
      `at_popular_product`.`attribute_id` = '1078'
    ) AND(
      `at_popular_product`.`store_id` = 0
    )
  LEFT JOIN
    `catalog_product_entity_datetime` AS `at_special_from_date_default` ON(
      `at_special_from_date_default`.`entity_id` = `e`.`entity_id`
    ) AND(
      `at_special_from_date_default`.`attribute_id` = '77'
    ) AND `at_special_from_date_default`.`store_id` = 0
  LEFT JOIN
    `catalog_product_entity_datetime` AS `at_special_from_date` ON(
      `at_special_from_date`.`entity_id` = `e`.`entity_id`
    ) AND(
      `at_special_from_date`.`attribute_id` = '77'
    ) AND(
      `at_special_from_date`.`store_id` = 1
    )
  LEFT JOIN
    `catalog_product_entity_datetime` AS `at_special_to_date_default` ON(
      `at_special_to_date_default`.`entity_id` = `e`.`entity_id`
    ) AND(
      `at_special_to_date_default`.`attribute_id` = '78'
    ) AND `at_special_to_date_default`.`store_id` = 0
  LEFT JOIN
    `catalog_product_entity_datetime` AS `at_special_to_date` ON(
      `at_special_to_date`.`entity_id` = `e`.`entity_id`
    ) AND(
      `at_special_to_date`.`attribute_id` = '78'
    ) AND(
      `at_special_to_date`.`store_id` = 1
    )
  LEFT JOIN
    `catalog_product_entity_decimal` AS `at_special_price` ON(
      `at_special_price`.`entity_id` = `e`.`entity_id`
    ) AND(
      `at_special_price`.`attribute_id` = '76'
    ) AND(`at_special_price`.`store_id` = 0)
  LEFT JOIN
    `catalog_product_entity_decimal` AS `at_sellingprice` ON(
      `at_sellingprice`.`entity_id` = `e`.`entity_id`
    ) AND(
      `at_sellingprice`.`attribute_id` = '143'
    ) AND(`at_sellingprice`.`store_id` = 0)
  LEFT JOIN
    `catalog_product_entity_decimal` AS `at_price` ON(
      `at_price`.`entity_id` = `e`.`entity_id`
    ) AND(`at_price`.`attribute_id` = '75') AND(`at_price`.`store_id` = 0)
  LEFT JOIN
    `catalog_product_entity_varchar` AS `at_name` ON(
      `at_name`.`entity_id` = `e`.`entity_id`
    ) AND(`at_name`.`attribute_id` = '71') AND(`at_name`.`store_id` = 0)
  WHERE
    (
      at_category_id.category_id IN('119')
    ) AND(
      IF(
        at_status.value_id &gt; 0,
        at_status.value,
        at_status_default.value
      ) = 1
    ) AND(at_dis_continue.value = 0) AND(at_seller_id.value IN('1065')) AND(
      at_popular_product.value IN('Yes',
      'No')
    )
  GROUP BY
    `product_name`
  </code></pre>
  
  <p><strong>Please help if there is any way IN MAGENTO</strong> </p>