0

A custom column is populated when a date range filter from UI is applied. I noticed weird behavior. When I apply a date filter it works fine until I decide to apply the date range I filtered before. Example:

  1. Apply December 2019 – December 2021 -> works fine;
  2. Apply December 2019 – December 2022 -> works fine;
  3. Apply the first one again and the grid stops reacting to the filter completely (shows the same data, and values don't change) until I enter a different date range or product ID.

Why is it happening and how to fix it?

Code inside public function addFilter(\Magento\Framework\Api\Filter $filter)

elseif ($filter->getField() == 'unique_purchases') {
            $dateValue = $filter->getValue();
            $conditionType = $filter->getConditionType();

            $select = $this->getCollection()->getSelect();

            // Check if the table is already joined
            $isJoined = false;
            foreach ($select->getPart(\Zend_Db_Select::FROM) as $tableName => $tableInfo) {
                if ($tableName === 'sales_order_item') {
                    $isJoined = true;
                    break;
                }
            }

            // Only join the table if it's not already joined
            if (!$isJoined) {
                $select->joinLeft(
                    ['sales_order_item' => $this->getCollection()->getTable('sales_order_item')],
                    'main_table.product_id = sales_order_item.product_id',
                    [
                        'unique_purchases' => new \Zend_Db_Expr("COUNT(DISTINCT order_id)")
                    ]
                );
            }

            // Apply date filter
            if ($conditionType == 'gteq') {
                $select->where("sales_order_item.created_at >= ?", $dateValue);
            } elseif ($conditionType == 'lteq') {
                $select->where("sales_order_item.created_at <= ?", $dateValue);
            }
        }

1 Answer 1

0

This is just a guess from the issue, but doing admin request in grid views are generating data in the table ui_bookmark. I would suggest to double check with this table if something weird is not happening like trying to recreate an object that is already existing in the table.

2
  • everything is alright there, something wrong with my code
    – Dramorian
    Commented Mar 25 at 17:06
  • last leftJoin in the query looks like this LEFT JOIN sales_order_item ON main_table.product_id = sales_order_item.product_id WHERE (inventory_id >= '4846') AND (inventory_id <= '4846') AND (sales_order_item.created_at >= '2019-01-01 00:00:00') AND (sales_order_item.created_at >= '2019-01-01 00:00:00') AND (sales_order_item.created_at >= '2019-01-01 00:00:00') AND (sales_order_item.created_at <= '2020-12-01 23:59:59') probably the duplication is the problem, but I might be wrong.
    – Dramorian
    Commented Mar 25 at 17:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.