Metabase Workaround When Field Filter Fails if it References a Column From an Aliased Table

Metabase’s query builder seems to struggle to handle certain SQL constructs or complex joins properly. Because of this, field filters can sometimes fail if they reference columns from tables that are aliased or involved in complex joins.

When you google this problem, the first answers you will see is to not to use aliases but the original table names instead. But what if you want to join the same table twice? Or what if you have such a complex solution and so little time that you cannot rewrite everything just to make everything “alias-free”?

Non-working solution

SELECT o.*, c.username
FROM `order` o
JOIN customer c ON c.id = o.user_id
WHERE 1=1
[[AND {{my_customer_field_filter}}]]
[[AND o.createdAt BETWEEN {{start_date}} AND {{end_date}}]]
ORDER BY o.createdAt DESC;

Solution with CTE

My workaround was creating a CTE in which you query the original table and apply the field filter to it. Then use this CTE in the join, instead of the original table.

WITH cte_customer AS (
    SELECT id, username 
    FROM customer
    [[WHERE {{my_field_filter}}]]
)
SELECT o.*, c.username
FROM `order` o
JOIN cte_customer c ON c.id = o.user_id
[[WHERE o.createdAt BETWEEN {{start_date}} AND {{end_date}}]]
ORDER BY o.createdAt DESC;

Solution with subquery

The customer CTE is replaced by a subquery inside the JOIN. No alias is applied, until the field filter’s job is done. This subquery performs the same filtering as the original CTE:

SELECT o.*, c.username
FROM `order` o
JOIN (
    SELECT id, username 
    FROM customer
    [[WHERE {{my_field_filter}}]]
) c ON c.id = o.user_id
[[WHERE o.createdAt BETWEEN {{start_date}} AND {{end_date}}]]
ORDER BY o.createdAt DESC;

Using subqueries can have a negative impact on readability, especially when a query is already complex.