Question

Using extensions to define joins

  • 2 November 2016
  • 2 replies
  • 281 views

Userlevel 3

When modeling, joins can be redefined in different explores. For example, wherever we join users, we will join in user facts and other tables in normalized schema designs. These “join chains” can lead to severe code bloat.


In this example, the join chain will be extended into other explores.


Here’s a starting generated model:


## Order Items view
explore: order_items {
view_name: order_items

join: users {
relationship: many_to_one
sql_on: ${order_items.user_id} = ${users.id} ;;
}

join: user_order_facts {
view_label: "Users"
relationship: many_to_one
sql_on: ${user_order_facts.user_id} = ${users.id} ;;
}

join: user_profile {
view_label: "Users"
relationship: many_to_one
sql_on: ${user_profile.user_id} = ${users.id} ;;
}
}

## Users view
explore: users {
view_name: users
join: user_order_facts {
view_label: "Users"
relationship: many_to_one
sql_on: ${user_order_facts.user_id} = ${users.id} ;;
}

join: user_profile {
view_label: "Users"
relationship: many_to_one
sql_on: ${user_profile.user_id} = ${users.id} ;;
}
}

Let’s convert use the Users explore as the extended explore into the Order Items explore:


## Order Items view
explore: order_items {
view_name: order_items
extends: [users]
join: users {
relationship: many_to_one
sql_on: ${order_items.user_id} = ${users.id} ;;
}
}

## Users view
explore: users {
view_name: users
join: user_order_facts {
view_label: "Users"
relationship: many_to_one
sql_on: ${user_order_facts.user_id} = ${users.id} ;;
}

join: user_profile {
view_label: "Users"
relationship: many_to_one
sql_on: ${user_profile.user_id} = ${users.id} ;;
}
}

The key feature of extends that makes this possible is that it takes an array of explores in order to construct the new explore. We can do this with multiple chains of common joins:


## Order Items view
explore: order_items {
view_name: order_items
extends: [users, orders]
join: users {
relationship: many_to_one
sql_on: ${order_items.user_id} = ${users.id} ;;
}
}

## Orders view

explore: orders {
view_name: orders
join: orders_product_facts {
view_label: "Orders"
relationship: many_to_one
sql_on: ${orders_product_facts.order_id} = ${orders.id} ;;
}
join: checkout_session {
relationship: many_to_one
sql_on: ${checkout_session.order_id} = ${orders.id} ;;
}
}

## Users view
explore: users {
view_name: users
join: user_order_facts {
view_label: "Users"
relationship: many_to_one
sql_on: ${user_order_facts.user_id} = ${users.id} ;;
}

join: user_profile {
view_label: "Users"
relationship: many_to_one
sql_on: ${user_profile.user_id} = ${users.id} ;;
}
}


These are simplified and ideal examples of using extends to manage long join chains. In the real world, we may not have a schema structure or explore structures that allow us to build something as elegant. We may also need to apply overrides to add or remove fields.


To reduce code and maintain flexibility is challenging to balance. Plan for the entire model before refactoring into this pattern.


2 replies

Userlevel 1

Hi


Thinking about doing something like this, could be very useful.

In the last example you have extends: [users, orders] but there is only a join to the explore users. How does the explore orders get used in this new explore?


Thanks

Hi

 

Thinking about doing something like this, could be very useful.

In the last example you have extends: [users, orders] but there is only a join to the explore users. How does the explore orders get used in this new explore?

 

 

Thanks

 

Wondering this too! Any help would be great @mikhailxu @kuopaz 

Reply