Usage

explore: view_name {
  join: view_name_2 {
    relationship: one_to_one
  }
}

Hierarchy

Model File

explore

join

relationship

Default Value

many_to_one

Accepts

A relationship (many_to_one, many_to_many, one_to_many, one_to_one)

Definition

relationship lets you 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 { join: user { sql_on: ${order.user_id} = ${user.id} ;; relationship: many_to_one } join: user_facts { sql_on: ${user.id} = ${user_facts.user_id} ;; relationship: one_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_facts joins through user to order in this example — the relationship being defined is from the intermediate view (user) to the final view (user_facts). In this example we’re saying there is one user for one user fact record.

The possible values for relationship are described in these sections of this page:

one_to_one
many_to_one (default value)
one_to_many
many_to_many

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.

A one_to_one relationship requires that there be no null values in the primary keys of the tables. If there are null values in one or both of the primary keys of the tables, the relationship is either many_to_many or many_to_one.

many_to_one (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 next example. 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 { 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 { 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 { 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 { 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 { 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 } }

Articles in this section