How can I filter by a value inside a pivot table using Supabase?

Question

I'm building a bike rental app where users can reserve bikes for a certain number of days. Bikes and users have a many-to-many relationship, there's a bike_user pivot table that contains the information about reservation start and end dates. See the diagram:

I need to take a range of dates, and filter the bikes, showing only the ones that haven't been reserved between these two dates.

Can you please help me figure out how to do this?

Answer 1

The documentation is not exactly clear on this one, but you can use the inner keyword to filter your results by values from your joined table like this:

const {data, error} = await supabase.from('bikes')
  .select('*, bike_user!inner(reservation_start_date, reservation_end_date)')
  .gt('bike_user.reservation_start_date', '2022-01-01 15:00:00.000')
  .lt('bike_user.reservation_end_date', '2022-11-01 19:00:00.000')

Let me know if this works for you!