Filter rows based on a boolean column

Filter rows based on a boolean column

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
We use cookies in order to give you the best possible experience on our website. By continuing to use this site, you agree to our use of cookies.
Accept
Reject