当前位置:Gxlcms > PHP教程 > Magento 2.2.5和2.2.6的问题产品设置特价又删除问题分析与解决

Magento 2.2.5和2.2.6的问题产品设置特价又删除问题分析与解决

时间:2021-07-01 10:21:17 帮助过:26人阅读

Magento是一款专业开源电子商务平台,采用php进行开发,使用Zend Framework框架。然而网上有网友说Magento 2.2.5和2.2.6的bug 产品设置special price又删除后价格排序有误,小编把问题给总结了一下。

一、问题描述:版本2.2.5和2.2.6均有此问题,为Magento2的系统bug。为产品设置special price,比如0.5元,这个产品按价格由低到高排序时,排在首位;然后删去special price,保存,重建索引后,此产品显示的价格是正确的,但是即使有显示的价格比它还低的,按价格排序这个产品依然排在首位。

二、问题定位:
1、价格排序有问题,肯定是数据保存有问题。先在数据库里找与价格有关的数据表,catalog_product_index_price 和 catalog_category_entity_decimal 放在一起看,发现有问题的产品中,final_price max_price min_price的值都为0,改为和price的值一样时,价格排序正确。有此确定有问题的表出在 catalog_product_index_price。

2、确定产品保存时 catalog_product_index_price 这张表的final price的值为什么会被保存为0。产品保存与 vendor/magento/module-catalog/Controller/Adminhtml/Product/Save.php 这个文件有关,断点调试未能发现保存 catalog_product_index_price 的操作。后经同事提醒,产品保存后会进行 reindex 的操作,简单测试发现 catalog_product_index_price 表确实是 reindex 时保存。

3、reindex时,断点调试获取最终插入数据表的 SQL语句。只要分析SQL语句,就能确定问题的来源。reindex的起始点在文件 vendor/magento/module-indexer/Console/Command/IndexerReindexCommand.php,但是indexer有很多,要准确找到价格的reindex操作,需要花费很大的努力和耐心。最终找到文件 vendor/magento/module-catalog/Model/ResourceModel/Product/Indexer/Price/SimpleProductPrice.php ,从中可以获取插入临时表的SQL语句 $query 变量,复制SQL语句,放到Navicat中执行,可以发现要插入的数据中,final_price为0,下面主要分析这个SQL语句。

4、如下面展示的SQL语句所示,这个语句很长而且很复杂,要分析清楚它内部的逻辑结构,一是没有相应的分析复杂SQL语句的经验而很是犯难,二是非常耗费时间。后经同事演示和提醒,发现分析这个SQL语句也没有想象中那么难,因为再复杂的语句都是由基本语句组合而成,不过其中加上了多层嵌套,if语句的判断让它看起来非常复杂罢了,基本的分析方法还是:“分而治之,各个击破”,这句中国的成语包含了丰富的哲理智慧,我发现现实生活中的问题都可以用这套理论去解决。下面详细说明如何“分而治之”,又如何“各个击破”。
分而治之,就是把这段SQL语句中无关的东西撇去,只关注核心的数据。final_price有问题,我们就看它的final_price是怎么查出来的,看黄色背景的部分。它最外面是一层IFNULL判断,它的意思是第一个参数如果为真,那么返回它本身,否则返回第二个参数,final_price现在为0,那么就可以判断,第一个参数一定为FALSE。但是第一个参数是很长一大段,我们又来仔细分析它,因为嵌套很多,不容易看清楚,我们这时要引入外面的工具,把它格式化,层次结构分明一点,并且可以看到括弧的开头和结束。PHPStorm是一个很好用的工具,把这段代码都复制进去,并且格式化后再来分析。
各个击破,因为代码中涉及到值的对比和运算,所以我们要学会将这些不同的值打印显示出来,算出来后一个个拿来进行比较分析。打印(查询)出来也不难,模仿它本身就好了,用IFNULL或IF语句,就可以查询出来。

5、经过上面的分析,最终确定,问题出在一个叫 special_from_date 的产品属性上。当保存special_price 时,会将这个属性的值也保存起来,但是删除 special_price 时却没有删除,遗留的数据会影响上面SQL语句的判断,从而导致final_price的值变为0。

