pasqualtroncone commented on issue #19404:
URL: https://github.com/apache/superset/issues/19404#issuecomment-1700171750
@rusackas Still relevant in 2.1.0. I'm not sure if there is a similar
request to this one but I will explain it here.
### Short explanation
I cannot or haven't found a way to remove `from_dttm` and `to_dttm` from
outer query as we would do with `get_filters('<filter name>',
remove_filter=True)`.
### Long explanation
Perhaps this is a bad example but I think it will illustrate very well what
is going on.
Suppose that we want to show a bar chart that shows _"number of packets
grouped by date of receipt that have been sent in a specific date range by
using the date filter"_
So, having a virtual data set with the following query ( I use Clickhouse as
DB)
```sql
SELECT `create_date`,
`send_date`,
`receive_date`,
`package_id`
FROM `default`.`packages`
WHERE
`send_date`> parseDateTimeBestEffort('{{ from_dttm }}')
AND `send_date` <= parseDateTimeBestEffort('{{ to_dttm }}')
```
with `create_date` as default datetime column.
A bar chart with `send_date` as TIME COLUMN, `Last year` as TIME RANGE, a
simple `count(package_id)` as part of the METRICS and `receive_date` as part of
DIMENSIONS
will generate the following query
```sql
SELECT toStartOfMonth(toDateTime(`send_date`)) AS `__timestamp_ae6234`,
`receive_date` AS `receive_date_04ccbe`,
count(`package_id`) AS `COUNT(package_id)_818505`
FROM
(SELECT `create_date`,
`send_date`,
`receive_date`,
`package_id`
FROM `default`.`packages`
WHERE `send_date`> parseDateTimeBestEffort('2022-08-31T00:00:00')
AND `send_date` <= parseDateTimeBestEffort('2023-08-31T00:00:00')) AS
`virtual_table`
WHERE `create_date` >= toDate('2022-08-31')
AND `create_date` < toDate('2023-08-31')
AND `send_date` >= toDate('2022-08-31')
AND `send_date` < toDate('2023-08-31')
GROUP BY `receive_date`,
toStartOfMonth(toDateTime(`send_date`))
ORDER BY `COUNT(package_id)_818505` DESC
LIMIT 1000;
```
Lets brake that query down.
1. `send_date`is applied twice. There is no need for that.
2. Why `create_date` (default datetime column of the Dataset) has to come
into play? Chart will no use or filter anything by it. I am aware that if we
don't mark any column as default datetime, the condition disappears from the
WHERE section of the query, but once you have selected it you are dooming
yourself forever unless you create a new dataset. I guess here is where
@moathOSA stopped and decided to ask on github. (This last tip will probably
solve your problem @moathOSA )
### Some thoughts
It would be interesting if date filters behaved like any other filter. In
this way we could define:
- the name of the 'column' to be passed to all the chart if needed. If it is
not defined to use the current ones.
- multiple date filters that allow thinner slices of the data in a data set.
- be able to make use of common functions such as get_filters().
I hope this put some light on the road
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
