Skip to main content
When modeling topics, simplicity in naming conventions is key. Aliasing table joins allows you to build out tables based on logical concepts, using language your users will recognize.

The scenario: Buyers and sellers

In this example, we’ll focus on buyers and sellers. Our model has an orders table containing fields like seller_id and buyer_id and a users table where individual users are identified by a unique id: To segment the users table into buyers and sellers, we need to join either the buyer_id or seller_id field with the id field in the users table. Essentially, our goal is to join the same tables twice but in different ways each time: This is where aliasing becomes necessary. If we attempt to make both joins without aliasing, Omni will surface the following validation error in the model IDE: 
Relationship duplication: there are multiple relationships between "order_items" and "users". This may lead to surprising results. Use a different alias for one of the relationships.

Implementing aliases in the relationships file

In the model’s relationships file, we’ll define two joins: one for buyers and the other for sellers. There are three parts to defining aliased joins:
  • The join_to_view_as parameter, which creates the alias for referencing the join in the modeling layer.
  • The join_to_view_as_label parameter, which creates the label for the view in the workbook layer.
  • The on_sql parameter, which creates the join condition. Use the alias when defining the condition or you’ll encounter errors. For example, the buyers alias is used as the prefix in ${order_items.buyer_id} = ${buyers.id}.
buyers join
- join_from_view: order_items
  join_to_view: users
  join_to_view_as: buyers
  join_to_view_as_label: Buyers
  join_type: always_left
  on_sql: ${order_items.buyer_id} = ${buyers.id}
  relationship_type: assumed_many_to_one
sellers join
- join_from_view: order_items
  join_to_view: users
  join_to_view_as: sellers
  join_to_view_as_label: Sellers
  join_type: always_left
  on_sql: ${order_items.seller_id} = ${sellers.id}
  relationship_type: assumed_many_to_one