Variables for end-users : add support for IN sql

Feedback and Requests
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!

querys_metabase
querys_metabase1280×696 54.2 KB

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 !