Filters & use of table aliases

Of the two following field filter queries the first one runs fine (widget, dropdowns work as they should), the second one with the alias throws the error “Invalid column name ‘com’.”:

SELECT COUNTY_N, COUNTY_D, DISTRICT_N
FROM [DashBase].[com].[COUNTY]
WHERE {{DISTRICT}}

SELECT COUNTY_N, COUNTY_D, DISTRICT_N
FROM [DashBase].[com].[COUNTY] C
WHERE C.{{DISTRICT}}

Any way around this error when making use of table aliases? With complex queries aliases are indispensable.

v0.28, default MySQL, synced to SQL Server

Hi,
this is a known issue, you can´t use aliases when you use certain filters:

cheers, Eva

Thank you so much Eva! I was going bonkers trying to figure out what I was doing wrong! For my purposes, for the time being, and for others facing the same issue with Field Filters unable to accommodate Table aliases, I can see only two options:

  1. Creating a View with everything Questions & Dashboards need; key fields, numerical codes, text labels, metrics, and so forth with essentially the same performance latency as a complex query, or
  2. Creating a MOAT (Mother Of All Tables) based on the same which hopefully would return information far more quickly for a more satisfying user experience.

And thanks for pointing out GitHub’s Issues section, I’ll search that as well as this forum in the future!

1 Like

@mesquest - when I started using Metabase, I did everything using SQL queries. As a BI developer, it was how I felt most comfortable and some of what I was trying to do required many tables.
Then I hit a problem with 0.28 with the multi-select filters not working with SQL queries, so I converted everything to views.
Now, I’m starting to see how well Metabase works with regular tables, so I’m planning to gradually make some of the tables visible and possibly augment with some views when necessary.
It really does do a nice job of managing the joins between tables. The only problem I can see at the moment is that I have a ‘Person’ table that can be used in different ways (carer, client, other), but I can work around that using some very simple views. This will be much more flexible than the approach of one view per question.

I think it’s worth perservering with just tables when possible, even though there will be times when you have to resort to a view.

2 Likes

Oh definitely, using a single or a set of views rather than one per question is the way to go as a table alternative.

Andrew, you captured some “zen of Metabase” very nicely here: :smiley:

Note, I’m reading the excerpt above with ideas on ways to use Metabase shared in the whole Best way to overcome lack of joins thread in mind. I think that is well worth a read if you are coming to Metabase with an “I-need-SQL-to-build-something-powerful” viewpoint.

1 Like

Thanks! The foreign key field swapping out of a numerical field for a text field label will save me lot’s of unnecessary joins and speed things up for users. That’s a really nice feature that I just discovered just yesterday and I’ll surely use it wherever I can!

Hi All,
I have a problem that I want to create a variable of type filter that is not available in the table that i am using. How to get it done if anyone has any idea, kindly help.
Eg -

select *
from abc
inner join def on abc.x = def.y
where {{variable}}
This variable belongs to a table def.

Hi @Akii1997
It’s not a problem to use a joined table when using Field Filter, but you cannot use aliases.

Welcome @Akii1997.

In the Metabase Documentation under SQL Parameters there's a really good explanation of how to formulate variables for filters. A couple of tricks that may not be so clear and lots of users had to learn the hard way that may save you some time:

  1. create a view of what you've already done and use it instead, that way the entire table referencing issue is gone, Metabase works best with a single table or view,
  2. don't use table aliases (you don't appear to here which is good), Metabase doesn't support them,
  3. start off every WHERE statement with WHERE 1=1 or some innocuous required statement like it, followed by your AND this, that, or the other kind of statement you're defining (no idea why, it just works, just do it)

One other option in your case is the following post but you'd have to follow-up with the author because I can't quite make out what his solution means by "put everything between double quotes", I've never been able to make it work!

Another thing is Metabase has a GitHub site where you can sign-in and explore various technical issues users are having and propose solutions -- it's not for asking general how-to type questions, that's what this forum is for. But you can gain allot of insight there, participate in the discussions, propose solutions, request enhancements, etc. For example, I'm particularly interested in Limit the number of options displayed in a filter based on the selection of another filter. #5540 and so I encourage everyone who inquires about something like this on this forum (and there are ALLOT of users who ask for this one!) to go there and "Upvote" :+1: the first post for that issue -- the Metabase team uses these as a guide to what features users need help with and enhancements they need.

Hope this helps!