Conditional join in Question

[matthew.hofmann]

I want to left join an Address table to a Facility table in a question. There can be multiple types of addresses per facility (e.g. billing address, facility address, etc). On the Address table there is an enumtype field called ‘type’ that has various options (‘Billing’, ‘Facility’, etc). I want the ‘Facility’ type address joined to the Facility table. This is how I would do the join in sql:

select…
from facility
left join address on address.facility = facility.id and address.type = ‘Facility’

Can this type of join be done in a question? I did some looking at custom expressions and the case function but not sure how to get the syntax right.

Thanks for any help on offer!

[dwhitemv]

What database are you using?

Generally speaking, yes, you should be able to use an enum as part of a join condition.

On PostgreSQL, using an enum in a join condition with a custom expression of a bare string threw the error operator does not exist: character varying = my_enum_type. The setup for this was like this (note the dkim column is the enum):

The solution was to move the secondary join condition to a filter:

The execution plan was the same for both queries.

In the second case, Metabase casted the string to the enum, so it’s aware it’s there. That sounds like a bug; it should cast the join condition too.

I could have also defined an operator to handle the casting.

[dwhitemv]

Opened bug #63910 for this issue.

Missing cast in join condition between custom expression and enum