Variables for end-users : add support for IN sql

#1

Hi,

I am trying to allow users to run a query depending on an attribute that has various values in our database.
EDIT: my question wasn’t clear that I would like them to paste a list of possible values quickly into the input box, not select each value individually.
Also, I don’t see the dropdown: "Type of filter: “Category” or “None” " for the attribute that I’m querying, so as a result it acts like None and users can’t edit the value at the top of the query, as shown on Lourival’s screenshot.

Therefore my SQL includes:
where cat IN (‘category1’,‘category2’).

However, I can’t currently make this work with user-set variables, e.g.
where phone IN ({{list_of_categories}}).

It works of course when I have only 1 category defined by the user in the input box, but as soon as we try to input 2 values (that are 2 categories that exist in the database) in the text field, whether we separate them in the input box by commas, spaces, " ‘,’ " or anything else I tried, it returns “No results”. Expected: it works as if the user had written the SQL query where category IN (‘category1’,‘category20’,‘category5’,…).
I

I’ve tried writing these 2 category names separated by ‘,’ as that would be the correct underlying syntax. I get “No results”.

(I’ve even tried replacing the whole section: where phone {{test code}}, but I get error messages in that case and it wouldn’t be user friendly if it worked anyhow).

Thanks!

#2

Hi Vaite, good morning!

In the questions created via query, you can omit the “IN” clause, because Metabase takes care of the rest. You must use a variable. See the attached image:

I used the people table from the sample database. In this select, I want to return all of the people from cities selected by the user. If he does not select none, all people will come. If he reports a city that does not exist on the list, he will not return anyone. And important detail, cities are retrieved from the bank and are listed as the user goes typing …

Hope this helps.

Hugs and good luck!

#3

Hi @Vaite
It sounds like you would benefit from using Field Filters, which allows the users to select the category options instead of having to write them. @LOURIVAL has made an example.
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#the-field-filter-variable-type

Currently what you’re seeing when you’re doing WHERE phone IN ({{list_of_categories}}) and the user input cat1,cat2,cat3, then it get’s converted to WHERE phone IN ('cat1,cat2,cat3'), which is a single string containing commas.

#4

Thanks a lot !