Looker generating SQL Joins in the wrong order causing SQL Error

Looker Q&A Looker
1

Hello All,

I have been trying many way of solving this issue but couldn’t figure it out.

Context:

In an explore, I have couple joins, but one is quite tricky and the join condition are not ideal so far.

include: "*" #example

explore: my_explore {

  join: my_explore_parameter1 {
      sql: ;;
    relationship: one_to_one
  }

  join: my_explore_parameter2 {
    sql: ;;
  relationship: one_to_one
  }

  join: derived_table1 {
    type: left_outer
    foreign_key: primary_key
    relationship: one_to_one
  }

  join: derived_table2 {
    type: left_outer
    foreign_key: primary_key
    relationship: one_to_one
  }

  join: master_data {
    type: left_outer
    sql_on:
    ((${my_explore.model} = ${master_data.model} AND ${master_data.column1} != 'value_1') OR (${my_explore.model} = ${master_data.model_variant} AND ${master_data.column1} = 'value_1')) ;;
    relationship: many_to_many
    fields: [master_data*] #set
  }

  join: master_data_extended {
    type: left_outer
    sql_on: ${master_data.article} = ${master_data_extended.article} ;;
    relationship: one_to_one
    fields: []
  }

  join: master_data_variant_extended {
    type: left_outer
    sql_on: ${master_data.article} = ${master_data_variant_extended.article} ;;
    relationship: one_to_one
    fields: []
  }

}

The issue currently, is when one pick a field, it generates SQL but the code is in error because it introduces the table with alias and the joins are in the wrong order of declaration.

There is master_data_variant_extended being joined before alias master_data is introduced causing the error.

Having a simple ON condition does not trigger this bug.
But I couldn’t find a way to enforce the JOIN order.

SELECT my_explore.model AS my_explore_model

FROM `table1` AS my_explore

LEFT JOIN `master_data_variant_extended` AS master_data_variant_extended ON
master_data.article = master_data_variant_extended.article

LEFT JOIN `master_data` AS master_data ON
(my_explore.model = master_data.model AND master_data.column1
!= ‘value1’)
OR (my_explore.model = master_data.model_variant AND
master_data.column1 = ‘value1’)

LEFT JOIN `master_data_extended` AS master_data_extended ON
master_data.article = master_data_extended.article

GROUP BY
1
ORDER BY
1

If anyone encountered the same issue.

Thank you for your help

2

Is the example sql you posted what it’s doing or is that what you trying to get? Also when you say “JOIN order” are you just talking about when the clause shows up in sql string?