foreign_key

LookML
Version

On this Page
Docs Menu

Go to Join Parameter List

Usage

foreign_key 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
foreign_key: dimension_name
a looker dimension name

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 joins: - 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 joins: - 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 joins: - 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 joins: - 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 joins: - 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

In order 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.

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