Skip to content

Issue with Data Provider date range filter

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(MagentoFrameworkApiFilter $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);
            }
        }