support embedded or filter

[bmartel]

Embedded filters

Postgrest supports embedded filters through the following syntax https://postgrest.org/en/v7.0.0/api.html#embedded-filters

Use case

Filtering a sub selection in postgres with or/and for the foreignTable.

Required workaround query param format based on an extended version of the supabase todos example:

const start = new Date().toISOString();
const end = new Date().toISOString();

const workaroundParam = `todos.or=(completed_at.is.null,and(completed_at.gte.${start},completed_at.lt.${end}))`

await client
.from(`projects?${workaroundParam}`)
.select('id,todos(id,completed_at)')
.eq('id', 1)
.order('inserted_at', { foreignTable: "todos", ascending: false });

Solution

Update the or filter to support foreignTable handling to achieve the desired output of postgrest which doesn't require hacking it into the from statement.

const start = new Date().toISOString();
const end = new Date().toISOString();

await client
.from(`projects`)
.select('id,todos(id,completed_at)')
.eq('id', 1)
.or(`completed_at.is.null,and(completed_at.gte.${start},completed_at.lt.${end})`, { foreignTable: 'todos' })
.order('inserted_at', { foreignTable: "todos", ascending: false });

[kiwicopple]

Nice one @bmartel thanks for the PR.

Will just need to check with @soedirgo - I thought this was already possible with our current embedded filters implementation. eg:

const { data, error } = await supabase
  .from('projects')
  .select('id,todos(id,completed_at)')
  .or(`todos.completed_at.is.null, completed_at.gte.${start}`)
  .or(`todos.completed_at.is.null, completed_at.lt.${end}`)

[soedirgo]

I see, single column filters like eq, gte, etc. worked fine but not for or & and, i.e. you can't do:

.../users?select=*,messages(*)?or=(messages.channel_id.eq.1)

Which gives:

{
  "details": "unexpected \"c\" expecting \"not\" or operator (eq, gt, ...)",
  "message": "\"failed to parse logic tree ((messages.channel_id.eq.1))\" (line 1, column 13)"
}

Rather you have to do:

.../users?select=*,messages(*)?messages.or=(channel_id.eq.1)

Thanks for the PR @bmartel!

[github-actions]

🎉 This PR is included in version 0.25.0 🎉

The release is available on:

• npm package (@latest dist-tag)
• GitHub release

Your semantic-release bot 📦🚀

[kiwicopple]

Thanks @bmartel !

@soedirgo perhaps you can bump the version in supabase-js (and add an example in the docs)?