Variables SQL filters - Results don't correspond to filter

ErickCoimbra · March 11, 2021, 3:03pm

SELECT progress.progress_id AS progress_id, progress.user_id AS user_id, progress.enrollment_id AS enrollment_id, progress.course_id AS course_id, progress.module_id AS module_id, progress.lesson_id AS lesson_id, progress.resource_type AS resource_type, progress.resource_id AS resource_id, progress.time_in_seconds AS time_in_seconds, progress.percentage AS percentage, progress.created_at AS created_at, progress.updated_at AS updated_at
FROM progress
[[where {{aluno_id}} and {{curso_id}} and {{aula_id}} and {{conteudo_id}} and {{criado_em}} and {{atualizado_em}}]]

I am doing this query and I have set some optional variables in order to allow my end users to search for specific conditions.

With the exception of the last 2 variables all of the others are entities keys (ID).

eache and every association on the field filters are working just fine.

That been said, I am experiencing problems when executing any of the filters.

EX: I choose to see only results that correspond to a certain ID in the first column and the result, although it includes the one specified in the filter, brings me more IDs.

Does anybody know what’s that about?

flamber · March 11, 2021, 3:08pm

Hi @ErickCoimbra
Don’t add multiple variables in Optional Clauses unless you only want to run those, when all variables are active.
https://www.metabase.com/learn/building-analytics/sql-templates/field-filters.html
Do this instead:

where 1=1
[[and {{aluno_id}}]]
[[and {{curso_id}}]]
[[and {{aula_id}}]]
[[and {{conteudo_id}}]]
[[and {{criado_em}}]]
[[and {{atualizado_em}}]]

ErickCoimbra · March 11, 2021, 3:27pm

Thanks a lot for the quick reply Flamber!

It worked Perfectly.

It's been right in my face this whole time....hahaha.

Best Regards!