Hi everyone,
I have a problem when using the Variable type specifically “Field Filter” where I select the Field to map to the column of another table that I want to appear in the filters and it gives me problems.
My SQL query is as follows:
SELECT product_all.order, count (*) AS “count”
FROM product_all
[[where product_all.name like {{product_part1_name}}]]
This problem arose when I wanted to show my students an example, it all started very simple and we have been testing the functionalities offered by metabase and this problem arose.
In my simple example I have two tables that are not related, one table has the name Products_ALL where it has as product name column and other fields as quantity and price, and the other table that I have as an example has the name Products_Part1 where it has as fields the name of the product and a name of the company but I am only interested in knowing the name of those products, also those products are also in the Products_ALL table. (I know that from the database point of view it is something crazy but it is only for test the features provided by metabase).
The objective is to show in that product filter the names of the products that are in the Product_Part1 table (only those that appear in that table and I mean when you click on the filter that is above the graphs or tables within a dashboard) not all products in the product_all table.
Rather it is a customization of the filter that allows me to edit those values that appear there. That is why I have reached this point.
After making the query gives us the following error:
org.postgresql.util.PSQLException: ERROR: operator does not exist: text ~~ integer Hint: No operator matches the given name and argument type (s). You might need to add explicit type casts. Position: 321
As I understand in the error message where that field
“Field Filter” where I select the Field to map is from the other table and the type of data is TEXT in both cases and it doesn’t why tells me that I am comparing with an INTEGER.
The problem is solved when I change in Variable type and change where I have Field Filter and put TEXT and there if it works correctly. Why does this happen? What do I have to do to make it work when I add a Field Filter to a field in a table?
Can someone help me with that problem?
Thank you
Best regards to all
Hi @jose1992ambrosio
When you use Field Filters, then you should not include the column in the where-clause, just the variable, since Metabase replaces it with SQL code.
So it should just be [[where {{product_part1_name}}]]
But you’re then missing Product_Part1 table in a from-clause or join-clause.
Sounds like it should looks something like this:
SELECT product_all.order, count (*) AS “count”
FROM Product_Part1
LEFT JOIN product_all ON product_all.blah=Product_Part1.blah
[[where {{product_part1_name}}]]
It would help a lot if you wrote a complete, working example without any Metabase filters - it makes it a lot easier to see how, where and which type of filter might be best.
Thank you very much, after investigating a little more I managed to realize the mistake I am making and now you confirm it too
After analyzing this problem and the solution provided by metabase I began to think that if this were the case, an example where we have a single table with many product names and one wants to create a filter for the user that interacts with the dashboard when selecting the filter the names of the products appear, even there everything great with metabase, but if the customer wants to not show all the names of those products in that filter, only those he wants, what solution would metabase provide for that case?
It would be interesting to make metabase have one more option to be able to customize those filters, and if you have that option I would like to know the way.
It has been very helpful. Excellent work.
@jose1992ambrosio
Okay, so that depends. If you want, you can make a LIKE filter with the Text Filter, but the user won’t get a dropdown. If you want the user to have a dropdown, where they select the products they want, then you can use Field Filter.
And if you’re really fancy, you can even add both options, so it’s up to the user which type of filter they prefer or combine if they want.
WHERE 1=1 [[ AND {{field_filter}} ]] [[ AND product_name LIKE CONCAT('%', {{text_filter}}, '%') ]]
You might need to adjust a bit depending on which type of database you’re querying.
The problem with filters arises in dashboards, since you cannot specify which type of search is done (like the filter widget on questions, you can say is, isn’t, contains, …).
There’s a feature request open on that - and there’s several Correctness-tagged issues about making Metabase more consistent (like having the same filter options on dashboards as on questions): https://github.com/metabase/metabase/issues/6023 - upvote by clicking on the first post
on the first post