Joining to an aliased view

Modeling
,
1

Sometimes views need to be aliased or duplicated and can be automatically created using joins. In a previous article How to join the same table twice we used aliasing to join the same tables in different ways. In that case, we joined the same two tables order_items and users with differing join conditions and refer to the separate results using an alias, for one join buyers and in another it was sellers.

Often, the next step will be to create a relationship to this alias. In this article we’ll show you how to do that.

Worked example with steps

In this example we have 3 tables order_items, users and inventory_items. First we will create a table called first_orders for every user’s first order. Then we’ll join that result to inventory_items to get product information on the first order. (We’ll assume that this dataset only allows one order per timestamp, so users and order_items have a 1:1 relationship for the first order.)

  1. Create the first alias relationship (docs) using the table names in join_from_view, join_to_view and create the alias name in join_to_view_as:. In the join condition on_sql use the alias name instead of the table name. For this example it will look like the following:
#relationships file
- join_from_view: users
  join_to_view: order_items
  join_to_view_as: first_order
  join_type: inner
  on_sql: ${first_order.user_id} = ${users.id} and ${first_order.created_at}
    = ${users.created_at}
  relationship_type: one_to_one
  1. Create a topic (we’ll name it demo_topic) and add users as a base table and join the aliased table to it. We’ll add some labels in too using base_view_label and label (refer to topic parameter docs).
# model file
topics:
  demo_topic:
    base_view: users
    base_view_label: Users

    joins:
      first_order: {}

    label: Demo Topic
  1. Create the join to this alias. In relationships, use table names for join_from_view, join_to_view and create the alias name in join_to_view_as:. Again, in the join condition on_sql use the alias name instead of the table name. This example will call the result first_inventory_items.
## relationships file
- join_from_view: order_items
  join_to_view: inventory_items
  join_to_view_as: first_inventory_items
  join_type: always_left
  on_sql: ${order_items.inventory_item_id} = ${first_inventory_items.id}
  relationship_type: one_to_one
  1. Update the model file to include the relationship to the new joined in table
## updated model file
topics:
  demo_topic:
    base_view: users
    base_view_label: Users

    joins:
      first_order:
        first_inventory_items: {}

    label: Demo Topic

Further things to consider

If there are measures which we only want to use in the context of this relationship, it’s worth considering where to define these. If we define them in the users.view file, they will be included automatically in all topics that use that view, which might not be the desired behaviour. One way to solve this, would be to exclude this new metric (e.g. users.number_of_items_in_first_order) from other topic using the fields parameter like so:

fields: [ all_views.*, -users.number_of_items_in_first_order ]

Topic Scoped Views

If you don’t want to add these measures/dimensions to the view file and explicitly remove them from certain topics, you can use topic scoped views. With these we can add dimensions and metrics to a view just within a topic, so it doesn’t exist everywhere and we don’t have to explicitly remove it from other topics. Here’s how we would define the topic-scoped view by extending the original view just for the context of this topic.

# model file
topics:
  demo_topic:
    base_view: users
    base_view_label: Users

    joins:
      first_order:
        first_inventory_items: {}

    label: Demo Topic
# define a topic-scoped view to extend the definition of users just for this topic.
   views:
     users:
        measures:
           number_of_items_in_first_order:
           sql: ${count}*10000

Conclusion

To wrap up, in this article we covered how we’d join to an aliased table, to give us the added flexibility in data modeling to handle complex relationships.

When implementing the relationships, remember the parameters join_from_view and join_to_view should refer to the view name, the on_sql parameter should use the alias name defined in join_to_view_as parameter. In the model file where the topic is defined, use the alias names.

Finally we covered the pros and cons of where to define your dimensions and measures which may only be relevant for the relationships created with the aliases. If you want these dimensions and measures to appear in all topics, you can define them in the view file, but you’ll need to explicitly remove them from topics where they’re not needed. To add them to just one topic, you can use topic-scoped views.