Filter on third relationship doesn't work

Hi everyone.
I have one table (“Planos de Clientes”) that make relationship with another table (“Nome do Plano”), and this second table (“Nome do Plano”) make relationship with an third table (“Grupo de Serviços”).
Since I update to 0.37.x, filters with my third table (“Grupo de Serviços”) doesn’t work without make the joins on notebook editor.
Segments that I have with this type of filter stopped work too.

I can’t reproduce with Sample Dataset because the dataset is simple and don’t have this type of relationship.

Example - Without join:

Example - With join:

Diagnostic info:
{
“browser-info”: {
“language”: “en”,
“platform”: “Win32”,
“userAgent”: “Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.88 Safari/537.36”,
“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”: “3.10.0-1062.12.1.el7.x86_64”,
“user.language”: “en”,
“user.timezone”: “GMT”
},
“metabase-info”: {
“databases”: [
“postgres”,
“mysql”,
“h2”
],
“hosting-env”: “unknown”,
“application-database”: “postgres”,
“application-database-details”: {
“database”: {
“name”: “PostgreSQL”,
“version”: “9.6.20”
},
“jdbc-driver”: {
“name”: “PostgreSQL JDBC Driver”,
“version”: “42.2.8”
}
},
“run-mode”: “prod”,
“version”: {
“date”: “2020-12-03”,
“tag”: “v0.37.3”,
“branch”: “release-x.37.x”,
“hash”: “2f1e783”
},
“settings”: {
“report-timezone”: “America/Sao_Paulo”
}
}
}

Hi @yuriandreoli
Which version were you using before? Have you tried to downgrade to that version again?
You are filtering on different columns.
If you enable debug logging on your database, then you can see the queries being executed. You can also check the browser developer Network-tab to see the request, when running the query, which contains all the details in the response data.

I don’t remember what’s the exact version :pensive:

But I saw the SQL in Network-tab and the error is because Metabase is using the entity name in relationship, the correct is use the entity key.

@yuriandreoli I don’t understand. You are showing a table with a filter, and getting no results. And then you are showing a question with joins and a different filter, and you are getting results.
Without detailed information about your data model and the query, then it’s impossible to know what the problem is.
But I would recommend that you revert to your backup.

Below is my diagram:

image

The filter is the same, but without join, Metabase use the name of “Grupo de Serviços” and doesn’t work.

Video without join:

Video with join:

@yuriandreoli
In “Without Join”, you are filtering with the table “Nome do Plano”, which is referenced by FK (implicit).
While in “With Join”, you are filtering with the table “Grupo de Servicos” from the manual join (explicit).
Different tables will return different results.
Check the query by clicking “View the SQL” in the top-right corner of the Notebook editor.

And currently only a single-level FK are supported:
https://github.com/metabase/metabase/issues/12789 - upvote by clicking :+1: on the first post

Thanks @flamber, it’s exactly this.

The “bug” is the single-level FK support, I just upvoted in the post.
My segment doesn’t work too because is in “Planos de Clientes” and make reference to “Grupo de Servicos” directly.

Thanks for your help.

@flamber Look this, in this segment I had the filter (by “Grupo de Servicos”) inside table “Planos de Clientes”.

The values that I had show the ID after the name and the filter works.
If I add a new value, just the name appears and this filter doesn’t work.

@yuriandreoli That looks like this issue:
https://github.com/metabase/metabase/issues/13235 - upvote by clicking :+1: on the first post

1 Like

It’s exactly that, thanks @flamber