sql_always_where

LookML
Version

On this Page
Docs Menu

Go to Explore Parameter List

Usage

sql_always_where is a child of
explore
explore: view_name
...
1st
Tab
sql_always_where: ${field_name} = 1
a sql where condition

Definition

sql_always_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, for all queries on the explore where sql_always_where is used. 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_always_where condition is not displayed to the user, unless they look at the underlying SQL of any queries that they create.

Examples

Prevent users from looking at orders before 2012-01-01:

# Using Looker references - explore: order sql_always_where: ${created_date} >= '2012-01-01'   # Using raw SQL - explore: order sql_always_where: DATE(created_time) >= '2012-01-01'

Prevent users from looking at customer information for Acme Corporation:

- explore: customer sql_always_where: ${name} <> 'Acme Corporation'

Prevent users from looking at orders from Acme Corporation:

- explore: order sql_always_where: ${customer.name} <> 'Acme Corporation' joins: - join: customer sql_on: ${order.customer_id} = ${customer.id}

Common Challenges

If You Use Raw SQL You Might Need To Use always_join

If you are referencing a SQL column name in sql_always_where that is part of a joined view, instead of the explore, it’s important to use the always_join parameter. Consider this example:

- explore: order sql_always_where: customer.name <> 'Acme Corporation' joins: - join: customer sql_on: ${order.customer_id} = ${customer.id}

In this case sql_always_where is referencing a column from the joined customer view, instead of the order explore. Since sql_always_where will be applied to every query, it’s important that customer is also joined in every query.

When Looker generates SQL for a query, it attempts to create the cleanest SQL possible, and will only use the joins that are necessary for the fields a user selects. In this case, Looker would only join customer if a user selected a customer field. By using always_join, you can force the join to occur no matter what.

If, instead of sql_always_where: customer.name <> 'Acme Corporation' you used sql_always_where: ${customer.name} <> 'Acme Corporation', Looker would be smart enough to make the customer join without requiring you to use always_join. For this reason, we encourage you to use Looker field references instead of raw SQL references when possible.

You Must Use Parentheses When Using OR Logic With sql_always_where

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

sql_always_where: region = 'Northeast' OR company = 'Acme'

write this:

sql_always_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_always_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.

Only Use One sql_always_where per Explore

You should only have one sql_always_where in an explore definition. Put all of the desired behavior into a single sql_always_where by using AND and OR as needed. If you are using OR, be sure to use parentheses as described above.

Things to Know

There is a Similar Parameter for the SQL HAVING Clause

There is a very similar parameter to sql_always_where called sql_always_having that works in the same way, but applies conditions to the HAVING clause instead of the WHERE clause.

If You Want Filters A User Can Change, But Not Remove, Consider always_filter

If you want to force users to use a specific set of filters, but where the default value can be changed, try always_filter instead.

If You Want User Specific Filters That Can’t Be Changed Consider access_filter_fields

If you want an explore to have filters that are specific to each user, and cannot be changed in any way, you can use access_filter_fields.

Still have questions?
Go to Discourse - or - Email Support
Top