How to query using join in Supabase?

Question

In Supabase documentation, it explains this as how you would "join" tables to get data

const { data, error } = await Supabase
  .from('countries')
  .select(`
    name,
    cities (
      name
    )
  `)

But how do I know this works every time when I have not specified which columns would be joined? Is there a way to specify which column to perform join on?

I have a same problem for pulling data from supabase database. I like to join tables and select values from multiple tables at once. How can I solve this problem? — Cardoso
– Cardoso, Commented Feb 24, 2023 at 1:56

dshukertjr · Accepted Answer · 2020-12-29 03:08:10Z

56

So this code works when there is only one relation(foreign key) between the two table countries and cities

const { data, error } = await Supabase
  .from('countries')
  .select(`
    name,
    cities (
      name
    )
  `)

Or when you want to join multiple tables, you can do this:

const { data, error } = await supabase
  .from('products')
  .select(`
    id,
    supplier:supplier_id ( name ),
    purchaser:purchaser_id ( name )
  `)

answered Dec 29, 2020 at 3:08

dshukertjr

19.2k11 gold badges71 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Kenny John Jacob Over a year ago

I am facing a similar issue but for a join one more level deep. e.g. here we are selecting countries, and then joining to get the cities. Say after this, I wanted to get the cafes in the cities, and assuming the FK are all configured. .select(`name, cities (name, restaurants (name))`) does this work? In my case it just shows up as null

dshukertjr Over a year ago

@KennyJohnJacob I have never seen a Supabase query like that, so I'm not sure, but it might be worth posting to Supabase discussion page github.com/supabase/supabase/discussions

Steve Chavez Over a year ago

@KennyJohnJacob That should work as well, it's documented on postgrest.org/en/v9.0/api.html#nested-embedding

dshukertjr Over a year ago

@Cardoso supplier can be anything you would like. supplier_id is the column within the table that you passed to the from clause.

dshukertjr Over a year ago

@AmarjeetSinghRai You cannot do it yet, but a feature to spread the nested object in the parent object is coming to Supabase soon. supabase.com/blog/… Until this feature lands, you would have to use Database Functions supabase.com/docs/guides/database/functions

|

Collectives™ on Stack Overflow

How to query using join in Supabase?

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related