Why Not to Write a Custom Query for Getting Tier Price

Anyone who has worked on custom code for tier prices or customer group prices has likely considered querying the catalog_product_entity_tier_price database table to retrieve the required price information. Custom querying bypasses Magento’s classes responsible for price getting and rendering. Below, I’ll show an example of why we should stick to native classes whenever possible.

Prerequisites

For testing purposes I’ve created a test customer group named “Luma Club” on the default Magento installation with sample data installed. My test customer from the Luma Club customer group is Dwight Schrute. There are two cases to analyze: change from fixed amount to discount (database changes caught me by surprise) and change from discount to fixed amount (all good).

Changing Customer Group Price from Fixed Amount to Discount Percent

1. Let’s initially set a customer group price to Aim Analog Watch. The regular price is $45, so it’s fair to offer a fixed amount of $40 to Luma Club customers. 😀

   

   
   

   

   
   

   

   So far so good!

2. Now let’s change the discounted group product price from fixed amount to discount percentage, for example 10%.

   

   

   
   

   At this moment one would expect that value column in catalog_product_entity_tier_price will change to 0 or null, but it doesn’t! It stays the same and only percentage column is updated to the new value defined in admin dashboard.
   

   

We’ll take a little stop here and take a look how Magento handles saving data for group pricing since I was expecting that property which is not used is falsy (value in this case).

Detective Mode Enabled: Saving of Tier/Customer Group Price

What has happend in the process of saving tier price to have two non falsy values that determine price? Let’s find out! \Magento\Catalog\Model\ResourceModel\Product\Attribute\Backend\GroupPrice\AbstractGroupPrice is the class responsible for saving price data in the catalog_product_entity_tier_price table.

public function savePriceData(\Magento\Framework\DataObject $priceObject)  // case fixed -> 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']
        $where = $connection->quoteInto($this->getIdFieldName() . ' = ?', $data[$this->getIdFieldName()]);  // $where = "value_id = 3" 
        unset($data[$this->getIdFieldName()]);
        $connection->perceupdate($this->getMainTable(), $data, $where);  //  $data = ['value_id' => 3, 'percentage_value' = 20], notice that existing fixed value is not overwritten!
    } else {
        $connection->insert($this->getMainTable(), $data);
    }

    return $this;
}

It calls \Magento\Framework\Model\ResourceModel\AbstractResource::_prepareDataForTable – here, the table columns are checked if they can be nullable (among other things).

    protected function _prepareDataForTable(DataObject $object, $table)
    {
        $data = [];
        $fields = $this->getConnection()->describeTable($table);
        foreach (array_keys($fields) as $field) {
            if ($object->hasData($field)) {
                $fieldValue = $object->getData($field);
                if ($fieldValue instanceof \Zend_Db_Expr) {
                    $data[$field] = $fieldValue;
                } else {
                    if (null !== $fieldValue) {
                        $fieldValue = $this->_prepareTableValueForSave($fieldValue, $fields[$field]['DATA_TYPE']);
                        $data[$field] = $this->getConnection()->prepareColumnValue($fields[$field], $fieldValue);
                    } elseif (!empty($fields[$field]['NULLABLE'])) {  // 'value' column is NOT NULLABLE so $data['value'] is NOT set to null
                        $data[$field] = null;
                    }
                }
            }
        }
        return $data;
    }

So when the percentage_value column is updated the value is left as it is since it’s not nullable. But how does Magento know which value to take for tier price calculation in case when both value and percentage_value are non falsy?

Magento Logic for Getting Tier/Customer Group Price

In situations like this, when I’m interested in one particular table, I always like to examine the database query log. This way, I get to find out if the choice between value and percentage_value is done before or after querying the database. It goes like this:

1. Log in as customer who is customer group for which you have set group pricing. In my case it’s Dwight Schrute and Luma Club.

2. Enable database logging, but without call stack. Keeps the log file smaller and more readable. You’re going to use xdebug later. 😉

   bin/magento dev:query-log:enable --include-call-stack=false

3. Go to product for which you have set the group price. In my case it’s Aim Analog Watch with id 36 so I’ll go to www.magento.loc/catalog/product/view/id/36. On page load all related database queries will be saved to var/debug/db.log file.

4. Disable database logging.

   bin/magento dev:query-log:disable

5. Search for catalog_product_entity_tier_price in var/debug/db.log

Result is:

 SELECT
    `catalog_product_entity_tier_price`.`value_id` AS `price_id`,
    `catalog_product_entity_tier_price`.`website_id`,
    `catalog_product_entity_tier_price`.`all_groups`,
    `catalog_product_entity_tier_price`.`customer_group_id` AS `cust_group`,
    `catalog_product_entity_tier_price`.`value` AS `price`,
    `catalog_product_entity_tier_price`.`qty` 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`
WHERE
    (website_id IN(0, '1')) AND(entity_id = '36')
ORDER BY
    `qty` ASC

In Magento 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 argument to the modifyPriceData method of \Magento\Catalog\Model\Product\Attribute\Backend\Tierprice class. Here you can see that fixed value from the catalog_product_entity_tier_price table, i.e. price is overwritten if there’s non falsy percentage_value.

protected function modifyPriceData($object, $data)
{
    /** @var \Magento\Catalog\Model\Product $object */
    $data = parent::modifyPriceData($object, $data);
    $price = $object->getPrice();

    foreach ($data as $key => $tierPrice) {
        $percentageValue = $this->getPercentage($tierPrice);
        if ($percentageValue) {
            $data[$key]['price'] = $price * (1 - $percentageValue / 100);
            $data[$key]['website_price'] = $data[$key]['price'];
        }
    }

    return $data;
}

A custom SQL query that would look for non falsy value or percentage_value values for given product id, customer group id and website id would give you wrong result for this case!

Changing Customer Group Price from Discount Percent to Fixed Amount

To cover both change cases mentioned in the introduction, let’s see what happens when store admin changes pricing for Luma Club back to fixed amount. Does percentage_value stays the same when value is updated to amount of 38.00?

• Yes

• No (correct)

percentage_value is nullable so on row update if the value column is the one that has changes, the percentage_value column becomes falsy.

Dwight Schrute Would Approve

If Dwight Schrute were a Magento developer, I think he would appreciate the time invested in stubborn debugging to find out why there are two non-false values in the catalog_product_entity_tier_price table and how Magento calculates tier price in that case. I hope it was useful for you, too! Let us know if you agree with our opinion on Dwight. 😉