User Guide Getting Started Help Center Documentation Community Training
New LookML
Old LookML
New LookML
Looker
  
English
日本語
relationship

Usage

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

Hierarchy

relationship

Default Value

many_to_one

Accepts

A relationship (many_to_one, many_to_many, one_to_many, one_to_one)

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 { 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 four possible values for relationship are described below.

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 { 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 } }

Top