用法
explore: view_name_1 {
join: view_name_2 {
sql_where: ${view_name_1.id} < 100 ;;
}
}
sql_where
|
Hierarchy
sql_where |
Default Value
None
Accepts
A SQL WHERE clause
|
Definition
sql_where lets you apply a query restriction that users cannot change. The restriction will be inserted into the WHERE clause of the underlying SQL that Looker generates if and only if the join is used in the query. In addition to queries run by human users, the restriction will apply to dashboards, scheduled Looks, and embedded information that relies on that Explore.
The condition can be written in pure SQL, using your database's actual table and column names. It can also use Looker field references like ${view_name.field_name}, which is the preferred method, because Looker can be smarter about automatically including necessary joins. A sql_where condition is not displayed to the user, unless they look at the underlying SQL of any queries that they create.
Example
For example, you can specify that if the join to users is used, that only users younger than 50 should be included:
explore: orders_users_under_50 {
view_name: orders
join: users {
sql_on: ${users.id} = ${orders.user_id} ;;
sql_where: ${users.age} < 50 ;;
type: left_outer
}
}
如果用户选择 Orders.Count 和 Users.Count，Looker 将根据此 LookML 生成以下 SQL：
SELECT
COUNT(orders.id) AS orders_count,
COUNT(DISTINCT users.id, 1000) AS users_count
FROM thelook2.orders AS orders
LEFT JOIN thelook2.users AS users ON users.id = orders.user_id
WHERE users.age < 50
LIMIT 500
注意事项
如果您使用 OR 逻辑，则必须使用圆括号
如果您将 OR 逻辑与 sql_where 搭配使用，请务必在 SQL 条件周围添加英文圆括号。例如，不要编写以下代码：
sql_where: region = 'Northeast' OR company = 'Altostrat' ;;
您应编写如下代码：
sql_where: (region = 'Northeast' OR company = 'Altostrat') ;;
如果您忘记在此示例中添加圆括号，并且用户添加了自己的过滤条件，则生成的 WHERE 子句可能采用以下形式：
WHERE
user_filter = 'something' AND
region = 'Northeast' OR
company = 'Altostrat'
在这种情况下，用户应用的过滤条件可能无法正常发挥作用。无论如何，包含 company = 'Altostrat' 的行都会显示，因为 AND 条件会先进行评估。如果不使用圆括号，则只有部分 sql_where 条件会与用户的过滤条件结合使用。如果添加了圆括号，WHERE 子句将如下所示：
WHERE
user_filter = 'something' AND
(region = 'Northeast' OR company = 'Altostrat')
现在，系统将为每一行应用用户的过滤条件。
对于 sql_where 依赖项，联接顺序很重要
一般来说，无论 LookML 中联接的定义顺序如何，Looker 都会按正确的顺序实现联接。但 sql_where 除外。如果您在 sql_where 语句中引用了另一个联接中的字段，则您引用的联接必须在 LookML 中的 sql_where 语句之前定义。
例如，以下 sql_where 语句在 inventory_items 加入之前引用了 inventory_items.id 字段：
explore: orders {
hidden: yes
join: order_items {
sql_on: ${order_items.order_id} = ${orders.id} ;;
sql_where: ${inventory_items.id} IS NOT NULL ;;
}
join: inventory_items {
sql_on: ${inventory_items.id}=${order_items.inventory_item_id} ;;
}
}
如果您在此探索中运行查询，Looker 会返回找不到 inventory_items.id 字段的错误。
不过，您可以通过重新排序联接来解决此问题，以便在 sql_where 语句之前定义 sql_where 语句中引用的联接，如下所示：
explore: orders {
hidden: yes
join: inventory_items {
sql_on: ${inventory_items.id}=${order_items.inventory_item_id} ;;
}
join: order_items {
sql_on: ${order_items.order_id} = ${orders.id} ;;
sql_where: ${inventory_items.id} IS NOT NULL ;;
}
}
这样可以防止出现错误，因为 inventory_items 联接是在 order_items 联接的 sql_where 语句中引用 inventory_items.id 字段之前定义的。
只有在使用联接时，才会应用 sql_where 查询限制
只有当查询中使用了联接时，Looker 生成的基础 SQL 的 sql_where 中指定的查询限制才会插入到 WHERE 子句中。如果您希望即使不使用联接也应用 WHERE 子句，请改用 sql_always_where。