with_relationships

info

This is part of our beta expression language AQL. Learn more. Request beta.

Definition

Specifies existing relationships to activate during calculating the measure

This function comes into handy when you have 2 tables that are connected through multiple paths, where only one path can be activated to avoid ambiguous path.

Syntax

with_relationships(measure, relationship, ...)

Examples

// Activate the relationship between order_items and products
sum(order_items.revenue) | with_relationships(order_items.product_id > products.id)

Input

• measure: A measure that you want to override the relationships

• relationship (repeatable): A relationship that you want to activate. It comes in 2 forms:

  • model1.column1 > model2.column2 (many-to-one). E.g. order_items.product_id > products.id
  • model1.column1 - model2.column2 (one-to-one). E.g. merchants.admin_id - users.id
  caution

  Only relationships that are already defined in the dataset can be used in with_relationships

Output

Measure with specified relationships.

caution

Relationships activated by with_relationships will have with priority over normal relationships, but the shortest relationships path will still always be used. For example, if you have 2 paths between order_items and products: order_items.product_id > products.id and order_items.order_id > orders.id > products.id, then the first path will be used even if you activate the second path in with_relationships.

---

Sample Usages

Consider the scenario where we have two facts models order_items and shippings, both connected to the dimension models products, orders, and countries.

In order to avoid ambiguous paths, for order_items relationships, we can only activate one link between order_items and products, and deactivate the other links. While deactivating these relationships, we still need to keep those definitions in order to reuse it in with_relationships.

Dataset e_commerce {
  (...)
  
  relationships: [
    // shippings relationships
    relationship(shippings.product_id > products.id, true),
    relationship(shippings.order_id > orders.id, true),
    relationship(shippings.country_id > countries.id, true),
    
    // order_items relationships
    relationship(order_items.product_id > products.id, true),
    relationship(order_items.order_id > orders.id, false), // deactivated
    relationship(order_items.country_id > countries.id, false) // deactivated
  ]
}

Then override the deactivated relationships in the measure/metric definition using with_relationships. We can also omit the link from order-items-products since we already activate it in the dataset.

Model order_items {
  (...)
  
  measure total_sales {
    label: 'Total Sales'
    type: 'number'
    definition: @aql 
      sum(order_items.revenue) | 
      with_relatonships(
        // You can omit the first relationship as it is already activated in the dataset
        // order_items.product_id > products.id,
        order_items.order_id > orders.id,
        order_items.country_id > countries.id,
      )
    ;;
  }
}