Usage
explore: view_name_1 { join: view_name_2 { foreign_key: dimension_name } }
Hierarchy
foreign_key |
Default Value
None
Accepts
A Looker dimension name
Special Rules
|
Definition
foreign_key
establishes a join relationship between a view and its Explore. Looker matches the dimension referenced by foreign_key
with the primary key of the joined view. You set the joined view's primary key by turning on primary_key
for the field that serves as the primary key.
A view can be joined directly to an Explore when using foreign_key
, or it can be joined through a second view that is already joined to that Explore.
An example of the first case, where a view is joined directly to the Explore, looks like this:
explore: order {
join: customer {
foreign_key: customer_id
}
}
Assuming that the primary key of customer
was named id
, the SQL that Looker generated would be:
SELECT ...
FROM order
LEFT JOIN customer
ON order.customer_id = customer.id
In the second case, a view is joined to an Explore through an intermediate view that is already joined to that Explore. An example of that would be:
explore: order_items {
join: order {
foreign_key: order_id
}
join: customer {
foreign_key: order.customer_id
}
}
Here, customer
cannot be joined directly to order_items
. Instead, it must be joined through order
. Assuming that the primary keys of both order
and customer
were named id
, the SQL that Looker generated would be:
SELECT ...
FROM order_items
LEFT JOIN order
ON order_items.order_id = order.id
LEFT JOIN customer
ON order.customer_id = customer.id
To make this work properly, you can see that we used the fully scoped field reference order.customer_id
when joining customer
, instead of simply customer_id
. If we had only used customer_id
, Looker would have tried to join customer
directly to order_items.customer_id
instead of through order.customer_id
.
Examples
Join the view named customer
to the Explore named order
by matching up the primary key from customer
with order.customer_id
:
explore: order {
join: customer {
foreign_key: customer_id
}
}
Join the view named customer
to the Explore named order_items
through the view called order
. Match up the primary key from customer
with order.customer_id
, and the primary key from order
with order_items.order_id
:
explore: order_items {
join: order {
foreign_key: order_id
}
join: customer {
foreign_key: order.customer_id
}
}
Join the views named order
and inventory_item
to the Explore named order_items
. Match up the primary key from order
with order_items.order_id
, and the primary key from inventory_item
with order_items.inventory_id
:
explore: order_items {
join: order {
foreign_key: order_id
}
join: inventory_item {
foreign_key: inventory_id
}
}
Common challenges
foreign_key
must reference a dimension name, not a column name
The foreign_key
parameter only takes a dimension name, not the column name in your underlying SQL database. Often times the dimension name and column name are identical, which may lead to the false conclusion that column names can be used.
A primary key must be defined in views joined with foreign_key
For foreign_key
to function properly, one of the dimensions in the joined view must be defined as the primary key of that view. A primary key is defined via the primary_key
parameter.
Since only a single dimension can be defined as a primary key, you cannot use foreign_key
with views that have a multi-column primary key. In such a situation you'll need to use sql_on
instead.
Things to know
foreign_key
is not the only way to join in Looker
Some join relationships cannot be established with foreign_key
. For example, the join may not use the primary key of the joined view, or it may require that multiple conditions are part of the join. In these situations, use sql_on
instead.