How to use field filter with model (postgres)

Get Help
1

I have a bit of an odd issue. In order to get the improved drilling-down from metadata but to avoid using the graphical query builder and also because of a metabase limitation preventing me adding multiple visualisations from the same query into a dashboard I want to save a model then use queries off of that and embed those child queries into a dashboard.

Currently I'm just using a simple test table to do so. My model is saved as the results of the basic SQL query

select * from table_name

And then I use a child question to build some visualisations with field filters (for now a basic date field filter) like this (assume the saved model is number 6)

select column1, column2 from {{#6}}
where {{date}}

But what gets odd is that I get an error 'ERROR: subquery in FROM must have an alias' so I fix that by doing

select column1, column2 from {{#6}} as "whatevername"
where {{date}}

And everything works fine as long as you don't actually pick a date. However when you actually select a date from the date field filter you get 'ERROR: missing FROM-clause entry for table' which I gather is caused by aliasing the select at the top.

So basically I'm trapped between two errors. How am I supposed to select from a model without being forced to alias it and thus being locked out of field filters?

1 Like
2

Hi @ametauser
Field Filters don't work with table aliases:
https://www.metabase.com/learn/sql-questions/field-filters.html#field-filters-are-incompatible-with-aliasing
And it is currently not possible to parse-thru filters from nested questions.
Upvote by clicking :+1: on the first post of each issue - read the comments for potential workarounds:
https://github.com/metabase/metabase/issues/6449
https://github.com/metabase/metabase/issues/3324

3

Thanks for the speedy reply.

Field Filters don't work with table aliases:

That seems to be a problem when referencing a model requires an alias to work at all since it makes it impossible to ever use field filters if you want to reference a model/saved question.

Presumably this is because the model reference is implemented behind the scenes as a sub-query and postgres at least requires subqueries to be aliased? If so automatically aliasing it (pulled from the current model name perhaps) and passing that alias behind the scenes to the field filter would work. Perhaps that is a comment for a github issue.

I suppose I can entirely cut models out of the picture and just use a materialised view in postgres and target all child queries at that but it seems then that things like dynamic drag-zoom won't be available?

And it is currently not possible to parse-thru filters from nested questions.

I don't have any filter set on the query that generates the model though, which is what's a bit confusing here. I just want to apply the filter to the child question. Perhaps I'm misunderstanding the issue or the way that field filters work.

4

@ametauser Field Filters generate the SQL needed (depending on the type). Doing anything automatically in SQL will require that Metabase can parse all the SQL, which is currently not possible.
Creating view can be a workaround in some cases.

5

Got it, thanks for your help. I'll probably take the materialised views path when I need the features.

6

@ametauser, I believe it's now fixed in version 0.45.0

7

I'm having to use models again to try and get actions working, the same issue from ~two years ago is still present so I'd say it probably isn't fixed.

What's weird is the metabase docs suggest SELECT * FROM {{#1-customer-model}} should work for selecting from a model but it will always cause an error with postgres.