Using the results of a query in Liquid script

so I can use a query to build a selector like so:

SELECT distinct zone_name
FROM customer_zone_map
ORDER BY 1

{% form %}
zone:
  type: select
  defualt: 'zonesf09b'
  options:
    labels: zone_name
    values: zone_name
{% endform %}

and I can use a numerical for loop to step across shards, to union results together

{% for i in (1..10) %}

but I would like to be able to step across zone_name in a similar fashion

so test code that works, not that it's how I want the sql to be shaped, but it demo's the loop working...

SELECT 'a'
{% for i in (1..10) %}
 ,'{{i}}'
{% endfor %}

so I hoping to be able to

SELECT 'a'
{% for name in zone_name %}
 ,{{name}}
{% endfor %}

I am other part is I also have multiple values per row that I really want, basically a shard id and 1-n zones per shard. I am effectively writing dynamic sql, and trying to understand the liquid's model of how to do so. 

4replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
    • Jonathan Krangel
    • Director - Head of Customer Success @ Mode
    • Jonathan_Krangel
    • 1 yr ago
    • Reported - view

    Hi Simeon Pilgrim - thanks for writing in.

    You can't iterate over the values of zone_name in this way. All Liquid code is executed by Mode first before any SQL is executed. So when Mode is evaluating the Liquid code, the object "zone_name" is undefined. I'm not totally clear on the actual outcome you're looking for here, but one of the folks on our success team can probably help you think of the right solution.

    Feel free to open a ticket with our success team via in-product chat or success@modeanalytics.com if we can be of further assistance. Thanks!

    Reply Like
  • When I use top block of code, I get a Picker that allows we to choose one row from a set of rows, which I can then use to inject that value into a second block of liquid..

    SELECT distinct zone_name
    FROM customer_zone_map
    ORDER BY 1
    
    {% form %}
    zone:
      type: select
      defualt: 'zonesf09b'
      options:
        labels: zone_name
        values: zone_name
    {% endform %}

    then I can use it later like so 

    SELECT count(*)
    FROM bi_{{zone_name}}.event
    

    to do stuff, that stuff not being the important part but the injecting the selected value {{zone_name}} is the good stuff.

     

    Ok, so the you have another pattern of using

    {% for i in (1..10) %}

    which the result of you can also inject into "expanded code"

    now the I can put both of those in to the same liquid and have my single {{zone_name}} that I selected and loop.. all good.. so I know the picker value and the for loop variables are known at macro expansion time, other the code wouldn't work.

     

    But what I would like is the python equivalent of 

    for zone in zone_name:
      print( zone )
    

    thus I liquid I Imagine do this

    {% for zone in zone_name%}
    select '{{zone}}', count(*) from bi_{{zone}}.event
    {%end for%}
    

    possible my syntax is wrong and ignoring the lack of a union all line, but I would hope this would expand if a had three rows form my zone_map table to:

    select 'zone1', count(*) from bi_zone1.event
    select 'zone2', count(*) from bi_zone2.event
    select 'zone3', count(*) from bi_zone3.event
    
    
    Reply Like
  • Simeon Pilgrim Jonathan Krangel were you ever able to solve this issue?

    Reply Like
  • After being told it could not be done,  and exploring the functionality offered, I stopped trying solve this problem with Mode. I still use mode to explore data, I just hard code the servers into the SQL. Which means I cannot use it to share with others, which means I don't need to purchase as non-demo account.. This works fine with Looker, so for sharing with others we use that.

    Reply Like
Like Follow
  • Status Answered
  • 2 mths agoLast active
  • 4Replies
  • 366Views
  • 3 Following