Filter using custom function for NON-SQL users

Hi all,

I have a table where I need to filter on a the key column using a custom mysql "id_to_binary" method to convert the "human friendly id" to binary.
Basically, using SQL questions, what I want is :

FROM my_table
WHERE my_table.binary_id = id_to_binary({{human_friendly_id}})

The problem is that I want to give the ability to do this to NON-SQL users who only use the regular question interface.

My main idea was to save a SQL question:

SELECT binary_id
FROM my_table
WHERE my_table.binary_id = id_to_binary({{human_friendly_id}})

And use this saved question as a join in a regular question but unfortunately the value of the variable {{human_friendly_id}} cannot be chosen this way and the query is always executed with the default value.

Unfortunately, because of this issue, I cannot filter on the binary value directly.

Is there a way to do this in metabase?

Hey, could you try the workaround from this thread?

Something like this?

SELECT binary_id
FROM my_table
WHERE cast(my_table.binary_id AS CHAR) = {{human_friendly_id}}

Unfortunately I do not have the right DB or test data for this, but let me know if it works :slight_smile:

This could work but only with a very small amount of data (which is not my case) as doing the SQL query this way will not allow mySQL to use index for performance.
With a small amount of data, I found a solution which is to first create a SQL question to basically do a SELECT binary_to_id(binary_id), [all other columns] and then declare the result of this query as a metabase model so you can then query it using regular questions. But this approach will also not use the primary index and will fail in my case.

Any reason why you don't create a view at the database level? or add that column to the table in question. I think if you are altering the column you will always lose the index

Not sure how a view on the database level could solve my issue with better performance than using a metabase model. Same thing with a generated non stored column.

Adding a extra column on the model could party solve the issue but is definitely not a good design as we are talking of the primary key which is used as foreign key in many other tables. Hence I feel that this kind of solution would make us duplicate a lot of data that would make our model worse. More generally, it feels wrong to me to have to modify the database itself so that a tool like metabase can read it correctly.