I read in the documentation that it is recommended to use Field filters in native queries when possible. How does metabase handle field filter variables in different SQL questions that are mapped to different table names / field names which contain the same information?
For example, I have a dashboard with a filter for Organization Name. In the dashboard there are some SQL questions for which the organization_name variable is mapped to the organizations table field name but many other SQL questions do not use the organizations table. Rather those use pre-aggregated daily analytics etc. Those questions also have an organization_name variabe but those variables are mapped to the dedicated organization_name on the respective daily pre-aggregated tables.
Is the metabase dashboard filter fine to handle variables which are mapped to different tables/fields or does that impact query performance?
We are currently with a PostgreSQL database but will be switching to ClickHouse. Are there limitations regarding the flexibility of field filters with a ClickHouse database?
The dashboard filter automatically handles field-linked filters. You configure which field in the question/card gets the filter data when you set up the dashboard filter. The question-level filter then figures out how to translate that into the question’s query.
The main benefit of field-linked filters for SQL queries is the added flexibility in building the filter in the query string. Date range filters in particular know that it can emit more complex predicates.
It might be different for ID filters as there is some communication there if the IDs are getting resolved to names.
Metabase tracks tables and fields using internal identifiers, the symbolic names are there for the humans; table1.id and table2.id are distinct entities, even though the column name is the same.
I don’t see anything in the docs related to limitations with field filters and ClickHouse specifically. Its SQL support is complete enough to handle Metabase’s filter situations.
Interesting. I actually never though of it a double layer action.
Dashboard filter communicating first to the question filter
Question filter injecting the correct SQL in the question.
For some reason, I always saw it as the question filters are there for convenience when viewing that individual question as stand-alone out of a dashboard. I was wrong.
When you say “added flexibility in building the filter in the query string.”, all I see is a very simple gain here in shortening the WHERE clauses. Instead of
[[AND org.name = {{organization_name}}]]
I can write
[[AND {{organization_name}}
For date filters, I have always indeed used filed filters from the start as I noticed that I provided more options with the Relative Date options that we use a lot.
For string/text, not sure what the benefit is aside from linked filters.
Right, and now {{organization_name}} can be replaced with an entire expression, not just an equality to a single value. Case in point, case insensitive search. You can’t do it* with the first clause (you need to modify the left-hand side), but with the linked filter, {{organization_name}} could be replaced with lower(org.name) = lower($1), and now searches case-insensitive.
-* Well, I guess you could, it’s a correlated subquery and performance will be horrible.
