This is my column code:
$this->addColumn(
Mage::getModel('catalog/product')->formatUrlKey($key),
array(
'header' => Mage::helper('core')->__($label),
'index' => $key,
'filter' => $isFilterable,
'sortable' => true,
'type' => 'date',
'filter_condition_callback' => array($this, 'dateFilter'),
)
);
This is my callback code:
public function dateFilter($collection, $column){
$value = $column->getFilter()->getValue();
$from = $value['orig_from'];
$to = $value['orig_to'];
if (!$value) {
return $this;
}
$this->getCollection()->getSelect()->where("date_scheduled >=DATE('".$from."') AND date_scheduled <= DATE('".$to."') ");
return $this;
}
I executed query in phpmyadmin and I got no errors, but neither results. I want to do is to filter my grid after date, so between X and Y.
The value for $isFilterable is adminhtml/widget_grid_column_filter_date
This is my Query generated:
SELECT `t`.*
FROM (SELECT
DATE_FORMAT(`as`.`scheduled_at`, '%Y-%c-%d ') AS `date_scheduled`,
`o`.`customer_email`,
`as`.`increment_id` AS `subscription id`,
`as`.term AS `period`,
`c`.`increment_id` AS `customer increment id`,
`o`.`increment_id` AS `order incrementid`,
(SELECT SUM(`asi`.`price` * `asi`.`qty`)
FROM `adyen_subscription_item` AS `asi`
WHERE `asi`.`subscription_id` = `as`.`entity_id`) AS `subtotal_ex_vat`,
(SELECT SUM(`asi`.`price_incl_tax` * `asi`.`qty`)
FROM `adyen_subscription_item` AS `asi`
WHERE `asi`.`subscription_id` = `as`.`entity_id`) AS `subtotal_inc_vat`,
`s`.`code` AS `store`,
`as`.`shipping_method` AS `shipping_method`,
`bill`.`method_code`,
`bill`.`agreement_label`,
GROUP_CONCAT(`oi`.`sku` SEPARATOR ',') AS `product SKUs`
FROM `adyen_subscription` AS `as` LEFT JOIN `sales_flat_order` AS `o` ON `o`.`entity_id` = `as`.`order_id`
LEFT JOIN `adyen_subscription_item` AS `oi` ON `as`.`entity_id` = `oi`.`subscription_id`
LEFT JOIN `customer_entity` AS `c` ON `o`.`customer_id` = `c`.`entity_id`
LEFT JOIN `core_store` AS `s` ON `as`.`store_id` = `s`.`store_id`
LEFT JOIN `sales_billing_agreement` AS `bill` ON `as`.`billing_agreement_id` = `bill`.`agreement_id`
WHERE (`as`.`scheduled_at` >= now() AND `as`.`scheduled_at` <= DATE_ADD(now(), INTERVAL 6 MONTH)) AND
`as`.`status` = 'active'
GROUP BY `oi`.`subscription_id`
ORDER BY `as`.`scheduled_at` ASC) AS `t`
WHERE (date_scheduled >= DATE('02/25/2017') AND date_scheduled <= DATE('02/28/2017'))
Best Answer
Change type from date to datetime as shown below: