required_joins

Usage

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

Hierarchy Model File explore join required_joins

Default Value None Accepts Square brackets containing a comma-separated list of joins for this Explore Special Rules A view must be joined to an Explore before it can be referenced in required_joins

Definition

required_joins forces one or more joins 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 from view_name_2, that would be the only view joined to the Explore.
• If a user selects a field from view_name_3, then that join's required_joins parameter causes view_name_2 to be joined to the Explore.

The primary use cases for required_joins:

• Old syntax style with sql_on
• Need or desire to write raw SQL

Old syntax style with sql_on

sql_on does not need required_joins when it is used with ${view_name.looker_dimension_name} syntax. However, some older models still use the view_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 from customer, the order view must be joined in as well to maintain the proper join relationship. If you forget to require this queries might 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 using required_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 with sql_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, the pre_sign_up_events join relies on dates from user. Consequently, it's important to make sure that user is joined in by using required_joins.

Instead of using required_joins to avoid casting with time or date fields, consider using the date_raw type and avoid using required_joins.

Common challenges

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

To place a view into required_joins, you need to make sure it is joined to the explore where the required_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]
  }
}

Here order hasn't been joined to order_items, so it isn't available for use in required_joins.