sql_always_having

LookML
Version

On this Page
Docs Menu

Go to Explore Parameter List

Usage

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

Definition

sql_always_having enables you to apply a query restriction that users cannot change. The restriction will be inserted into the HAVING clause of the underlying SQL that Looker generates, for all queries on the explore where sql_always_having 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_having 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 groups with less than 100 orders:

# Using Looker references - explore: order sql_always_having: ${count} >= 100   # Using raw SQL - explore: order sql_always_having: COUNT(*) >= 100

Prevent users from looking at groups with less than $1,000 in revenue:

- explore: customer sql_always_having: ${total_revenue} >= 1000

Prevent users from looking at groups with less than 100 customers:

- explore: order sql_always_having: ${customer.count} >= 100 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_having 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_having: SUM(customer.visits) >= 100 joins: - join: customer sql_on: ${order.customer_id} = ${customer.id}

In this case sql_always_having is referencing a column from the joined customer view, instead of the order explore. Since sql_always_having 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_having: SUM(customer.visits) >= 100 you used sql_always_having: ${customer.total_visits} >= 100, 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_having

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

sql_always_having: ${count} > 100 OR ${revenue} > 1000

write this:

sql_always_having: (${count} > 100 OR ${revenue} > 1000)

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

HAVING user_filter > 100 AND COUNT(*) > 100 OR SUM(revenue) > 1000

In this situation the filter that the user applied may not work. No matter what, rows with SUM(revenue) > 1000 will show up, because the AND condition is evaluated first. Without parentheses, only part of the sql_always_having condition combines with the user’s filter. If parentheses were added, the HAVING clause would look like this instead:

WHERE user_filter > 100 AND (COUNT(*) > 100 OR SUM(revenue) > 1000)

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

Only Use One sql_always_having 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_having 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 WHERE Clause

There is a very similar parameter to sql_always_having called sql_always_where that works in the same way, but applies conditions to the WHERE clause instead of the HAVING 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