foreign_key is a child of
join is a child of
joins is a child of
|a looker dimension name|
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:
Assuming that the primary key of customer was named id, the SQL that Looker generated would be:
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:
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:
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.
Join the view named customer to the explore named order by matching up the primary key from customer with order.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:
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:
foreign_key Must Reference A Dimension Name, Not A Column Name
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
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
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
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