Variables Field Filter


#1

Hi, I’ve this query:
SELECT c.business_name,b.lastname, b.firstname,(a.tot_time*b.costo_orario_ordi) as Costo FROM db_summary_syn a
LEFT join users b ON a.codfis=b.codfis
LEFT join db_companies c ON a.idcompany=c.id
WHERE b.onoff=1
[[ AND {{v_lastname}}]]
[[ AND {{v_firstname}}]]
[[ AND a.dataora={{v_dataora}}]]
ORDER BY c.business_name,b.lastname, a.dataora

v_lastname and v_firstname are linked to users.firstname and users.lastname.
When I select a value from this two value and run query, I received an error:
Unknown column ‘users.lastname’ in ‘where clause’

But users.lastname exist in my DB.

What am I doing wrong?
Thanks


Filters & use of table aliases
Category widget crash
WIKI: query issues (SQL/others)
#2

Hi,

So within the variable setting you selected the relevant fields as field filters, right?

have you tried to set text field filters, then the structure should be
AND v_lastname = {{var}}
the filter has to be a text field then.
Just to find out if the error occurs as well?

Another thing to try could be to use only one filter in the query for a start (delete the other two) and have a look if the error then occurs.

Let me know what happens!
Eva


#3

Hi Eva,
I’ve tried to modify query in this way:

SELECT c.business_name,b.lastname, b.firstname,(a.tot_time*b.costo_orario_ordi) as Costo FROM db_summary_syn a
LEFT join users b ON a.codfis=b.codfis
LEFT join db_companies c ON a.idcompany=c.id
WHERE b.onoff=1
[[ AND b.lastname={{v_lastname}}]]
ORDER BY c.business_name,b.lastname, a.dataora

Only one variable (v_lastname) set as text and…IT WORK.

It would seems a problem of the variable type on a joined table…

Antonio


#4

Hi Antonio,
I don´t think that the joined table should be the issue.
Have you tried using all three variables in this text filter way - does this work?
another way would be to have all three set in your way (with field filters) but without the “optional” brackets.
Does this work?
We will find out how to make it work :slight_smile:
Eva


#5

Hi,
With all three variables set as text, it work.
When I set variables as field filter (without brackets):

SELECT c.business_name,b.lastname, b.firstname,(a.tot_time*b.costo_orario_ordi) as Costo FROM db_summary_syn a
LEFT join users b ON a.codfis=b.codfis
LEFT join db_companies c ON a.idcompany=c.id
WHERE b.onoff=1
AND {{v_lastname}}
AND {{v_firstname}}
ORDER BY c.business_name,b.lastname, a.dataora

doesn’t work…


#6

Hi Antonio,
I recreated your query and you might be right - when I use field filters from joined tables I get the same error message.
That looks like a bug to me.
For a workaround, are you able to make either the users table your main table or use text filters?
Just for documentation, I use metabase v0.25, in case you use a newer version it would be worth mentioning it so that the metabase guys know where to find the bug.


#7

https://github.com/metabase/metabase/issues/3324 Hi again. have a look at that github issue, for me it works when I don´t give an alias to my tables.
So rather than

FROM db_summary_syn a
LEFT join users b ON a.codfis=b.codfis
LEFT join db_companies c ON a.idcompany=c.id

you should write:

FROM db_summary_syn
LEFT join users ON db_summary.codfis=users.codfis
LEFT join db_companies ON db_summary.idcompany=db_companies.id

its worth a try :slight_smile:


#8

Hi Eva,
yes, I can use variables as text filter.
My metabase version is 0.28.1.

Ok, I report the bug.

Thanks for the support Eva.
Bye!


#9

Just wrote you again - think there is a solution :slight_smile:


#10

Wow!! It’s the solution.
Without alias, work fine.

Thank you again Eva!