Field Filter not working with View

Hi, I created a small data mart in a PostgreSQL database and it has a dimensional table called dim_discipline, that contains 15 items. I would like to use a subset of this table in a Field Filter, but metabase always returns the entire values of the table.

My solution was to create a View in PostgreSQL with the subset of data from dim_discipline and use this View (called vw_discipline) as a Field Filter in my query. In this solution, my query became:

SELECT AVG(ft.ranking) AS average, dt.year AS year 
FROM my_fact_table ft
LEFT JOIIN vw_discipline vd ON vd.id  = ft.dim_discipline_id
WHERE 1=1
[[AND vd.name LIKE CONCAT('%', {{discipline_name}}, '%')]]
GROUP BY dt.year
ORDER BY dt.year ASC

The variable {{discipline_name}} is used as Field Filter and display properly the subset of data I wanted. However, when I select some value in the Filter and try to ā€œGet Answerā€ again, Metabase returns the error ā€œERROR: missing FROM-clause entry for table ā€˜vw_disciplineā€™. Hint: [ā€¦]ā€

I thought the problem was the syntax in my query, however, when I changed it to the version below (without filter) it worked:

SELECT AVG(ft.ranking) AS average, dt.year AS year 
FROM my_fact_table ft
LEFT JOIIN vw_discipline vd ON vd.id  = ft.dim_discipline_id
WHERE 1=1
AND vd.name LIKE '%DISCIPLINE_NAME%'
SELECT AVG(ft.ranking) AS average, dt.year AS year 
FROM my_fact_table ft
LEFT JOIIN vw_discipline vd ON vd.id  = ft.dim_discipline_id
WHERE 1=1
[[AND vd.name LIKE CONCAT('%', {{discipline_name}}, '%')]]
GROUP BY dt.year
ORDER BY dt.year ASC

I investigated the Metabase logs but it only displays this error. Is there some validation Metabase is doing that is not clear for me?

Hi, what metabase version are you using? and have you tried using the variable type text instead of a field filter? Does it return the same error message?
Cheers, Eva

Iā€™m using Metabase 0.27.2 (most recent one).

When I try to use text filter it works properly. However, to my end-users, it would be better to have this dropdown list with the values of vw_discipline, since not all of them have a previous knowledge of all the possible values and Metabase does not release the autocomplete option yet. Thatā€™s why Iā€™m trying to make the dropdown list work.

I also tried to change a little bit the query to use = instead of LIKE, but it is still not working.

SELECT AVG(ft.ranking) AS average, dt.year AS year 
FROM my_fact_table ft
LEFT JOIIN vw_discipline vd ON vd.id  = ft.dim_discipline_id
WHERE 1=1
AND vd.name LIKE '%DISCIPLINE_NAME%'
SELECT AVG(ft.ranking) AS average, dt.year AS year 
FROM my_fact_table ft
LEFT JOIIN vw_discipline vd ON vd.id  = ft.dim_discipline_id
WHERE 1=1
[[AND vd.name = {{discipline_name}} ]]
GROUP BY dt.year
ORDER BY dt.year ASC

A hypothesis is that Metabase is checking the foreign key link between my fact table and the dimensional table and is firing an exception because foreign key is related to dim_discipline and not vw_discipline. Can this hypothesis be correct? If yes, is there some alternative to this situation that allow me to use dropdown list?

Hi,
I use filters a lot (although we are still on v0.25 - but I guess they havenĀ“t changed the filter behaviour) and I also have a lot of joins, but I have never experienced that behaviour or error message before.

The only thing that comes to my mind is that when I use filters as text filters, they look like your example. But field filters (where the variable type is a field filter and you then choose the database field belonging to that filter) my filters look like [[AND {{field}}]] (so without "field = {{field}}).

Could you try that?
It reminds me a bit of an issue another user had Unable to get field filters to return data in a MySQL query.
Let me know if it worked!

I figured out the problem. It seems Metabase canā€™t understand very well table aliases when they are used without double quotes. This only happens when using Field Filters. So, my solution was to change my query to contain all the information about the table, considering the Schema, Table Name, Column Name and put everything between double quotes.

2 Likes

Ha thats interesting, thanks for sharing the solution!

Iā€™m trying to use the same approach in the query below with no luckā€¦ am I missing something?

SELECT count(*)
FROM ā€œpublicā€.ā€œtable_1ā€
Left join ā€œpublicā€.ā€œlist_tagsā€ ON ā€œpublicā€.ā€œtable_1ā€.ā€œtagsā€ = ā€œpublicā€.ā€œlist_tagsā€.ā€œtag_namesā€
WHERE 1=1
[[and ā€œpublicā€.ā€œtable_1ā€.ā€œtagsā€ LIKE concat(ā€™%ā€™, {{Tags}}, ā€˜%ā€™)]]

Hi @Rlmsh
You cannot use Field Filters like that. Field Filters creates their own SQL, so it should look like this:

 [[and {{Tags}}]]

If you need to do the LIKE '%...%', then you would need to sub-query that first got all the tags selected in the Field Filter, which you would then use in the LIKE.
https://metabase.com/docs/latest/users-guide/13-sql-parameters.html#the-field-filter-variable-type

Thanks @flamber

Iā€™m not proficient with sub-queriesā€¦ can you point me in the right direction to figure out the correct syntax? Thanks!

@Rlmsh
Can you explain what type of tags you have and why the user should be able to select them on a list, while you then still run them through LIKE?
If you just want the user to be able to write whatever they want, then just use the simple Text filter with the query you posted.

The tags are all strings, but the end user wonā€™t know the all possible options available so I want to give them a multi-select list of options. I currently use the text filter as a workaround but itā€™s not as useful to the users as a listā€¦

@Rlmsh Okay, if you just want to give the user a drop-down list, then just use the Field Filter like I showed before. Then when the user selects a tag, then the generated query will look like this:

and "table"."column" IN ("my tag", "other tag")

So like this? Think I am still getting it wrongā€¦

SELECT count(*)
FROM ā€œpublicā€.ā€œtable_1ā€
Left join ā€œpublicā€.ā€œlist_tagsā€ ON ā€œpublicā€.ā€œtable_1ā€.ā€œtagsā€ = ā€œpublicā€.ā€œlist_tagsā€.ā€œtag_namesā€
WHERE 1=1
[[and {{Tags}}]]
and ā€œtable_1ā€.ā€œtagsā€ IN (ā€œmy tagā€, ā€œother tagā€)

@Rlmsh Just this

SELECT count(*)
FROM "public"."table_1"
Left join "public"."list_tags" ON "public"."table_1"."tags" = "public"."list_tags"."tag_names"
WHERE 1=1
[[and {{Tags}}]]

Then the {{Tags}} filter will be set to Field Filter, and youā€™ll connect it to the table and column is should be looking at. If you read the documentation, then everything should be pretty well explained.

@flamber I should have been more clear, sorry.

The values in the target column are multiple and comma separated i.e ā€œTag1, Tag2, Tag5ā€, so selecting the Field Filter as a category doesnā€™t work as it will only return the exact match.

Thatā€™s why I need the LIKE '%...%' workaroundā€¦

First, shoot your database designer.
Then, depending upon database and number of tags either:

  1. create a view one boolean column for each tag value
  2. create a view that contains all the tag value - parent table values. Use that in a subquery (horrid bit of SQL though)
1 Like