Join several views at once to an explore (making code DRY)

Looker Q&A Looker
1

I have a model in which serveral explores repeat the same join logic.

Imagine I have this diagram:

table_a -> table_x
           table_x -> table_y
           table_x -> table_z

table_b -> table_x
           table_x -> table_y
           table_x -> table_z

My model looks like this:

explore: table_a {
  join: table_x {
    type: left_outer
    sql_on: ${table_a.id} = ${table_x.id} ;;
    relationship: one_to_one
  }

  join: table_y {
    type: left_outer
    sql_on: ${table_x.other_id} = ${table_y.other_id} ;;
    relationship: one_to_one
  }

  join: table_z {
    type: left_outer
    sql_on: ${table_x.yet_another_id} = ${table_z.yet_another_id} ;;
    relationship: many_to_one
  }
}

explore: table_b {
  join: table_x {
    type: left_outer
    sql_on: ${table_b.id} = ${table_x.id} ;;
    relationship: one_to_one
  }

  join: table_y {
    type: left_outer
    sql_on: ${table_x.other_id} = ${table_y.other_id} ;;
    relationship: one_to_one
  }

  join: table_z {
    type: left_outer
    sql_on: ${table_x.yet_another_id} = ${table_z.yet_another_id} ;;
    relationship: many_to_one
  }
}

You can see the code is not very DRY. How can we improve this? I don’t think I can use extends because my from table is changing (it’s table_a first, then table_b). Since the joins are all left_outer I don’t have guarantees that all the records will be in tables x, y, and z, so I can’t start from these and use an extend…

2

How about a derived table? Something like this:

view: x_y_and_z {

  derived_table: {
    sql: <join x, y, and z here>
  }
  ...
  ...
}

In your Model, you will only need to join the x_y_and_z View:

explore: table_a {
  join: x_y_and_z
    type: left_outer
    sql_on: ${table_a.id} = ${x_y_and_z.id} ;;
    relationship: one_to_one
  }
}

explore: table_b {
  join: x_y_and_z
    type: left_outer
    sql_on: ${table_b.id} = ${x_y_and_z.id} ;;
    relationship: one_to_one
  }
}
3

you can extend and swap out the central table using “from:”

4

@PaulM1 a derived table would work, but I’ve been wanting to avoid these to keep SQL logic out of the Looker models…

@IanT could you elaborate more on how to do this? I’ve tried this but it didn’t work. The logic isn’t making much sense to me…

explore: table_xyz {
  view_name: table_x

  join: table_y {
    type: left_outer
    sql_on: ${table_x.other_id} = ${table_y.other_id} ;;
    relationship: one_to_one
  }

  join: table_z {
    type: left_outer
    sql_on: ${table_x.yet_another_id} = ${table_z.yet_another_id} ;;
    relationship: many_to_one
  }
}

explore: table_a_xyz {
  extends: 
  from: table_a
}<table>
</table>
5

What was the problem, you want to write out the whole explore starting with table a, then extend that and swap a for b

6

I typically do something similar to what @IanT has suggested. The only difference is the common explore which contains the repeated code I’d name it something like _table_x_base and then also include the extension: required parameter in that base explore. In that base explore I’d also have the join condition for table_x use the foreign_key parameter instead of the sql_on parameter so it doesn’t require a reference to the explore which extends _table_x_base.

If you can’t use the foreign key parameter trick because your ids in the tables aren’t called id, you’ll need to not only include extends: [_table_x_base] in the table_a explore but then you’ll also need to explicitly state the table_x join again (not all the other tables that table_x joins to) and specify the sql_on parameter (just the following is enough)

join: table_x {> sql_on: ${table_a.id} = ${table_x.id} ;;> }

This works because extends essentially copies the contents of the specified extended explore and then intelligently applies the code in the explore “on top”. For more details on how extends works this doc is really helpful and is the main way I DRY out my explore code. https://docs.looker.com/data-modeling/learning-lookml/extends