User Guide Getting Started Help Center Documentation Community Training
New LookML
Old LookML
New LookML
Looker
required_joins

Usage

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

Hierarchy

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:

The primary use cases for required_joins:

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, whenver 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 wish 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

If you want 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.

Top