Supabase•14mo ago•

2 replies

Filter original records, given a joined table

Given such a query:

const partnerId = "MY_PARTNER_ID_1"

const query = supabase
      .from("past_bookings")
      .select(
        `
        *,
        past_booked_services (*)
      `
      )
      .eq('past_booked_services.partner_id', partnerId)

const partnerId = "MY_PARTNER_ID_1"

const query = supabase
      .from("past_bookings")
      .select(
        `
        *,
        past_booked_services (*)
      `
      )
      .eq('past_booked_services.partner_id', partnerId)

The result gives us all the records from

past_bookings

past_bookings

, even if there are some

past_booked_services

past_booked_services

records whose

partner_id

partner_id

is not the same as the value of the

partnerId

partnerId

variable in my code.

I want the results of

past_bookings

past_bookings

to be only those records who had at least one

past_booked_services

past_booked_services

joined record whose

partner_id

partner_id

matches the value of

partnerId

partnerId

.

Please also consider the following factors:

- Pagination is also applied to this query using

.range()

.range()

as this is only a sample piece of code which is part of a more complex function so filtering from the application level is out of the question and needs to be applied from the database level.
- The relationship between

past_bookings

past_bookings

and

past_booked_services

past_booked_services

is a one-to-many relationship, therefore I cannot query the

past_booked_services

past_booked_services

table first and then join it with

past_bookings

past_bookings

because this will result in many duplicate records of

past_bookings

past_bookings

Filter original records, given a joined table

Given such a query:

const partnerId = "MY_PARTNER_ID_1"

const query = supabase
      .from("past_bookings")
      .select(
        `
        *,
        past_booked_services (*)
      `
      )
      .eq('past_booked_services.partner_id', partnerId)

const partnerId = "MY_PARTNER_ID_1"

const query = supabase
      .from("past_bookings")
      .select(
        `
        *,
        past_booked_services (*)
      `
      )
      .eq('past_booked_services.partner_id', partnerId)

The result gives us all the records from

past_bookings

past_bookings

, even if there are some

past_booked_services

past_booked_services

records whose

partner_id

partner_id

is not the same as the value of the

partnerId

partnerId

variable in my code.

I want the results of

past_bookings

past_bookings

to be only those records who had at least one

past_booked_services

past_booked_services

joined record whose

partner_id

partner_id

matches the value of

partnerId

partnerId

.

Please also consider the following factors:

- Pagination is also applied to this query using

.range()

.range()

past_bookings

past_bookings

and

past_booked_services

past_booked_services

is a one-to-many relationship, therefore I cannot query the

past_booked_services

past_booked_services

table first and then join it with

past_bookings

past_bookings

because this will result in many duplicate records of

past_bookings

past_bookings

Filter original records, given a joined table

Similar Threads

Filter original records, given a joined table

Similar Threads

Similar Threads

Similar Threads