Multiple wild cards and filters

[sharathsridhar]

Hello, Thank you for a great library. I am looking to build a complex sql query which has multiple wild card statements, for example Select * From table A Where (A.col1 like '%abc%' or A.col1 like '%123%' or A.col1 like '%345%') AND (B.col2 like '%xyz%' or B.col2 like '%qwe%')... etc., where the values within the wildcards are in a list or dictionary. Is this possible?

[clayadavis]

This is a good usecase for the Jinja syntax. In the Python, you'd have something like

data = {
    'col1_patterns': ['%abc%', '%123%', '%345%'],
    'col2_patterns': ['%xyz%', '%qwe'],
}

and in the template:

SELECT * FROM table A
WHERE (
{%- for pattern in col1_patterns %}
    A.col1 LIKE {{ pattern }}
    {% if not loop.last %} OR {% endif -%}
{% endfor -%}
)
AND (
{%- for pattern in col2_patterns %}
    A.col2 LIKE {{ pattern }}
    {% if not loop.last %} OR {% endif -%}
{% endfor -%}
)

More info on Jinja template design, specifically:

• loop variables like the loop.last,
• whitespace control for those extra dashes {%- and -%},
• and getting fancy with a template macro for this if you wanted.