User Guide Getting Started Help Center Documentation Community Training
New LookML
Old LookML
New LookML
Looker
  
English
Français
Deutsch
日本語
sql_always_where

Usage

explore: explore_name {
  sql_always_where: ${created_date} >= '2017-01-01'
}

Hierarchy

sql_always_where

Default Value

None

Accepts

A SQL WHERE condition using dimension name(s) and/or SQL column name(s)

Special Rules

If you are referencing a SQL column name in sql_always_where that is part of a joined view rather than part of the Explore, it’s important to use the always_join parameter — or reference a field name instead

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 references like:

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' ;; 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' ;; 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.

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.

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

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.

Top