Working with complex table joins

We’ve learned about joining related tables by using nested select strings (for example, fetching related comments in tickets), but with Supabase, you can do the following:

• Resolve ambiguous joins, to avoid errors, explicitly
• Rename joined tables on the fly
• Create inner joins to only return data where related data exists
• Combine the renaming feature with the inner join feature

Let me show you a few practical examples.

In our tickets table, we have two user references: created_by -> public.service_users and assignee -> public.service_users. I want to retrieve both user objects with a join. We do so by nesting the related table like so:

supabase.from("tickets").select("*, service_users(*)");

This will fail – since there are two pointers pointing to service_users, it will not know which one to resolve.

However, you can also resolve this another way, by declaring two explicit joins...