How to apply field filters within nested queries?


#1

Hello people!

I´m struggling with this: I have a nested query and want to apply a field filter to the inner join, but it won’t show up for me as a filter (it shows up in the variables menu though):

select
    date,
    b.name

from
    transactions a
    inner join (select user_id, name from users [[where name = {{name}}]]) b on b.user_id = a.user_id

where 1=1
    [[and date = {{date}}]]

The date field works just fine. I´ve tried using the variable in the outer where as well.

Any ideas how to make this work?


#2

Hi @PabloBrenner
I’m not sure how the date is even functioning, since there’s only a single bracket. Does this work?

select
    date,
    b.name

from
    transactions a
    inner join (select user_id, name from users where 1=1 [[and name = {{name}}]]) b on b.user_id = a.user_id

where 1=1
    [[and date = {{date}}]]

#3

Thanks for the reply! I forgot the bracket when posting.

I´ve tried your suggestion just now, the variable shows up in the Variables menu and I´m able to set it as a field filter and select the column name within users table for it to map. The problem is that the filter won´t show up at the question header as the Date filter does.

Thanks!


#4

If you’re using Field Filter, then you should not include the column, meaning name={{name}} should just be {{name}}
And there’s most likely an issue with you data model - go to Settings > Admin > Data Model, and find your table and column, and change the Type of the column.


#5

It was the data model! I mapped the field name as Entity Name and it worked just fine!