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
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
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 user_facts
joined view would be one_to_one
, since both tables have one row per user.
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 eithermany_to_many
ormany_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
, because there may be multiple orders per user.
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
, because one order can contain multiple items..
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 a student has multiple classes and a class has multiple students. 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
}
}