6、定位了问题所在后,就是最终的解决。覆写 vendor/magento/module-catalog/Observer/SetSpecialPriceStartDate.php 文件的 execute方法,改为如下。它的作用就是,当有 special_price时,就保存special_from_date,没有special_price时,就删除speical_from_date。更新代码后,问题解决。

 /**
* Set the current date to Special Price From attribute if it empty
*
* If special price was deleted, Special Price From attribute will be deleted
*
* (Important! Otherwise indexer would be confused)
*
* @param \Magento\Framework\Event\Observer $observer
* @return $this
*/
public function execute(\Magento\Framework\Event\Observer $observer)
{
  /** @var $product \Magento\Catalog\Model\Product */
  $product = $observer->getEvent()->getProduct();
  if ($product->getSpecialPrice() && !$product->getSpecialFromDate()) {
    $product->setData('special_from_date', $this->localeDate->date());
  } elseif (!$product->getSpecialPrice() && $product->getSpecialFromDate()) {
    $product->unsetData('special_from_date');
  }

  return $this;
}

php视频教程

三、总结:经此,定位问题,解决问题的能力又获得一丁点的提升。主要是学会了对复杂SQL语句的初步分析,知道了IFNULL、IF、LEAST函数的使用,AND比OR的优先级要高的事实。

SQL语句:

 INSERT INTO `catalog_product_index_price_temp` SELECT `e`.`entity_id`, `cg`.`customer_group_id`, `pw`.`website_id`, IF(IFNULL(tas_tax_class_id.value_id, -1) > 0, tas_tax_class_id.value, tad_tax_class_id.value) AS `tax_class_id`, IFNULL((ta_price.value), 0) AS `price`, IFNULL((LEAST(ta_price.value, IF(ta_special_price.value IS NOT NULL AND IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL OR DATE(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value)) <= cwd.website_date AND IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL OR DATE(IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value)) >= cwd.website_date, ta_special_price.value, ~0), IFNULL((IF(tier_price_1.value_id is NULL AND tier_price_2.value_id is NULL AND tier_price_3.value_id is NULL AND tier_price_4.value_id is NULL, NULL, LEAST(IFNULL((IF(tier_price_1.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_1.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_1.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_2.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_2.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_2.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_3.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_3.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_3.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_4.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_4.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_4.value * cwd.rate, 4))), ~0)))), ~0))), 0) AS `final_price`, IFNULL((LEAST(ta_price.value, IF(ta_special_price.value IS NOT NULL AND IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL OR DATE(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value)) <= cwd.website_date AND IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL OR DATE(IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value)) >= cwd.website_date, ta_special_price.value, ~0), IFNULL((IF(tier_price_1.value_id is NULL AND tier_price_2.value_id is NULL AND tier_price_3.value_id is NULL AND tier_price_4.value_id is NULL, NULL, LEAST(IFNULL((IF(tier_price_1.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_1.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_1.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_2.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_2.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_2.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_3.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_3.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_3.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_4.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_4.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_4.value * cwd.rate, 4))), ~0)))), ~0))), 0) AS `min_price`, IFNULL((LEAST(ta_price.value, IF(ta_special_price.value IS NOT NULL AND IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL OR DATE(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value)) <= cwd.website_date AND IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL OR DATE(IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value)) >= cwd.website_date, ta_special_price.value, ~0), IFNULL((IF(tier_price_1.value_id is NULL AND tier_price_2.value_id is NULL AND tier_price_3.value_id is NULL AND tier_price_4.value_id is NULL, NULL, LEAST(IFNULL((IF(tier_price_1.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_1.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_1.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_2.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_2.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_2.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_3.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_3.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_3.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_4.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_4.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_4.value * cwd.rate, 4))), ~0)))), ~0))), 0) AS `max_price`, IF(tier_price_1.value_id is NULL AND tier_price_2.value_id is NULL AND tier_price_3.value_id is NULL AND tier_price_4.value_id is NULL, NULL, LEAST(IFNULL((IF(tier_price_1.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_1.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_1.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_2.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_2.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_2.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_3.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_3.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_3.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_4.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_4.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_4.value * cwd.rate, 4))), ~0))) AS `tier_price` FROM `catalog_product_entity` AS `e`
