Variable List from SQL query with lookup table

Feedback and Requests
1

I really like metabase but I've run into a real issue.

I'm using a view for many of my questions/dashboards use that is very heavyweight and I really need to be able to write my own where statements instead of using field filter syntax. The field filter query is so ham handed when my view has millions of records and there are only 4 distinct values that it needs to group by. I can take minutes just to build the list of available options when I have those same four values in a lookup table.

I really need metabase to be able to define a filter from a SQL query like "select cat from cat_lookup" for my variable and it's values, when use that in my query like:

Select * from
anothertable
where cats in ( {{cat}} );

in my case the anothertable (view) has tens of millions of records that need to be grouped and ordered by if is use:

Select * from
anothertable
where {{cat}};

with field filter.

This has made it almost impossible to use metabase for my use case. Am I missing something or is there no way to do what I am attempting above?

2

Hi @csyperski
I don't think I understand your data, but sounds like you are looking for these - upvote by clicking :+1:
https://github.com/metabase/metabase/issues/4728
https://github.com/metabase/metabase/issues/5245

3

Odd repsonse :confused: , seems very dismissive. The problem is that I have many screens and dashboard and I need the re-usability of a view where we have already denormalized our data already as maintainability would be near impossible otherwise.

If we didn't denormalize via a view we'd be repeating our queries with many joins in every single question where we currently have hundreds, and an update would require touching them all vs recreating a view.

We denormalize via view because then on a single dashboard we can reuse that single view many times for many different metrics and it greatly reduces the complexity of the queries and the nesting of queries to get the data in required formats for the various graph requirements.

I don't understand why metabase thinks it's better to build a list like this:

select code from attendance group by code order by code; -- View with millions of rows containing only 50 distinct values

instead of

select code from attendance_codes order by code; -- Table with 50 rows

The later give more flexibility too if I want to filter or mutate my possible selections like

select code from attendance_codes where code_location = 'X' order by code; -- Table with 50 rows

Just my 2 cents, yes I see why you guys built things this way, but in the real world when you have dozens of dashboards with 12+ graphs and datatables on each, things would get unmanageable quickly.

4

@csyperski I didn't dismiss anything, but didn't understand what you were asking for.

Not all databases are structured equally, so in your example it would make sense to built it differently.

But you are essentially asking for https://github.com/metabase/metabase/issues/5245, which would give you the flexibility to construct the dropdown list with your own query.
There's also https://github.com/metabase/metabase/issues/6009 - and probably more if you try to search on Github, where all features and bugs are tracked.