Dynamic Joins - How to use liquid to dynamically adjust sql_on conditions (Community)

View the original community article here

Last tested: Oct 22, 2019
 

Define the different join conditions within liquid if/then control flow statements:

explore: order_items {

join: orders {

relationship: many_to_one

sql_on: {% if orders.id._in_query and orders.created_date._in_query %}

${orders.id} = ${order_items.order_id} AND ${orders.created_raw} = ${order_items.returned_raw}

{% elsif orders.id._in_query %}

${orders.id} = ${order_items.order_id}

{% elsif orders.created_date._in_query %}

${orders.created_raw} = ${order_items.returned_raw}

{% endif %}

;;

}

}

In the above example, I am joining on A) the date and id fields if both the date and id field are included in the query, B) the id field if only the id field is included in the query, and C) the date field if only the date field is included in the query.

Note: Make sure to confirm your if statements are in the order you would like them evaluated because the process will return the "then" response for the first true logic test.