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, Feb 24 '23 at 01:56

score 40 · Accepted Answer · answered Dec 29 '20 at 03:08

40

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 '20 at 03:08

dshukertjr

15,244
11
57
94

1

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 – Kenny John Jacob Sep 17 '21 at 02:49
1

@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 https://github.com/supabase/supabase/discussions – dshukertjr Sep 17 '21 at 03:57
@KennyJohnJacob That should work as well, it's documented on https://postgrest.org/en/v9.0/api.html#nested-embedding – Steve Chavez Dec 14 '21 at 23:00
Can someone please point to necessary documentation where how there queries are structured and work are detailed? I only see example queries everywhere. Please point to some nice resources. – Akash Gorai Jan 03 '22 at 05:53
@AkashGorai Would the official Supabase documentation be what you were looking for? https://supabase.com/docs/reference/javascript/select – dshukertjr Jan 03 '22 at 07:36
@dshukertjr! what kinds of value will be replaced with supplier and supplier_id? – Cardoso Feb 24 '23 at 01:54
1

@Cardoso `supplier` can be anything you would like. `supplier_id` is the column within the table that you passed to the `from` clause. – dshukertjr Feb 24 '23 at 09:08
The join field comes out as an object. Anyway for it to be just one value? – Amarjeet Singh Rai Mar 01 '23 at 17:41
3

@AmarjeetSinghRai You cannot do it yet, but a feature to spread the nested object in the parent object is coming to Supabase soon. https://supabase.com/blog/postgrest-11-prerelease#spreading-related-tables Until this feature lands, you would have to use Database Functions https://supabase.com/docs/guides/database/functions – dshukertjr Mar 02 '23 at 06:37

How to query using join in Supabase?

1 Answers1

Linked