Hello,
It looks like the problem I'm trying to solve is most similar to topic 7453, though that was from a couple years ago and doesn't seem to work with my use case.
Context:
This situation has come up quite recently in the past few weeks, but to make this sound not so abstract, I'll illustrate a particular case, so that it's a bit easier to follow:
I am trying to generate a chart in Metabase showing app usage by app version, which -- if I were to not include any filters -- would be very cluttered since there are A LOT of app versions:
Since I would rather not have my chart contain a legend with over 30 categories, my idea would be to filter the chart as a DEFAULT to -- say -- the top 5 most used versions, just so that when a visitor immediately arrives at this dashboard and looks at this chart, they see a filtered view of the data (only 5 colored lines instead of 30+). However, I still want the dashboard user to be able to filter the dashboard by ANY app version AFTERWARDS -- using the filter widget -- even if this version exists outside of the top 5. In other words, I want the filter widget value to override the default.
I am doing this in SQL, and I am using field filters, and would like to allow the user to select multiple app versions in the filter. I am aware that you are able to set default values for field filters, but unfortunately you can only hardcode those, and my default value is rather dynamic since it is the results of a sub-query. Here is my default behavior:
SELECT
date,
app_version,
sum(users) users
FROM
app_data
WHERE
app_version in (
--subquery to only show the top 5 most used app versions
select app_version from (
select
app_version,
sum(users) AS "sum"
from app_data
group by 1
order by 2 desc
limit 5)
top_5_versions
)
GROUP BY 1, 2
So far so good.
But now the tricky part would now be to write this SQL in such a way that the app_version that is selected for the chart is EITHER the result of that subquery (i.e. top_5_versions) OR whatever the user puts into the field filter widget.
Here is where I conceptually know what I want to do, but I'm getting a bit stuck since Metabase has its own picky SQL-like templating syntax that has its own behavior.
I know that, according to the documentation, I can put default values in the query, e.g:
select
date,
app_version,
sum(users) users
from
app_data
where app_version = ([[ {{app_version}} --]]select '1.0.0'
)
group by 1, 2
... but that only works with a single value (explicitly using the = sign), and also it is hardcoded. Here's if I actually do it a bit more dynamically:
select
date:,
app_version,
sum(users) users
from
app_date
where app_version IN ([[ {{app_version}} --]]select app_version from (select app_version, sum(users) from app_data group by 1 order by 2 desc limit 1) top_app
)
group by 1, 2
but this already feels a hacky because I need to shove everything on a single line, and make sure to close the ending parenthesis in a new line so it's not commented out.
But the bigger problem at this point is that it doesn't work with multiple inputs, because I need to work with the field filter syntax. So I tried that out by switching syntax and instead making the default value return the top 5 apps instead of the single top 1 app:
select
date:,
app_version,
sum(users) users
from
app_data
where ([[ {{app_version}} --]]select app_version from (select app_version, sum(users) from app_data group by 1 order by 2 desc limit 5) top_5_apps
)
group by 1, 2
... and the filtering seems to work now when I input multiple options (yay!) BUT now the default value is throwing an error (when I don't input anything into the filter), the reasoning being ERROR: argument of WHERE must be type boolean, not type text.
So it feels a bit like whack-a-mole here, where I fix one problem but then it causes another. Is this special commenting syntax / "trick" for default values incompatible with queries that return multiple values? The only other place where I saw this was in topic 7453, but this example is using a query that returns a single value.
In your opinion, what would be the best way around such an issue? I experimented a bit with using CASE statements inside a where clause, but it was getting a bit messy and I still somehow come back to this same problem. Am I missing a much more obvious / clever workaround? Or is this a tricky thing that is currently impossible inside Metabase?
Thank you for your time!