Filter on multiple joins, same table

Get Help
1

How can I define a pair of field filters for a report, where both filters refer to the same joined table but the table is joined more than once?

Example: let's say we have an airport system, with a fact table called "arrivals", with "airport_from_id" and "airport_to_id" columns, both referring the dimension table named "airports".

So the query for the report would be something like:

SELECT...
...
FROM arrivals
JOIN airports as airport_from ON arrivals.port_from_id = airport_from.id
JOIN airports as airport_to ON arrivals.port_to_id = airport_to.id
...
WHERE...
[[AND {{airport_from}}]]
[[AND {{airport_to}}]]
AND ...;

I need to create a Field Filter for airport_from and another (independent) Field Filter for airport_to. However, it is not possible to create a Field Filter based on an aliased table, and I cannot use the same table more than once in a query without using aliases.

Catch 22 or am I missing something?

Any hints welcome.

2

Hi @piotr1
Try looking in some of the examples - also upvote the issue:
https://github.com/metabase/metabase/issues/3324 - upvote by clicking :+1: on the first post
Alternatively, you could create a new on your database.

1 Like
3

Answering my own question, it seems that creating multiple views based on the "airports" table and using these views in the query works. It is not the most elegant trick in the world, but it is good enough until a feature of using aliased tables for field filters is introduced.

4

@piotr1 Yeah, I think me brain skipped a word. Sorry about that.

Alternatively, you could create a new View on your database.

1 Like