Usage

explore: view_name_1 {  join: view_name_2 { ... }  join: view_name_3 {    required_joins: [view_name_2, ...]  }}

Definition

required_joins forces one or morejoins to be included in the SQL that Looker generates, even if the user has not selected a field from that joined view. This behavior is triggered whenever the user selects a field from a related view that you specify. Multiple joins can be required by using a comma-separated list like[join_name_a, join_name_b, ...].

When Looker generates SQL for a query, it attempts to create the cleanest SQL possible, and will only use the joins that are necessary for the fields a user selects. In the syntax diagram example at the top of this page:

• If a user only chose a field fromview_name_2, that would be the only view joined to the Explore.
• If a user selects a field fromview_name_3, then that join'srequired_joins parameter causesview_name_2 to be joined to the Explore.

The primary use cases forrequired_joins:

• Old syntax style withsql_on
• Need or desire to write raw SQL

Old syntax style withsql_on

sql_on does not needrequired_joins when it is used with${view_name.looker_dimension_name} syntax. However, some older models still use theview_name.native_column_name syntax. For example:

explore: order_items {  join: order {    sql_on: order_items.order_id = order.id ;;  }  join: customer {    sql_on: order.customer_id = customer.id ;;    required_joins: [order]  }}

In this example, whenever a user selects a field fromcustomer, theorder view must be joined in as well to maintain the proper join relationship. If you forget to require this queriesmight still work if the user happens to choose fields from all the required views. However, other queries may silently result in bad data due to the bad join.

Instead of usingrequired_joins, consider modifying the model to use the${view_name.looker_dimension_name} syntax.

Need or desire to write raw SQL

There are some cases when you cannot or do not want to use the${view_name.looker_dimension_name} syntax withsql_on. Typically this is because you want to use the raw values in your database and want to avoid any casting or other manipulations that occur with the${view_name.looker_dimension_name} syntax. Here is an example of the usage:

explore: order {  join: user {    sql_on: ${order.user_id} = ${user.id} ;;  }  join: pre_sign_up_events {    from: event    sql_on:      ${event.user_id} = ${user.id} AND      event.date BETWEEN user.creation_date AND user.sign_up_date ;;    required_joins: [user]    relationship: one_to_many  }}

In this example, thepre_sign_up_events join relies on dates fromuser. Consequently, it's important to make sure thatuser is joined in by usingrequired_joins.

Instead of usingrequired_joins to avoid casting with time or date fields, consider using thedate_raw type and avoid usingrequired_joins.

Common challenges

A view must be joined to an Explore before it can be referenced inrequired_joins

To place a view intorequired_joins, you need to make sure it is joined to theexplore where therequired_joins is being used. For example, this will not work:

explore: order_items {  join: customer {    sql_on: order.customer_id = customer.id ;;    required_joins: [order]  }}

Hereorder hasn't been joined toorder_items, so it isn't available for use inrequired_joins.