CROSS JOIN `customer_group` AS `cg`
INNER JOIN `catalog_product_website` AS `pw` ON pw.product_id = e.entity_id
INNER JOIN `catalog_product_index_website` AS `cwd` ON pw.website_id = cwd.website_id
LEFT JOIN `catalog_product_index_tier_price` AS `tp` ON tp.entity_id = e.entity_id AND tp.customer_group_id = cg.customer_group_id AND tp.website_id = pw.website_id
LEFT JOIN `catalog_product_entity_tier_price` AS `tier_price_1` ON tier_price_1.row_id = e.row_id AND tier_price_1.all_groups = 0 AND tier_price_1.customer_group_id = cg.customer_group_id AND tier_price_1.qty = 1 AND tier_price_1.website_id = 0
LEFT JOIN `catalog_product_entity_tier_price` AS `tier_price_2` ON tier_price_2.row_id = e.row_id AND tier_price_2.all_groups = 0 AND tier_price_2.customer_group_id = cg.customer_group_id AND tier_price_2.qty = 1 AND tier_price_2.website_id = pw.website_id
LEFT JOIN `catalog_product_entity_tier_price` AS `tier_price_3` ON tier_price_3.row_id = e.row_id AND tier_price_3.all_groups = 1 AND tier_price_3.customer_group_id = 0 AND tier_price_3.qty = 1 AND tier_price_3.website_id = 0
LEFT JOIN `catalog_product_entity_tier_price` AS `tier_price_4` ON tier_price_4.row_id = e.row_id AND tier_price_4.all_groups = 1 AND tier_price_4.customer_group_id = 0 AND tier_price_4.qty = 1 AND tier_price_4.website_id = pw.website_id
LEFT JOIN `catalog_product_entity_int` AS `tad_tax_class_id` ON tad_tax_class_id.row_id = e.row_id AND tad_tax_class_id.attribute_id = 149 AND tad_tax_class_id.store_id = 0
LEFT JOIN `catalog_product_entity_int` AS `tas_tax_class_id` ON tas_tax_class_id.row_id = e.row_id AND tas_tax_class_id.attribute_id = 149 AND tas_tax_class_id.store_id = cwd.default_store_id
INNER JOIN `catalog_product_entity_int` AS `tad_status` ON tad_status.row_id = e.row_id AND tad_status.attribute_id = 97 AND tad_status.store_id = 0
LEFT JOIN `catalog_product_entity_int` AS `tas_status` ON tas_status.row_id = e.row_id AND tas_status.attribute_id = 97 AND tas_status.store_id = cwd.default_store_id
LEFT JOIN `catalog_product_entity_decimal` AS `ta_price` ON ta_price.row_id = e.row_id AND ta_price.attribute_id = 77 AND ta_price.store_id = 0
LEFT JOIN `catalog_product_entity_decimal` AS `ta_special_price` ON ta_special_price.row_id = e.row_id AND ta_special_price.attribute_id = 78 AND ta_special_price.store_id = 0
LEFT JOIN `catalog_product_entity_datetime` AS `tad_special_from_date` ON tad_special_from_date.row_id = e.row_id AND tad_special_from_date.attribute_id = 79 AND tad_special_from_date.store_id = 0
LEFT JOIN `catalog_product_entity_datetime` AS `tas_special_from_date` ON tas_special_from_date.row_id = e.row_id AND tas_special_from_date.attribute_id = 79 AND tas_special_from_date.store_id = cwd.default_store_id
LEFT JOIN `catalog_product_entity_datetime` AS `tad_special_to_date` ON tad_special_to_date.row_id = e.row_id AND tad_special_to_date.attribute_id = 80 AND tad_special_to_date.store_id = 0
LEFT JOIN `catalog_product_entity_datetime` AS `tas_special_to_date` ON tas_special_to_date.row_id = e.row_id AND tas_special_to_date.attribute_id = 80 AND tas_special_to_date.store_id = cwd.default_store_id WHERE ((IF(IFNULL(tas_status.value_id, -1) > 0, tas_status.value, tad_status.value) = 1) AND (e.type_id = 'simple') AND (e.entity_id BETWEEN 2 AND 21)) AND (e.created_in <= '1546224120') AND (e.updated_in > '1546224120')

mysql视频教程

以上就是Magento 2.2.5和2.2.6的问题产品设置特价又删除问题分析与解决的详细内容,更多请关注Gxlcms其它相关文章!

人气教程排行