redash
redash copied to clipboard
getredash
Optional Parameters Support
Today to make a parameter optional, the user needs to have a workaround in the query logic itself. For example:
select count(0)
from queries
where 'All' = '{{org_id}}' or org_id::varchar = '{{org_id}}'
In this case when the value of org_id is All, then we don't filter by it. It will be great if the user could mark a whole section as optional if no parameter is provided. The templating language we use (Mustache) actually supports this:
select count(0)
from queries
{{#org_id}}
where 'All' = '{{org_id}}' or org_id::varchar = '{{org_id}}'
{{/org_id}}
If no value for {{org_id}} is provided, then the section between {{#org_id}} and {{/org_id}} won't be rendered. So in theory, we can just allow for passing empty values for parameters and it works.
This has two issues though:
- Any false-y value will make this section not render. Including
0(zero), which might be acceptable in some cases. - This might make the implementation of #2904 more complicated.
We should probably take this into account when implementing #2904, and revisit once it's indeed implemented.
we are evaluating Redash but got stuck on date range filters. This has been an issue for two years? That's very discouraging.
https://discuss.redash.io/t/add-ui-for-query-params-at-dashboard-level/236
@Maxhodges the discussion you linked to started 2 years ago but over time changed topic.
Date Range parameters are supported.
Metabase solved this using bracket syntax: [[AND created >= {{start_date}} ]].
If the variable referenced within the double brackets (start_date) exists then the contents of the brackets is parsed into the query, otherwise the line is ignored.
This is quite elegant in my opinion and it doesn't invite users to abuse default values.
This mustache template workaround did not work for me. Am I doing this wrong?
@joekjoshua we still don't allow empty values for parameters...
@pavelpatrin we're thinking about using Jinja instead of Mustache here. Just not sure how safe it is (even with the sandbox) and it introduces some issues with frontend compatibility, as it can be only processed on the backend. But it's definitely an option.
I've used this in the past for SQL-safe Jinja queries - maybe it could be an option here? https://github.com/hashedin/jinjasql
Optional Parameters feature will be very welcome here too. Mustache could be also useable with a Boolean value, even without allowing empty values. How about adding a system parameter that will control the mustache template workaround?
Any timeline for supporting Jinja?
What I did was to put the jinja syntax as comments in the sql editor so it wont bother the frontend , then parse it with jinjasql (for safety) , conditionally to os param (new param). if the os param is not set, it will be ignored because of the comments.
something like this:
Select name, — {% if {{ detail_flag }} and {{ address_flag }} %} — Lname, comments, address1, address2 , city — {% elif {{ detail_flag } and not {{ address_flag }} %} — , Lname , city — {% else %} — , city — {% endif %} From customer
What do you think about this? I'll submit the code if you want to try/check it
Im using this Jinja support branch successfully. It supports the rendering of the query fully based on the input parameters. See example above.
I think redash can support optional parameter like {{paramName, defaultValue}} , when input empty string, then parameter paramName set the defaultValue.
A bit different than the workaround in the OP (I could not make it to work). This worked for me:
SELECT *
FROM CLIENTS
WHERE (client_id = '{{ client_id }}' OR 'all' = '{{ client_id }}')
AND (activated = TRUE OR something_else = TRUE);
Than manually add a description for the param so users know what to type in:
Result:
Any updates on this? This feature would be awesome, it's kind of weird how this hasn't been implemented yet
And updates on this? This is a bit weird that this is not supported. Thanks!
I like, how it works in metabase
For example:
[[ and {{start_date]] >= “2018-01-01”]]
If start_date is empty or null, all condition removed from sql query
