Filter using custom function for NON-SQL users

Get Help
1

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 :

SELECT *
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?
Thanks

2

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:

3

Hey,
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.

4

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

5

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.