Foreign keys

Get Help
1

Hey,

I saw that there’s an option to set foreign keys inside Metabase but unfortunately it gives me the option to connect only to fields that are named id. Are you planning on having the option to connect between tables with whatever fields the user wants?

Thanks,
Alon

2

You can connect to any field that has been labeled a Primary Key in the admin.

We autolabel anything with the name “id” and a few other cases , but you can always manually label things we’ve missed. If you do that, it will show up in the list of foreign keys targets.

3

Thanks for the quick response. Is there a way to have joins between tables without foreign and/or primary keys?

4

Depends on what you mean by “joins”.

We’re trying to avoid exposing joins explicitly but rather allow people to use connections in a way that feels closer to how their mental model of the underlying data works.

Could you give a concrete example? We’re currently revisiting how we expose “the other side” of FKs as well as working on query composition, and examples of problems real people are trying to solve would be very very useful =)

5

Let’s say that we have 2 tables: Sessions and Transactions.
I want to see for each platform (appears only in sessions) the # of transactions per day. A simple SQL query will look like:
select platform, count(*)
from sessions join transactions
on sessions.user_id = transactions.user_id
group by platform

Note that user_id is not a primary key in both tables but still creating a join between the two tables can give us interesting insights. In general I believe that joins shouldn’t be based only on FK and PK. I don’t think that there should be an issue if the user will create the “connection” through the admin panel before having it available while asking questions but it should have the freedom to connect every field he wishes. You can keep these connections to be restricted for fields of the same type even.

6

That’s an interesting angle.

We currently restrict FK’s to only pointing to primary keys, but we don’t really have any specific reason to, aside from making the list of targets manageable. I’ll open an issue to allow FKs to point to arbitrary fields.

7

Hi Sameer,

Whats the issue number?
When is this feature is planned?
I just tried Metabase and we liked to very much but this is blocker for us.
We were planning to use Metabase for internal analytics and internal platforms.

regards,
Tushar

8

So the feature ties into our use of arrays and M2M fields.

Eg for the example you gave of sessions and transactions, a given session can be connected to multiple transactions.

We currently only support pulling in information from a toOne connection, eg, being able to filter sessions, by the user’s country of residence.

Queries that involve toMany connections need SQL at the moment. For our internal use, we typically either ETL a table that corresponds to an enriched session in your case, or use a SQL view.

Would love to help you work through a way to figure out how to get it all to work, but out of the box gui tools that handle M2M relationships and arrays are pretty tricky =)