Category Field Filter not working on Countries and Boolean Fields

Hi,

I am struggling to get this query working with the filter:

select co.country_name, u.id
from "rails".users u
join "public".countries co
on u.country = co.id. 
where 1=1
[[and {{country}}]];

I have mapped the country filter to the “public”.countries, country_name column in the filter widget.

When I do not use the filter I am get the table.

When I do use the filter, say filter for United States of America, I am getting this error:

[Amazon](500310) Invalid operation: invalid reference to FROM-clause entry for table "countries";

I have googled it and the error comes up when the column being referenced is not called in the from clause.

Any help is greatly appreciated.

Thank you!
Vinayak

Version of metabase:
{
“browser-info”: {
“language”: “en-US”,
“platform”: “MacIntel”,
“userAgent”: “Chrome/Version 84.0.4147.89 (Official Build) (64-bit)”,
“vendor”: “Google Inc.”
},
“system-info”: {
“file.encoding”: “UTF-8”,
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “11.0.7+10”,
“java.vendor”: “AdoptOpenJDK”,
“java.vendor.url”: “https://adoptopenjdk.net/”,
“java.version”: “11.0.7”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “11.0.7+10”,
“os.name”: “Linux”,
“os.version”: “4.14.181-140.257.amzn2.x86_64”,
“user.language”: “en”,
“user.timezone”: “GMT”
},
“metabase-info”: {
“databases”: [
“postgres”,
“h2”,
“redshift”
],
“hosting-env”: “elastic-beanstalk”,
“application-database”: “postgres”,
“application-database-details”: {
“database”: {
“name”: “PostgreSQL”,
“version”: “11.6”
},
“jdbc-driver”: {
“name”: “PostgreSQL JDBC Driver”,
“version”: “42.2.8”
}
},
“run-mode”: “prod”,
“version”: {
“date”: “2020-05-28”,
“tag”: “v0.35.4”,
“branch”: “release-0.35.x”,
“hash”: “b3080fa”
},
“settings”: {
“report-timezone”: null
}
}
}

Hi @VinayakK
Because you cannot use table aliases with Field Filters:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#the-field-filter-variable-type
https://github.com/metabase/metabase/issues/3324 - upvote by clicking :+1: on the first post

1 Like

Hi @flamber,

Thank you very much for the fast reply. I have replaced all table aliases and it works now.