How can I extract just the list of values from a field filter?

Get Help
1

Can I extract just the list of values from the field filter for my SQL queries and not have it expand to IN clause?

My scenario is that I want to use a different table to get my field filter values from.

  • Say I have {{test_field_filter}} on field table_a.column_a
  • I want to write a query SELECT * FROM table_b WHERE column_b IN {{test_field_filter}}
  • However, the above query gets generated as SELECT * FROM table_b WHERE column_b IN column_a IN ('value1', 'value2', 'value3')

Simple reason is that table_a and table_b come from entirely different data sources/backends. Creating materialisation will be overkill and using joins will be inefficient.

If theres a way to not have the IN clause, it will also solve some of the issue here: Ability to create pre-populated SQL filters · Issue #5245 · metabase/metabase · GitHub
(i.e. one could just create a view and refer that in the field filter).

If it is currently not possible, I'm happy to hack about the code to make it work for me if someone can point me to the right files in the codebase. My guess is it will be in the src/metabase/driver/sql/parameters/substitution.clj and src/metabase/driver/sql/parameters/substitute.clj.

I am using the community driver metabase-clickhouse-driver, So I could also modify it. If someone can help me with the best way to do it.

p.s. I am a Python and C++ programmer and Java, Clojure, JS is a bit foreign to me :-/ so help would be really really appreciated.

p.p.s Also the Field Filter syntax is bit weird when used in queries and it makes queries hard to understand. Mabye I'm missing something, so it would be good to understand the logic behind such syntax)

2

Hi @nilay
You cannot control Field Filters, since Metabase does that.
You can do something like this:

SELECT * FROM table_b WHERE column_b IN (
    [[SELECT DISTINCT column_a FROM table_a WHERE {{test_field_filter}} --]] SELECT null
)
3

Thanks for the suggestions/workaround @flamber.

That would work in some cases, though the query would slightly inefficient.

Also some query constructs like following are not possible:

SELECT
    countIf(tableA.columnX, in(tableA.columnA, {{test_field_filter}})) 
...

The above could be written as CTE but in think there's a bug in Clickhouse (see here: IN clause doesn't work with arrays consistenly · Issue #42261 · ClickHouse/ClickHouse · GitHub) so CTE is not possible.

Anyway however for my case, it won't work as the tables are remote (e.g. different servers) and opening up access is not possible.

I'm happy to modify the driver code if I can somehow handle the substitutions in the driver if someone can help me out on what parts I need to modify.