Anyone who has worked on custom code for tier pricing or customer group pricing has probably considered querying the catalog_product_entity_tier_price database table to retrieve the required pricing information. Custom queries bypass the Magento classes responsible for obtaining and rendering prices. Below I will show an example of why we should stick to native courses whenever possible.
Prerequisites
For testing purposes, I created a test client group named “Luma Club” on the default Magento installation with sample data installed. My test client from the Luma Club client group is Dwight Schrute. There are two cases to analyze: moving from a fixed amount to a discount (the changes in the database surprised me) and move from a discount to a fixed amount (everything is fine).
Changing the customer group price from a fixed amount to a discount percentage
Let’s go initially set a customer group price for Aim Analog Watch. The regular price is $45, so it’s only fair to offer a fixed amount of $40 to Luma Club customers. 😀
Configuring customer group price administration
Front view of product prices for Luma Club customers – fixed amount
Matching data in the catalog_product_entity_tier_price table
So far, so good!
Now let’s go change the group product at a reduced price price from fixed amount to discount percentagefor example 10%.
Group price changed from a fixed amount to a percentage discount (10% discount) 
Front view of group rates set to a discount percentage (10% off)
At this point, one would expect the value column in catalog_product_entity_tier_price to change to 0 or null, but that is not the case! He stays the same and only the percentage column is updated with the new value defined in the admin dashboard.
Table view of group rates set to discount percentage (10% discount)
We’ll take a quick stop here and see how Magento handles data backup for group pricing since I expected the property that is not used to be false (value in this case).
Detective mode activated: recording the price of the customer level/group
What happened in the process of saving the price of the level to have two non-false values that determine the price? Let’s find out! \Magento\Catalog\Model\ResourceModel\Product\Attribute\Backend\GroupPrice\AbstractGroupPrice is the class responsible for save price data in the catalog_product_entity_tier_price table.
public function savePriceData(\Magento\Frame\Data object $priceObject) // case resolved -> discount update
{
$connection = $this->getConnection();
$data = $this->_prepareDataForTable($priceObject, $this->getMainTable()); // $this->getMainTable() = ‘catalog_product_entity_tier_price’
if (!empty($data[$this->getIdFieldName()])) { // $data[‘value_id’]
$Or = $connection->quoteIn($this->getIdFieldName() . ‘ = ?‘, $data[$this->getIdFieldName()]); // $where = “value_id = 3”
disarmed($data[$this->getIdFieldName()]);
$connection->perceupdate($this->getMainTable(), $data, $Or); // $data = [‘value_id’ => 3, ‘percentage_value’ = 20]note that the existing fixed value is not overwritten!
} other {
$connection->insert($this->getMainTable(), $data);
}
back $ this ;
}
It calls \Magento\Framework\Model\ResourceModel\AbstractResource::_prepareDataForTable – here the table columns are checked if they can be nullable (among others).
protected function _prepareDataForTable(Data object $object, $painting)
{
$data = [];
$fields = $this->getConnection()->describeTable($painting);
for each (key_array($fields) as $field) {
if ($object->hasData($field)) {
$field value = $object->get data($field);
if ($field value instance of \Zend_Db_Expr) {
$data[$field] = $field value;
} other {
if (null !== $field value) {
$field value = $this->_prepareTableValueForSave($field value, $fields[$field][‘DATA_TYPE‘]);
$data[$field] = $this->getConnection()->prepareColumnValue($fields[$field], $field value);
} Otherwise (!empty($fields[$field][‘NULLABLE‘])) { // Column ‘value’ is NOT NULLABLE so $data[‘value’] is NOT set to null
$data[$field] = null;
}
}
}
}
back $data;
}
So when the percent_value column is updated, the value remains as is because it is not nullable. But how does Magento know what value to take for calculating the price per level in case value and percent_value are not false?
Magento Logic to get price for customer tier/group
In situations like this, when I’m interested in a particular table, I always like examine the database query log. This way I can know if the choice between value and percentage_value is made before or after querying the database. It happens like this:
Log in as a customer who is part of the customer group for which you have set group pricing. In my case, it’s Dwight Schrute and Luma Club.
Enable database loggingbut without call stack. Keeps the log file smaller and more readable. You will use xdebug later. 😉
bin/magento dev:query-log:enable –include-call-stack=false
Go to product for which you have set the group price. In my case it is Aim Analog Watch with id 36, so I will go to www.magento.loc/catalog/product/view/id/36. When the page loads, all associated database queries will be logged in the var/debug/db.log file.
Disable database logging.
bin/magento dev:query-log:disable
Research for catalog_product_entity_tier_price in var/debug/db.log
The result is:
SELECT
`catalog_product_entity_tier_price`.`id_value` AS `price_id`,
`catalog_product_entity_tier_price`.`site_id`,
`catalog_product_entity_tier_price`.`all_groups`,
`catalog_product_entity_tier_price`.`customer_group_id` AS `customer_group`,
`catalog_product_entity_tier_price`.`value` AS `price`,
`catalog_product_entity_tier_price`.`quantity` AS `price_qty`,
`catalog_product_entity_tier_price`.`percentage_value`,
`catalog_product_entity_tier_price`.`entity_id` AS `product_id`
FROM
`catalog_product_entity_tier_price`
OR
(site_id IN(0, ‘1‘)) AND(entity_id = ‘36‘)
ORDER BY
`quantity` ASC
In Magento, the above data looks like this:
$data =
[
{
“price_id“: “3“,
“website_id“: “0“,
“all_groups“: “0“,
“cust_group“: “4“,
“price“: “40.000000“,
“price_qty“: “1.0000“,
“percentage_value“: “10.00“,
“product_id“: “36“
}
]
And this $data object is passed as an argument to the modifierPriceData method of the \Magento\Catalog\Model\Product\Attribute\Backend\Tierprice class. Here you can see this fixed value of catalog_product_entity_tier_price table i.e. the price is crushed if there is no non-false percentage value.
protected function editPriceData($object, $data)
{
/** @OUR \Magento\Catalog\Model\Product $object */
$data = mother::editPriceData($object, $data);
$price = $object->get the price();
for each ($data as $key => $levelPrice) {
$valuepercentage = $this->getPercentage($levelPrice);
if ($valuepercentage) {
$data[$key][‘price‘] = $price * (1 – $valuepercentage / 100);
$data[$key][‘website_price‘] = $data[$key][‘price‘];
}
}
back $data;
}
A custom SQL query that would search for non-false values or percentage values for a given product ID, customer group ID, and website ID. would give you a bad result for this case!
Changing the customer group price from discount percentage to fixed amount
To cover the two change cases mentioned in the introduction, let’s see what happens when the store admin changes the price of Luma Club to a fixed amount. Does the percentage_value stay the same when the value is updated to the amount of 38.00?
Tabular display of group price changed from discount percentage to fixed amount
percentage_value East nullable thus when updating the row if the value column is the one that changed, the percentage_value column becomes false.
Dwight Schrute would approve
If Dwight Schrute were a Magento developer, I think he would appreciate the time invested in dogged debugging to find out why there are two non-false values in the catalog_product_entity_tier_price table and how Magento calculates the tier price in this case. I hope this was helpful to you too! Let us know if you agree with our opinion of Dwight. 😉
