Filter Rows Based On A Boolean Column

Contents hide

Problem Description:

I am dynamically creating SQLs for each table via macro.Details of column and table name is stored in a seed file.

select table,
       columns,
       flag
from  {{ file }}

I am using run_query to execute the above query and assigning it to a list.

{%- set results = run_query(query) -%}

{%- if execute -%}
  {%- set table_names,columns,flag = [results.columns[0],results.columns[1],results.columns[2]] -%}
{%- endif -%}

Depending on the column flag, I have to group the CTEs and create two union CTEs separately:

flag_true as ( select * from tab1
            union
            select * from tab2) ,
flag_false as ( select * from tab3
            union
            select * from tab4)

I tried the below code but the problem is since my table_names loop have all flag values both true and false there is an extra union coming in the end.
I am unable to figure out a way to reduce the loop to only tables having flag == true.

flag_true as (
  {% for tab_name in table_names %}
    {%- if flag[loop.index-1]   %}
      select * from {{tab_name}} {% if not loop.last %}union{% endif %}
    {% endif %}
  {% endfor %}
),

Solution – 1

By creating your three lists table_names, columns and flag, based on your table columns, you are complicating the matter, as you are later treating them back as rows.

A better approach would be to filter your rows based on an attribute value, and this could be achieved with the selectattr filter or its opposite, rejectattr filter.
I believe you can use those filters on agate.Table.rows, and since run_query returns a Table object, on results.rows, in your code.

So, something like:

{%- set results = run_query(query) -%}

{%- if execute -%}
  {%- set rows = results.rows -%}
{%- endif -%}

flag_true as (
  {% for row in rows | selectattr('flag') %}
    select * from {{ row.table }} {% if not loop.last %}union{% endif %}
  {% endif %}
% endfor %}
),
flag_false as (
  {% for row in rows | rejectattr('flag') %}
    select * from {{ row.table }} {% if not loop.last %}union{% endif %}
  {% endif %}
)

Rate this post