Optional Parameters Support

[arikfr]

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:

1. Any false-y value will make this section not render. Including 0 (zero), which might be acceptable in some cases.
2. 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.

[Maxhodges]

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

[arikfr]

@Maxhodges the discussion you linked to started 2 years ago but over time changed topic.

Date Range parameters are supported.

[pavelpatrin]

Maybe we can preprocess a query with Jinja?

[smick]

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.

[joekjoshua]

This mustache template workaround did not work for me. Am I doing this wrong?

[arikfr]

@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.

[netmole]

Hi,@arikfr ,how was it going about optional parameters?

[evan-burke]

I've used this in the past for SQL-safe Jinja queries - maybe it could be an option here? https://github.com/hashedin/jinjasql

[renekyewski]

Any news? This is pretty critical for us.

[adikhel]

@arikfr will the next release support optional parameters?

[duncanita]

Critical also for us.

[darleisantossoares]

Critical for us too.

[erels]

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?

[erels]

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

[erels]

Brach is here

[chenyuantao]

Critical for us too.

[erels]

Im using this Jinja support branch successfully. It supports the rendering of the query fully based on the input parameters. See example above.

[floriank]

+1 for this, we need this as well.

[Sonivaibhav26]

👍 this will be really helpful

[dengc367]

I think redash can support optional parameter like {{paramName, defaultValue}} , when input empty string, then parameter paramName set the defaultValue.

[nicolasgnr]

Any ideas if this will be merged sometime?

[idoshilon]

+1

[shubhamgoyal41]

+1

[zkid18]

+1

[persiyanov]

+1

[Naokimi]

request for this too

[SORC3r3r]

+1

[wmorin]

+1

[clac1212]

+1

[RichardSmaldone]

+1

[mat-mfb]

+1

[cfwme]

+1

[antbofh]

+1 :)

[hoto]

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:

[samzong]

Critical++ also for us

[marohds]

+1

[kshishkin82]

+1 Critical

[instplanet]

Any updates on this? This feature would be awesome, it's kind of weird how this hasn't been implemented yet

[yuhao-xu]

And updates on this? This is a bit weird that this is not supported. Thanks!

[jypogoy]

Is this already supported? A major road block for me.

[vldmr-k]

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

[toplinuxsir]

The feature is already supported ? Thanks!

[nathanlmeyers]

+1

[lemaadi]

+1

[algirdas-iterato]

+1

[RuiFG]

+1

[trungbui2000]

+1

[AnthonyDugarte]

+1

[soulwawa]

+1

[9mm]

I was mindblown to discover something so basic is not in here... I was like, there's no way this can be? Why must every single input be required as opposed to just returning an empty string I can work with to make things optional myself?