User Guide Getting Started Help Center Documentation Community Training
New LookML
Old LookML
New LookML
Looker
sql_where

Usage

explore: view_name_1 {
  join: view_name_2 {
    sql_where: ${view_name_1.id} < 100 ;;
  }
}

Hierarchy

sql_where

Default Value

None

Accepts

A SQL WHERE clause

Definition

Used only in BigQuery, sql_where enables you to 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_each } }

If the user selects Orders.Count and Users.Count, the SQL that Looker would generate from this LookML is:

SELECT COUNT(orders.id) AS orders_count, COUNT(DISTINCT users.id, 1000) AS users_count FROM thelook2.orders AS orders LEFT JOIN EACH thelook2.users AS users ON users.id = orders.user_id WHERE users.age < 50 LIMIT 500

Common Challenges

If you use OR logic with sql_where it’s very important to place parentheses around the SQL condition. For example, instead of this:

sql_where: region = 'Northeast' OR company = 'Acme' ;;

write this:

sql_where: (region = 'Northeast' OR company = 'Acme') ;;

If you forgot to add parenthesis in this example, and a user added their own filter, the generated WHERE clause could have the form:

WHERE user_filter = 'something' AND region = 'Northeast' OR company = 'Acme'

In this situation, the filter that the user applied may not work. No matter what, rows with company = 'Acme' will show up, because the AND condition is evaluated first. Without parentheses, only part of the sql_where condition combines with the user’s filter. If parentheses were added, the WHERE clause would look like this instead:

WHERE user_filter = 'something' AND (region = 'Northeast' OR company = 'Acme')

Now the user’s filter will be applied for every row.

Things to Know

The query restriction specified in sql_where 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. If you want to have a where clause applied even if the join wouldn’t have been used, use sql_always_where instead.

Top