Joining non-foreign keyed tables in Supabase

Ask us anything How do I?
1

Hi folks, I have a question about using the Supabase plug to join together two tables that aren’t foreign keyed. I have

  1. A user table, with a UUID field
  2. A tags table, where there is a record_id field that a) contains the user.id and b) isn’t fkeyed to the user table (because it’s used across multiple tables)

From my playing around, I can’t get the advanced query builder in the SB plugin to link these two tables, because there’s no connection that WeWeb understands. But there is a unique ID that will link the two tables via a traditional SQL query.

Is there a way round this? I’ve used SB views but found them to be very slow when loading compared to using the SB plugin.

Hope I’ve explained this well enough, let me know if you need more detail!

2

Use supabase functions, as it sounds like pretty simple sql query, and ai probably can code it for you pretty easily
And also there is this supabase video for multi keys

1 Like
3

What?? :smiley: Are you aware you can use foreign keys on as many tables as you want? Or maybe I’m understanding it incorrectly?

4

As in, there’s two fields on the “tags” table:

  • Field 1: table_name
  • Field 2: record_id

It’s designed so that when we’re linking a tag, it can be linked to any table we want it to. So in the second field, you could have IDs of records from a bunch of different tables.

5

Thank you! Will investigate.

6

For anyone reading this - I tried making a view again (using Supabase’s AI SQL generator) and asked it to make it as performant as possible. And it works wayyyyy better than than the mega query from WeWeb. So I think there’s just a point where if you have too many joins / too much data the simplest thing is to do a view, and beyond that it’s an edge function. Still learning!

1 Like