Magento : Getting Product Collection Sorted by minimum price

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.....

EDIT - MYSQL QUERY

SELECT `e`.*,
`at_category_id`.`category_id`,
IF(
  at_status.value_id > 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 > 0,
  at_special_from_date.value,
  at_special_from_date_default.value
) AS `special_from_date`,
IF(
  at_special_to_date.value_id > 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 > 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 `lowest_price`,
`at_name`.`value` AS `name`,
`at_name`.`value` AS `product_name`,
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`
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 > 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`

Please help if there is any way IN MAGENTO

Answer 1

I'm afraid I'm not familiar enough with Magento itself to help directly with your code, but, more generally speaking, this is a common question when it comes to SQL SELECT queries.

GROUP BY

Firstly, an important clarification: When using GROUP BY, any fields in the SELECT part of the query not included in the GROUP BY clause itself may not be legal. The outcome depends on your server version and/or the ONLY_FULL_GROUP_BY SQL mode.

More importantly, assuming your server/configuration supports it, selecting fields not included in the GROUP BY clause means you get a value from an arbitrary row in the group, not the first row. From the MySQL Handling of GROUP BY page in the MySQL documentation:

In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate, which is probably not what you want.

Selecting specific rows within groups

One way of achieving the behaviour you're looking for that has always worked well for me is by using counters and sub-queries to order and filter your sub-groups. This gives you a greater level of control than a GROUP BY (although you do make some performance sacrifices):

SELECT @num := IF(products_name=@last_products_name, @num + 1, 1) b, (@last_products_name := products_name) AS last_pname, t1.*
FROM (
    SELECT p.products_id, p.products_name, p.selling_price
    FROM products p
    WHERE p.category_id = 123
    ORDER BY p.products_name,
    p.selling_price ASC
) t1, (SELECT @num := 0, @last_products_name := 0) d
HAVING b=1;

To understand more clearly how this works, run the query without the HAVING clause. You get a result like this:

+------+------------+-------------+---------------+---------------+
| b    | last_pname | products_id | products_name | selling_price |
+------+------------+-------------+---------------+---------------+
|    1 | Bar        |           8 | Bar           |          5.00 |
|    2 | Bar        |           2 | Bar           |         12.00 |
|    3 | Bar        |           4 | Bar           |         14.00 |
|    1 | Fizz       |           3 | Fizz          |         30.00 |
|    2 | Fizz       |           5 | Fizz          |         70.00 |
|    3 | Fizz       |           7 | Fizz          |        100.00 |
|    1 | Foo        |           1 | Foo           |         10.00 |
|    2 | Foo        |           6 | Foo           |         18.00 |
+------+------------+-------------+---------------+---------------+

The b column shows the value of the @num variable, which is incremented for each row in a group of identically named products, and reset each time the product name in the current row is not equal to the name of the last one. Adding the HAVING b=1 clause means we only get the cheapest product in each group.

A potential gotcha when using ORDER BY in sub-queries!

When I last used MySQL, the above solution would work (and I imagine that is still true now). However, this is not actually standard SQL behaviour. Database servers which adhere more strictly to the standard (such as MariaDB) will ignore an ORDER BY clause contained within a sub-query, unless the sub-query also features a LIMIT clause. Therefore, if you are using MariaDB, you need to force the server to honour the ORDER BY by including a LIMIT. A technique I have used before (as described in a comment on the previous link) is to specify a very large LIMIT value:

SELECT @num := IF(products_name=@last_products_name, @num + 1, 1) b, (@last_products_name := products_name) AS last_pname, t1.*
FROM (
    SELECT p.products_id, p.products_name, p.selling_price
    FROM products p
    WHERE p.category_id = 123
    ORDER BY p.products_name,
    p.selling_price ASC
    LIMIT 18446744073709551615 -- LIMIT clause forces sub-query ORDER BY
) t1, (SELECT @num := 0, @last_products_name := 0) d
HAVING b=1;

I hope that helps.