Multi-select filters with SQL when embedded

1

I’ve embedded a dashboard in an IFRAME. Passing the parameter values from select lists in the host page.

All worked fine until customer requested that I hid the filters in the dashboard (locked turned on in the Application Sharing), and allowed multi-select.

Everything works except for questions based on SQL when the filters have more than one value, there’s a query error. It looks like the SQL is being generated incorrectly.

workaround is to use Views rather than custom SQL.

One for the buglist or is there a workaround?

1 Like
2

I am also hoping that there is some way to pass multiple parameter values to a SQL based question.

The way we’ve been doing it is to pass a long comma-delim string in a URL parameter (treated as a single value) then split it in the query. The code to do that is very database specific, might not be possible for some, but in Postgres it looks something like:

SELECT 
...
WHERE 
    column_name in 
        (
         SELECT cast(unnest as integer) 
         FROM
         unnest(string_to_array({{passed_in_comma_delim_string}}, ','))
         )

column_name is the SQL Table column you want to filter on and passed_in_comma_delim_string would be the URL parameter.

There’s another discussion here Filters features enhancement which mentions this change: https://github.com/metabase/metabase/pull/6563 But it seems to be for filters not necessarily URL parameters. That feature is available; the Field Filter variable type lets you do something like WHERE {{created_at}}. The created_at variable is bound to both a column and will take multiple values.
This might be a possible workaround:
Create a Field Filter with some dummy values to choose from (multi-value). Perhaps there is then a way to pass multiple real values to it via the URL. Possibly not, it might all be in the javascript front end layer, and constructing valid SQL in a way not exposed to the URL.

But then I read the docs on “sql parameters” and it suggests it should be possible to “dynamically replace values in your queries using filter widgets or through the query’s URL”, although as you discovered it doesn’t format multiple values / an array correctly.

This is not a new problem nor unique to Metabase. In other reporting systems I’ve used some variation of split().join() to wrap values in the correct quotation marks/ apostrophes into the SQL. There’s the consideration of preventing SQL Injection, but it should certainly be possible.
There’s also a limit to how long a URL can be, but it’s something like 1000+ characters depending on the browser so is more than reasonable for practical use cases.

1 Like