always_join

LookML
Version

On this Page
Docs Menu

Go to Explore Parameter List

Usage

always_join is a child of
explore
explore: view_name
...
1st
Tab
always_join: [view_name]
one or more view names
joined to this explore

Definition

always_join forces one or more join to be included in the SQL that Looker generates, even if the user has not selected a field from that joined view. Multiple joins can be required by using a comma-separated list like [view_name_a, view_name_b, etc].

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. By using always_join, you can force joins to occur no matter what.

always_join may be valuable when a join is executed with the type parameter, and the join is not a LEFT JOIN. In such a situation the join may be critical to correctly limiting the rows that are returned.

Examples

Make sure that member is always joined to event, even if the user does not choose a field from member. This limits the results to only look at member generated events:

- explore: event always_join: [member] joins: - join: member sql_on: ${event.member_id} = ${member.id} type: inner

Make sure that member and payment are always joined to event, even if the user does not choose a field from either of those views. This limits the results to only look at member generated events where the member has paid already:

- explore: event always_join: [member, payment] joins: - join: member sql_on: ${event.member_id} = ${member.id} type: inner - join: payment sql_on: ${member.payment_id} = ${payment.id} type: inner

Common Challenges

A view Must Be Joined To An explore Before It Can Be Referenced In always_join

If you want to place a view into always_join, you need to make sure it is joined to the explore where the always_join is being used. For example, this will not work:

- explore: event always_join: [member]

Here member hasn’t been joined to event, so it isn’t available for use in always_join.

Things to Know

Don’t Apply Business Logic In Joins If Possible

The standard Looker approach to joining is to use a LEFT JOIN whenever possible. In the above examples, we avoid a LEFT JOIN so that business logic can be applied within the join itself. For instance, in one of the examples we created an explore that only included events that were associated with members:

- explore: event always_join: [member] joins: - join: member sql_on: ${event.member_id} = ${member.id} type: inner

The preferred way to execute this in Looker would be to use a LEFT JOIN to get event data and member data stuck together simply, like this:

- explore: event joins: - join: member sql_on: ${event.member_id} = ${member.id}

Then you would create a dimension that you could set to yes or no, if you only wanted to look at member events, like this:

- dimension: is_member_event type: yesno sql: ${member.id} IS NOT NULL

This approach is preferable because it gives users the flexibility to look at all events, or only member events, as they desire. You have not forced them to only look at member events via the join.

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