Last tested: May 15, 2020
Joins in explores have to have either a sql
(docs) or sql_on
(docs) parameter to define the relationship of one view to another.
Using sql_on
:
explore: order {
join: customer {
sql_on: ${order.customer_id} = ${customer.id} ;;
}
}
Using sql
:
explore: order {
join: customer {
sql: LEFT JOIN ON ${order.customer_id} = ${customer.id} ;;
}
}
This content is subject to limited support.
join: customer {
sql: LEFT JOIN ON ${order.customer_id} = ${customer.id} ;;
}
I tried this advice, but got an error:
Query execution failed: - Syntax error: Unexpected keyword ON at [….]
Looks like we need a table name between JOIN and ON
How to specify it?
Hi @tumikosha ! You can specify the table name by hard-coding it like so:
join: customer {
sql: LEFT JOIN public.customer on ${order.id} = ${customer.id} ;;
}
Unfortunately, there is no way to reference the table name as defined in the LookML view while writing in the model file. This one of the reasons that sql
is a deprecated parameter, and sql_on
is recommended instead!