How to control if I want to join to a view by a parameter?

Can we control to join to a view or ignore the view by a parameter?

For example create a parameter which the type is yesno
when users select yes, a specific view will be joined to the explore
when users select no, that view will be omitted.

In a derived table, where you can control the entire SQL using {% if %} blocks, you can definitely do this easily. Outside of a derived table, ie: in a normal explore, it’s a little more complicated.

You’d probably have to use the sql: parameter instead of sql_on: https://docs.looker.com/reference/explore-params/sql-for-join
And then you could have a liquid block in that sql: parameter to join the table only under some conditions (and otherwise have it be blank).

To modify the example from that docs page to show that:

explore: event {
  join: member {
    sql:
      {% if field._parameter_value == 'yes' %}
      INNER JOIN member
      ON member.id = event.member_id
      {% else %}
       {% endif %} ;;
  }
}
1 Like

this might not be very performant but we do it in the normal way you join in a view in the explore but we spoil the join condition if we dont want it to join (based on param value, eg. 1=1 or 1=2).

2 Likes

That’s much quicker, but yeah, I imagine that simple math would then evaluate on all rows? Curious to see if that does impact performance or if there’s some SQL execution magic that makes it quick still.

just looked, we put it into the where statement in the derived table

1 Like

Thanks! didn’t know the sql: parameter before and I was always stuck at using sql_on: before.