required_joins

LookML
Version

On this Page
Docs Menu

Go to Join Parameter List

Usage

required_fields is a child of
join is a child of
joins is a child of
explore
explore: view_name_1
joins:
join: view_name_2
...
join: view_name_3
...
1st
Tab
2nd
Tab
3rd
Tab
required_joins: [view_name_2]
one or more join names
joined to this explore
(typically join names are
the same as their view names)

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.

There are two primary use cases for required_joins:

1. 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 joins: - 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.

2. 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 joins: - 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 joins: - 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.

Still have questions?
Go to Discourse - or - Email Support
Top