Support multiple columns filter (AND) in realtime channel filter

[panmona]

Feature request

Is your feature request related to a problem? Please describe.

I want to only receive certain events from realtime. The events I want to receive are identified by two different columns that both need to have a specific value.

Describe the solution you'd like

I want to be able to add multiple column filters in the supplied topic to client.channel.
Something like this: realtime:{schema}:{table}:{col}=eq.{val}:{col}=eq.{val}

Additional context

My request is similar to the one requested in this discussion: supabase/supabase#1791 (comment)_

[w3b6x9]

@panmau thanks for the feature request!

It's not practical to do this on the Realtime sever at the moment(see comment) but perhaps we can do the matching multiple column filtering client-side. I'll leave this open in case anyone from the community has any suggestions. This is something we want to implement eventually after we improve the Realtime server itself.

[w3b6x9]

@panmau this is actually possible now given realtime-js/supabase-js v2. You can take a look at the bottom of https://supabase.com/docs/guides/realtime/postgres-changes where you can pass in multiple filters per channel.

[psteinroe]

@w3b6x9 either the link it outdated, or either of us misunderstands the requirement. I think this feature request want the following to work: filters: "col_a.eq.value,col_b.eq.other_value". That isn't possible, right?

[point-source]

@w3b6x9 either the link it outdated, or either of us misunderstands the requirement. I think this feature request want the following to work: filters: "col_a.eq.value,col_b.eq.other_value". That isn't possible, right?

That is what I have been trying to do as well and so far, have not found a way to do so.

[akeva001]

I would love this feature as well!

[mfissehaye]

Any updates on this issue? I don't think it's fully realtime without this feature?

[psteinroe]

for us, the main reason for this feature at this point is performance: the realtime query takes by far the most time, because we cannot apply the filters required to improve the performance given the rls policy.

[williamlmao]

+1 on this

We need a way to listen to a table but need to filter on two columns. If we aren't able to filter on the second column we get a ton of unnecessary events we have to filter on the client side.

[kukadiyaAni]

+1 on this

We also need to filter on two-column, please let me know how we can add two columns with or condition

[hipdev]

A temporary solution is to have a field that groups the identifiers you need 😿

[alfredkakuli]

I will suggest a solution that solves the issue and does not affect the performance as this is the fear from the supabase team.
Assume I have table orders that has order_number and customer_id columns as the needed filters. create a third column in the same sable whose value is the combination of the filtering columns and use this column for your filtering
example:

const handleInserts = (payload) => {
  console.log('Change received!', payload)
}

const customer_id=252525
const order_number=ord105245

const filter =customer_id+ord105245 
supabase.channel('channel_name').on('postgres_changes', {
    event: 'UPDATE',
    schema: 'public',
    table: 'orders',
    filter: filter
  }, handleInserts)
  .subscribe()

[cohlar]

+1 on this.

@w3b6x9 either the link it outdated, or either of us misunderstands the requirement. I think this feature request want the following to work: filters: "col_a.eq.value,col_b.eq.other_value". That isn't possible, right?

I would suggest one of those syntaxes - to be consistent with PostgREST:
filter: 'col_a=eq.value&col_b=eq.other_value' or filter: 'and(col_a=eq.value,col_b=eq.other_value)'

In addition to supporting multiple columns, it would also be nice to support other PostgREST operators. For example I would like to filter a column by not.is.null.