Supabase, filter by column value of foreign key row

Question

I am trying to figure out how to implement a query in supabase:

Schema

CREATE TABLE cars 
(
    id SERIAL PRIMARY KEY,
    brand TEXT
);

CREATE TABLE stores 
(
    id SERIAL PRIMARY KEY,
    car INT REFERENCES car(id),
    name TEXT
);

I want to get all stores which carry the car of brand "x"

In Supabase I can filter like this:

let { data: stores } = await supabase
  .from('stores')
  .select("*")
  .eq('name', 'Ford car shop')


// Returns
{
  id: 123456,
  car:"Ford",
  name:"Ford car shop"
}

or join like this:

let { data: stores } = await supabase
  .from('stores')
  .select(`
    *,
    cars (
      brand
    )
  `)
  .eq('name', 'Ford car shop')

// Returns
{
  id: 123456,
  car:"Ford",
  cars: {
     id: 654321,
     brand: "Ford"
  }
  name:"Ford car shop"
}

But how can I filter stores by the brand of the car they carry using the supabase sdk?

Answer 1

2022: This is now possible with supabase client. You can use the !inner() function.

let { data: stores } = await supabase
  .from('stores')
  .select('*, cars!inner(*)')
  .eq('cars.brand', 'Ford')

Doc here: Filtering with inner joins

Answer 2

You can use the built in postgrest api supabase gives you to get this info. e.g:

/projects?select=*,clients!inner(*)&clients.id=eq.12

this isn't added to the supabase client yet.

the patch is shown here: https://github.com/PostgREST/postgrest/releases/tag/v9.0.0

Answer 3

Edit: Supabase recently added this feature. The new accepted answer is below.

I did some more research and found out that this is currently not possible. However, it seems like it has been implemented and will make it into the next Supabase release.

An interim solution is using views and then querying those.

CREATE VIEW stores_expanded AS
SELECT
  stores.id,
  stores.name,
  cars.brand
FROM
  stores
  LEFT JOIN cars
ON
  stores.car = cars.id;

let { data: stores } = await supabase
  .from('stores')
  .select("*")
  .eq('brand', 'x')