首页 文章

Magento：获取产品系列按最低价格排序

提问于
浏览 次
16

Summary of Work Environment

我正在一个我们有客户和经销商的网站上工作 . 每个经销商都可以为产品定价 .

生产环境 收集数据具有另一个具有该卖方价格的产品的重复记录（克隆产品） . 例如，如果主目录有IPHONE 6S . 超过5个交易Iphone 6s的经销商可以有自己的价格 . 克隆产品会创建与卖家ID相关的新产品ID

Requirement

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

what I tried

目前我可以根据类别列出所有价格最低的产品 .

$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按产品名称对所有数据进行分组，获得最低价格的MINIMUM，按照LOWEST Price分类 .

PROBLEM

正如我解释的那样，我正在使用GROUP BY Name，这样我才能拥有独特的产品，但我无法获得价格最低的相关卖家的PRODUCT ID . 我需要获得最低价格的卖家ID

GROUP BY总是返回第一个ROW，但MIN（）函数给出最低的价格 . First ROW没有相关的最低价格的PRODUCT ID .....

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

php mysql sorting magento

1 回答

  • 5

    我对Magento本身并不熟悉直接帮助您的代码，但更一般地说，这是SQL SELECT 查询的一个常见问题 .

    GROUP BY

    首先，一个重要的说明：当使用 GROUP BY 时，查询 SELECT 部分中未包含在 GROUP BY 子句中的任何字段可能都不合法 . 结果取决于您的服务器版本和/或 ONLY_FULL_GROUP_BY SQL模式 .

    更重要的是，假设您的服务器/配置支持它，选择 GROUP BY 子句中未包含的字段意味着您从组中的任意行获取值 not the first row . 从MySQL文档中的MySQL Handling of GROUP BY page：

    在这种情况下，服务器可以自由选择每个组中的任何值，因此除非它们相同，否则所选的值是不确定的，这可能不是您想要的 .

    选择组内的特定行

    实现您正在寻找的行为的一种方法一直适用于我，方法是使用计数器和子查询来订购和过滤您的子组 . 这为您提供了比 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;

    我希望有所帮助 .

    回复于

相关问题