OR condition on different referencedTable(s)

[rrr3da]

Descripition

Supabase js library allow a user to match at least on filter but I don't find any way to have conditions A and B with A that is a main table and B a referenced table.

In fact, if I set the option "referencedTable", the option is applied to all the conditions described by the .or filter.

It could be very nice if the "referencedTable" option could be an array, or a string, that matches the conditions length, so we can apply the option differently over the single conditions.

Example

Say we have master table as

(id, title, owner)

and joined table (1:N relation) as

(id, master_id, note)

query.or('owner.eq.1,master_id.in.(4,5,6)', {
   referencedTable: "master_table,joined_table"
})

[a6z6]

Hi, I have the same question:

Say I have 2 tables, parents and kids.

parents:
name,
role,
age

kids:
name,
grade,
father_id,  // references parents
mother_id, // references parents

In the doc, it only teaches how to deal with one referenced table:
Say I wanna fetch all kids' name: who is in Grade 1 OR whose father is older than 30.

// Fetch all kids' name:  who is in Grade 1 OR whose father is older than 30.
.from("kids")
.select('''
name,
father:father_id!inner(age),
''')
.or('grade.eq.1,age.gt.30', referencedTable: 'father_id')

But what I wanna fetch is: all kids' name whose father is older than 30 OR mother is younger than 40.

// Fetch all kids' name whose father is older than 30 OR mother is younger than 40.
.from("kids")
.select('''
name,
father:father_id!inner(age),
mother:mother_id!inner(age),
''')
.or('age.gt.30,age.lt.40', referencedTable: 'father_id, mother_id') // does not work

Neither PostgREST API doc nor Supabase Flutter doc gave me further hints.

At the time I was submitting a new discussion, it reminded me to search similar ones.

I found your discussion, and another answered one

.or() only works with one table. You will need to do an rpc call or view.

It is hard to accept the reality that .or() supports only one referencedTable, but since it is answered by maintainer, I think it is the final answer.

I am really tired after hours of googling and trying. Next time I will come to this discussion area and search. (Seems Google did not index everything here)

[rrr3da]

Thank you @a6z6