Join table only if filtering

The view is:

- view: event_list

  derived_table:

    sql: |

      SELECT

        ev.event_date,

        ev.description,

        ev.occurrences

      FROM

        event ev,

        detail dt,

        site st,

        section sc

      WHERE

        ev.detail_id = dt.detail_id AND

        dt.site_id = st.site_id AND

        dt.section_id = sc.section_id AND

        {% condition site_filter %} st.name {% endcondition %} AND

        {% condition section_filter %} sc.name {% endcondition %}



  fields:



  # Filters



  - filter: site_filter

    type: string

    # suggest_explore: site_list

    # suggest_dimension: site_list.site_name



  - filter: section_filter

    type: string

    # suggest_explore: section_list

    # suggest_dimension: section_list.section_name



  # Fields



  - dimension_group: event_date

    type: time

    timeframes: [date]

    convert_tz: false

    sql: ${TABLE}.EVENT_DATE



  - dimension: description

    type: string

    sql: ${TABLE}.DESCRIPTION



  - dimension: occurrences

    type: number

    value_format_name: decimal_0

    sql: ${TABLE}.OCCURRENCES

The explore is simply:

- explore: event_list

If there isn’t a simple fix, I have just thought of a possible alternative way to do this. Perhaps if I defined two new derived table views, one of which joined the Detail table to Site and the other which joined the Detail table to Section, then I could join to those views in the event_list view derived table instead of the explicit joins to Detail, Site and Section. The potential downside is that, if they filter on both site and section, it will join to the Detail table twice (once per filter) but that may be a price worth paying to avoid the join when there is no filtering.

Many thanks for your help.

Andrew

I had simplified my example for the sake of clarity but I thought that my specific situation was too complicated to do without derived tables; having looked at your answer, however, I think that I was wrong about that, so I will follow your suggestion and see how far I get.

Note: There is one advantage of derived tables, though: it is possible to hide the underlying database structure from end-users. This is useful for two reasons:

1. 
   

   It should not be necessary for users to know which field is in which table (in fact, exposing this level of detail can be dangerous!)

   
   


2. 
   

   It is possible to change the source of the fields without anything changing from the user’s perspective.

   
   

Many thanks for your help.

Andrew, as @lloydtabb pointed out, you can use a combination of view_label and fields to abstract the underlying material table structure. For example,

- explore: event_list

  fields: [event_list.event_date,event_list.description,event_list.occurrences,event_list.detail_id,detail.detail_id]

  joins:

  - join: detail

    view_label: 'Event List'

    sql_on: ${detail_id} = ${detail.detail_id}

    relationship: many_to_one

This will make only the 5 fields available in the explore:

• event_date


• description


• occurrences


• detail_id


• detail_id (belonging to detail view)

In addition, we probably don’t need to surface detail_id from the event_list table since whenever we end up using it, we also pull data from details table. So, let’s hide it:

``- dimension: detail_id

hidden: true

sql: ${TABLE}.detail_id

This will still make it available for us to use in the model (for joins, and what-not), but now we won’t unnecessarily expose it to the end users.