relationship

LookML
Version

On this Page
Docs Menu

Go to Join Parameter List

Usage

relationship is a child of
join is a child of
joins is a child of
explore
explore: view_name_1
joins:
join: view_name_2
...
1st
Tab
2nd
Tab
3rd
Tab
relationship: one_to_one
a join relationship
one_to_one
one_to_many
many_to_one
many_to_many

Definition

relationship enables you to describe the join relationship between joined views. It’s important to properly define the type of relationship in order for Looker to calculate accurate measures.

To understand the relationship that is being defined, consider this example:

- explore: order joins: - join: user sql_on: ${order.user_id} = ${user.id} relationship: many_to_one   - join: user_type sql_on: ${user.type_id} = ${user_type.id} relationship: many_to_one

When a view is joined directly to an explore, like the user view in this example, the relationship is from the explore to the joined view. We’re saying here that there could be many orders for one user.

When a view is joined to an explore through another view — such as how user_type joins through user to order in this example — the relationship being defined is from the intermediate view (user) to the final view (user_type). In this example we’re saying there could be many users for one user_type.

The four possible values for relationship are:

one_to_one

If one row in the explore can only match one row in the joined view, the relationship is one_to_one. For example, a user explore with a dna joined view would be one_to_one.

many_to_one (the default value)

If many rows in the explore can match one row in the joined view, the relationship is many_to_one. For example, an order explore with a user joined view would be many_to_one.

one_to_many

If one row in the explore can match many rows in the joined view, the relationship is one_to_many. For example, an order explore with an item joined view would be one_to_many.

many_to_many

If many rows in the explore can match many rows in the joined view, the relationship is many_to_many. For example, a student explore with a class joined view could be many_to_many if the schema was designed like the example below. In practice, many-to-many relationships are often avoided in SQL database design so most models do not have a need for many_to_many.

Examples

Declare the user to dna relationship as one_to_one:

- explore: user joins: - join: dna sql_on: ${user.dna_id} = ${dna.id} relationship: one_to_one

Declare the order to user relationship as many_to_one:

- explore: order joins: - join: user sql_on: ${order.user_id} = ${user.id} relationship: many_to_one

Declare the order to item relationship as one_to_many:

- explore: order joins: - join: item sql_on: ${order.order_id} = ${item.order_id} relationship: one_to_many

Declare the student to class relationship as many_to_many:

- explore: student joins: - join: class sql_on: ${student.student_id} = ${class.student_id} relationship: many_to_many

Declare the user to user_type relationship as many_to_one:

- explore: order joins: - join: user sql_on: ${order.user_id} = ${user.id} relationship: many_to_one   - join: user_type sql_on: ${user.type_id} = ${user_type.id} relationship: many_to_one

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