[superset] branch master updated: feat: add a `where_in` filter for Jinja2 (#19574)

[be...@apache.org]

This is an automated email from the ASF dual-hosted git repository.

beto pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/superset.git


The following commit(s) were added to refs/heads/master by this push:
     new 83c3779a6c feat: add a `where_in` filter for Jinja2 (#19574)
83c3779a6c is described below

commit 83c3779a6c3f7cc371dd8b8bf70519ec473954db
Author: Beto Dealmeida <ro...@dealmeida.net>
AuthorDate: Thu Apr 7 07:27:23 2022 -0700

    feat: add a `where_in` filter for Jinja2 (#19574)
---
 docs/docs/installation/sql-templating.mdx |  6 ++++--
 superset/jinja_context.py                 | 22 ++++++++++++++++++++++
 tests/unit_tests/jinja_context_test.py    | 27 +++++++++++++++++++++++++++
 3 files changed, 53 insertions(+), 2 deletions(-)

diff --git a/docs/docs/installation/sql-templating.mdx b/docs/docs/installation/sql-templating.mdx
index 09cc036e6a..2a80f0fbf6 100644
--- a/docs/docs/installation/sql-templating.mdx
+++ b/docs/docs/installation/sql-templating.mdx
@@ -206,10 +206,12 @@ Here's a concrete example:
 SELECT action, count(*) as times
 FROM logs
 WHERE
-    action in ({{ "'" + "','".join(filter_values('action_type')) + "'" }})
+    action in {{ filter_values('action_type')|where_in }}
 GROUP BY action
 ```
 
+There `where_in` filter converts the list of values from `filter_values('action_type')` into a string suitable for an `IN` expression.
+
 **Filters for a Specific Column**
 
 The `{{ get_filters() }}` macro returns the filters applied to a given column. In addition to
@@ -243,7 +245,7 @@ Here's a concrete example:
 
     {%- if filter.get('op') == 'IN' -%}
         AND
-        full_name IN ( {{ "'" + "', '".join(filter.get('val')) + "'" }} )
+        full_name IN {{ filter.get('val')|where_in }}
     {%- endif -%}
 
     {%- if filter.get('op') == 'LIKE' -%}
diff --git a/superset/jinja_context.py b/superset/jinja_context.py
index ab3aa5070c..e365b9a708 100644
--- a/superset/jinja_context.py
+++ b/superset/jinja_context.py
@@ -401,6 +401,25 @@ def validate_template_context(
     return validate_context_types(context)
 
 
+def where_in(values: List[Any], mark: str = "'") -> str:
+    """
+    Given a list of values, build a parenthesis list suitable for an IN expression.
+
+        >>> where_in([1, "b", 3])
+        (1, 'b', 3)
+
+    """
+
+    def quote(value: Any) -> str:
+        if isinstance(value, str):
+            value = value.replace(mark, mark * 2)
+            return f"{mark}{value}{mark}"
+        return str(value)
+
+    joined_values = ", ".join(quote(value) for value in values)
+    return f"({joined_values})"
+
+
 class BaseTemplateProcessor:
     """
     Base class for database-specific jinja context
@@ -433,6 +452,9 @@ class BaseTemplateProcessor:
         self._env = SandboxedEnvironment(undefined=DebugUndefined)
         self.set_context(**kwargs)
 
+        # custom filters
+        self._env.filters["where_in"] = where_in
+
     def set_context(self, **kwargs: Any) -> None:
         self._context.update(kwargs)
         self._context.update(context_addons())
diff --git a/tests/unit_tests/jinja_context_test.py b/tests/unit_tests/jinja_context_test.py
new file mode 100644
index 0000000000..1f88f4f1a9
--- /dev/null
+++ b/tests/unit_tests/jinja_context_test.py
@@ -0,0 +1,27 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements.  See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership.  The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License.  You may obtain a copy of the License at
+#
+#   http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied.  See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+from superset.jinja_context import where_in
+
+
+def test_where_in() -> None:
+    """
+    Test the ``where_in`` Jinja2 filter.
+    """
+    assert where_in([1, "b", 3]) == "(1, 'b', 3)"
+    assert where_in([1, "b", 3], '"') == '(1, "b", 3)'
+    assert where_in(["O'Malley's"]) == "('O''Malley''s')"