Query drops schema parameter used in "sql:" filter dimension field


(Ezra Wolfe) #1

We have a field that contains data we want to use in a filter suggest. However the data to be shown in the filter autocomplete needs to be restricted to a subset of results.

In order to get a dashboard filter to suggest only a subset of data, I tried to use a filter dimension and define the query to only pull the restricted values.

The query works in SQL runner, but not in the dashboard.

LookML:

  filter: parent {
    type: string
    sql: (SELECT name FROM {{ _user_attributes['schema']  }}.${TABLE} 
      WHERE (${TABLE}.type = 'grid'));;
 }

Generates this query:

SELECT 
	(SELECT name FROM myschema.webform_component
      WHERE (webform_component.type = 'grid')) AS "webform_component.parent"
FROM myschema.webform_component  AS webform_component
WHERE ((SELECT name FROM .webform_component
      WHERE (webform_component.type = 'grid'))) ILIKE '%'
OR ((SELECT name FROM .webform_component
      WHERE (webform_component.type = 'grid'))) ILIKE '% %'
GROUP BY 1
ORDER BY 1 
LIMIT 1000

Note how the schema is missing in SELECT name FROM .webform_component?

When I use SQL Runner to just run SELECT name FROM myschema.webform_component WHERE (webform_component.type = 'grid')) AS "webform_component.parent" it works as expected.

Is this not supported or is something wrong in the LookML?

If it’s not supported, what other options are recommended?


#2

Hey @Ezra_Wolfe,

Sounds like you might need the sql_table_name parameter here! This parameter is used at the beginning of the view file to specify the underlying table name. The table can be specified with both the schema and table name defined, telling Looker to include the schema scoping when generating the SQL.


(Ezra Wolfe) #3

The view does have the table name parameter. It looks like this:

 view: webform_component {
  label: "Webform components"
  sql_table_name: {{ _user_attributes['schema']  }}.webform_component ;;