How to write a correlated subquery in a measure

[LookerExperts]

Knowledge Drop

Last tested: Dec 9, 2019
 

The Problem

I want to write a correlated subquery, but I'd like to do it without using derived tables or table calculations.

A Solution

We can take advantage of the fact that type: number measures allow us to write basically any SQL in order to write a subquery. (Warning: Not all databases support correlated subqueries. These examples are written for MySQL.)

1. Make a measure of type number so that Looker doesn't add any additional SQL.
2. For the sql parameter, write a subquery enclosed in parentheses. In this case, we want a category count, so it would look something like:
   sql: (SELECT count(category) FROM table) ;;
3. The tricky part here is that we want the count to listen to the email field in the table. So we need to write a correlated subquery to reference the outer email field. In this case it would look something like:
   sql: (SELECT count(category) FROM table WHERE users.email = `users.email`) ;;
   (When generating SQL for MySQL, Looker creates the field aliases in the general form `viewname.fieldname`.)
4. At this point, the field will break if it's selected without the email. The user would see an error saying "unknown field `users.email`". To avoid this error message, let's use the _is_selected liquid parameter to capture that potential case and show a bogus value like -1.
   sql: {% if users.email._is_selected %}

(SELECT count(category) FROM table WHERE users.email = `users.email`)

{% else %} -1 {% endif %} ;;
5. We can use the HTML parameter to show a nicer message whenever -1 is shown. (We could not have written this string in the SQL during step 4 because returning a string in a type: number measure returns null.)
   html: {% if value == -1 %}

Please select users.email in order to use this field.

{% else %} {{value}} {% endif %} ;;

That's it! Here is the full measure definition that we have been building up to:

measure: count_categories {

label: "Count of categories for this email address"

type: number

sql:

{% if users.email._is_selected %}

(SELECT count(category) FROM table WHERE users.email = `users.email`)

{% else %}

-1

{% endif %}

;;

html:

{% if value == -1 %}

Please select users.email in order to use this field.

{% else %}

{{value}}

{% endif %}

;;

}

This content is subject to limited support.