Multiple foreign tables in a single "or" statement

[luketpena]

Currently with Supabase JS you can filter by nested joined tables by just just digging down, like this:
.eq('table1.table2.table3', value)

You can also filter an or statement by a foreign table like this:
.or('column1.eq.value1, column2.eq.value2, { foreignTable: 'table2' })

But if there are multiple foreign tables we want to filter on in a single "or" statement, this is just not possible. This has led us to created a view for every single table we need to do searching on when the values we are searching are spread across multiple joined tables. So if I am getting something on table 1, but want to search by something from table 2 OR table 3, that just isn't possible right now, which feels like a huge oversight.

My suggestion is to either allow the same drill-down syntax from how it can be used in other filter statements (like eq) or to allow us to provide an array of filter statements to an "or" statement where we can specify a different table for each.

My current use case is that I am repeatedly asked by designers to display a bunch of joined data in a table and then have some kind of search bar at the top that is searching by values across multiple joined tables.

[steve-chavez]

It is possible to use or across tables, but you have to use the is.null or not.is.null filters. See:

• https://supabase.com/blog/postgrest-11-prerelease#anti-joins
• At the end there's an example with or https://postgrest.org/en/stable/references/api/resource_embedding.html#null-filtering-on-embedded-resources

[rasikhq]

This is still an issue, found multiple threads on this from multiple people and all of them are just left unanswered because just like OP mentioned, there is no working solution for it besides edge-function/views hell.

@steve-chavez I looked at the links you've posted and couldn't get anything to work still.

[otang]

We have a strong need for this as well. My preference for how this would work would be to add support for the table names as prefixes in our filters, similar to how it works with the PostgREST docs. Example:

query.or('profiles.first_name.eq.chris,teams.name.eq.Team1')

@luketpena did you find any other solution?

@steve-chavez is there any way for us to pull the exit hatch and drop down to a more "raw" level way to query PostgREST through the Supabase client to allow us to create more complex filters across embedded resources?