php - Magento : Getting Product Collection Sorted by minimum price

Question

工作环境总结

我正在开发一个既有客户又有经销商的网站。每个经销商都可以有自己的产品价格。

生产集合数据对于具有该卖方价格的每个产品都有另一个重复记录(克隆产品)。例如，如果主目录有 IPHONE 6S 。超过 5 家销售 Iphone 6s 的经销商可以有自己的价格。克隆产品会创建一个与卖家 ID 相关的新产品 ID

要求

我需要获得经销商价格最低的类别明智的产品列表。还需要根据最低价格对该列表进行排序。

我尝试了什么

目前我可以按类别列出所有价格最低的产品。

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

我找出经销商的最低售价、特价、mrp。

使用 Group By 将所有数据按 Product Name 分组，得到 MINIMUM of Lowest Price ，按照 LOWEST Price 排序。

问题

正如我解释的那样，我正在使用 GROUP BY Name 以便我可以拥有独特的产品，但我无法获得价格最低的关联卖家的产品 ID。我需要获得最低价格的卖家 ID

GROUP BY 总是返回第一行，但 MIN() 函数给出最低的价格。第一行没有最低价格的关联产品 ID.....

编辑 - MYSQL 查询

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`

如果在 MAGENTO 有什么方法请帮忙

Answer 1

恐怕我对 Magento 本身不够熟悉，无法直接帮助您编写代码，但更一般地说，这是 SQL SELECT 查询时的常见问题。

分组依据

首先，一个重要的说明:当使用GROUP BY时，查询的SELECT部分中的任何字段不包括在GROUP BY中条款本身可能不合法。结果取决于您的服务器版本和/或 ONLY_FULL_GROUP_BY SQL 模式。

更重要的是，假设您的服务器/配置支持它，选择未包含在 GROUP BY 子句中的字段意味着您从组中的任意行获取值，不是第一行 。来自 MySQL Handling of GROUP BY page在 MySQL 文档中:

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.

选择组内的特定行

实现您正在寻找的行为的一种方法对我来说一直很有效，那就是使用计数器和子查询来排序和过滤您的子组。这为您提供了比 GROUP BY 更高级别的控制(尽管您确实牺牲了一些性能):

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;

要更清楚地了解其工作原理，请在不使用 HAVING 子句的情况下运行查询。你会得到这样的结果:

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

b 列显示了 @num 变量的值，该变量在一组同名产品中的每一行递增，并在每次产品名称时重置当前行中的名称不等于最后一个的名称。添加 HAVING b=1 子句意味着我们只获得每组中最便宜的产品。

在子查询中使用 ORDER BY 时的潜在问题!

当我上次使用 MySQL 时，上述解决方案会起作用(我想现在仍然如此)。但是，这实际上不是标准的 SQL 行为。更严格遵守标准的数据库服务器(如 MariaDB)will ignore an ORDER BY clause contained within a sub-query ，除非子查询还具有 LIMIT 子句。因此，如果您使用的是 MariaDB，则需要通过包含 LIMIT 来强制服务器遵守 ORDER BY。我以前使用过的一种技术(如上一个链接的评论中所述)是指定一个非常大的 LIMIT 值:

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;

希望对您有所帮助。