Joining three tables of different grains based on two primary keys in Looker

Question

Essentially, I have three tables: Orders, Order Products, and Products. In Looker, you have to have a primary key to be able to do any sort of joins.

Orders has an Order ID (unique primary), Products has Product ID (unique primary), while Order Products has both of them (Duplicate order keys, and then one product ID for each product in the order). I am trying to join these three tables in Looker, and I've attempted using a compound key (product key + order key to create a unique identifier) but I've been unable to join the three tables since there's nothing to link Orders to Products.

Example:

explore: business { 
label: "order products" 
view_label "order products
}

join: orders { 
view_label: "Orders" 
relationship: many to one
type: full_outer
sql_on: order_products.order_id = orders.order_id;;

join: products {
view_label: "Products"
relationship: many_to_one
type: full outer
sql_on: order_products.product_id = orders.product_id

This does not work, and I am trying to join the three.

Does anyone have any suggestions regarding this?

Thanks

Answer 1

My understanding is that you are trying to explore the Orders so I would do this:

explore: orders {

  join: order_products { 
    relationship: one_to_many
    type: left_outer
    sql_on: order_products.order_id = orders.order_id;;
  }

  join: products {
    relationship: many_to_one
    type: left_outer
    sql_on: order_products.product_id = products.product_id
  }
}