Dynamic SQL query

[orazs]

Hello,
Didn't find anything relevant in neither in discussions nor in documentation.
I have a postgresql db and a simple query like

select smth
from table
where 1=1 and search_vector @@ websearch('english', 'some words')

In dashboard there should be an input box, if it's empty the query should be wo where statement, otherwise need to be applied accordingly.
What i tried:

select smth
from table where 1=1
{% if filter_values('search') %}
and
search_vector @@ websearch('english', 'some words')
{% endif %}

but it turns to:

SELECT *
FROM (SELECT 
*
FROM table
) AS virtual_table 
WHERE param_column IN ('some words') 
 LIMIT 1000;

Sorry if smth similar has been already discussed - didn't find

Thank you

[dosubot[bot]]

Hey @orazs, I'm here to help you with any bugs, questions, or contributions. Let's tackle this together!

To achieve dynamic SQL query modification based on user input in Superset, you can use Jinja templating effectively. It seems like you're on the right track, but there might be a misunderstanding in how the filter_values function is used. Here's a refined approach:

1. Ensure Jinja Templating is Enabled: Make sure that Jinja templating is enabled in your superset_config.py by setting the ENABLE_TEMPLATE_PROCESSING feature flag.

2. Use filter_values Correctly: The filter_values function should be used to dynamically insert filter values into your query. Here's how you can modify your query:

SELECT smth
FROM table
WHERE 1=1
{% if filter_values('search') %}
AND search_vector @@ websearch('english', {{ "'" + "','".join(filter_values('search')) + "'" }})
{% endif %}

3. Dashboard Input Configuration: Ensure that your dashboard input box is correctly configured to pass the filter value to the query. The filter name in filter_values('search') should match the filter component's name in your dashboard.

This setup should dynamically include the WHERE clause only when the input box is not empty. If the input box is empty, the WHERE clause will be omitted, as the filter_values function will return an empty list, and the if condition will not be satisfied.

If you continue to experience issues, double-check the filter configuration in your dashboard to ensure it aligns with the query logic.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}

[orazs]

Ok,@dosu, we made some progress, how than the dashboard input box should look like? I named it "search" pointing to "search_vector" field in original dataset, however this setup leads to :

SELECT columns
FROM (SELECT 
columns
from table
where 1=1
) AS virtual_table 
WHERE search_vector IN ('some words') 
 LIMIT 10000;

Thank you

[orazs]

For those who interested, the answer was found in https://preset.io/blog/intro-jinja-templating-apache-superset/