Hello,
I have an SQL query that I can't convert into a simple Metabase question unfortunately in which I have an optional parameter 'Id' which can be empty, a single value or multiple values. This question is in an embedded dashboard in an external app and I'm passing this parameter via the API.
The query works fine with a single Id but as soon as I pass multiple ids, it is failing. Here is a simplified version of the query as I initially wrote it (when there was only a single possible Id value).
SELECT AVG( ST_Distance (c."Point"::geography, p."Point"::geography) / 1000 ) FROM retailers r INNER JOIN agreements a ON a."RetailerId" = c."Id" INNER JOIN producers p ON a."ProducerId" = p."Id" WHERE a."State" = 1 [[and r."Id" = {{Id}}::uuid]]
Now that multiple values are possible, it obviously no longer work, I've tried a lot of different things from forcing the conversion of the {{Id}} into text to do a string comparison
{{Id}}::text like '%' || c."Id"::text || '%' (something like that)
to trying to convert the parameter to an array with stuff like ::text[]... etc but nothing seems to be working fine
Did anyone face a similar issue and could help me with that ?
Thanks in advance,
Arnaud