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:
- Apply December 2019 – December 2021 -> works fine;
- Apply December 2019 – December 2022 -> works fine;
- 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);
}
}