In metabase I want to have a query that gets two parameters of type date but if they are not provided uses some default values instead. This is an example of a between clause I used to achieve that but failed. It seems when no value is passed as parameter something other than null is set to variables in query.
BETWEEN COALESCE(date({{start_date}}),subdate(current_date, interval 1 month))
AND COALESCE(date({{end_date}}),subdate(current_date, 1))
I figured out how to solve this. First I replaced the whole between clause with a field filter. Then I rewrote the whole query generated by metabase. For some strange reasons the query generated by metabase with lots of aliases could not be run by MariaDB driver I use. Auto-generated query by metabase:
SELECT count(*) AS `count`, `orders`.`city` AS `city`, `orders`.`status` AS `status`, `orders`.`transport_type` AS `transport_type`,
date(`orders`.`launched_at`) AS `launched_at`
FROM `orders`
WHERE (date(`orders`.`deleted_at`) IS NULL
AND {{filter_date}}
GROUP BY `orders`.`city`, `orders`.`status`, `orders`.`transport_type`, date(`orders`.`launched_at`)
ORDER BY `orders`.`city` ASC, `orders`.`status` ASC, `orders`.`transport_type` ASC, date(`orders`.`launched_at`) ASC
Then this question can be added to the dashboard. In dashboard edit mode filter widgets (or type date here) could be used to filter the question results. One thing that annoyed me is the dashboard has to be saved in order to have data in card refreshed. This is my final query:
select count(*), orders.city, orders.status, orders.transport_type, date(orders.launched_at)
from orders
where (date(orders.deleted_at)) is NULL
AND {{filter_date}}
GROUP BY orders.city, orders.status, orders.transport_type, date(orders.launched_at)
ORDER BY orders.city, orders.status, orders.transport_type, date(orders.launched_at)