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
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}}).
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.
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}}, ā%ā)]]
@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:
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ā)
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.
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ā¦