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: https://github.com/supabase/supabase/discussions/1791#discussioncomment-891714_

[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.

[jonathanlal]

+1 would be useful

[guerdaa]

+1

[boadude]

+1

[filipecabaco]

Thank you for the feedback. We hope to tackle this as soon as we end the work on https://github.com/supabase/realtime/issues/376

[boadude]

Awesome, thank you so much.

On Thu, Mar 28, 2024 at 8:39 AM Filipe Cabaço @.***> wrote:

Thank you for the feedback. We hope to tackle this as soon as we end the work on supabase/realtime#376 https://github.com/supabase/realtime/issues/376

— Reply to this email directly, view it on GitHub https://github.com/supabase/realtime-js/issues/97#issuecomment-2024978058, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABIEVLX52F2UYQSC3M5AZ4TY2PXPDAVCNFSM5ASFHJ6KU5DIOJSWCZC7NNSXTN2JONZXKZKDN5WW2ZLOOQ5TEMBSGQ4TOOBQGU4A . You are receiving this because you commented.Message ID: @.***>

-- Miguel Angel Meza Salazar 82295147 - (041) 2955726

[w3sami]

awesome! since #376 is done now, can you give some status update and maybe throw us a ballpark estimate of release for this one?

[Michota]

Why is this still not done? :c

[filipecabaco]

there's a lot of extra complexity in the backend to support this as the current limitation is due to the way we're pulling changes out of the database to broadcast them.

this is in the roadmap and we're doing the ground work required to achieve this goal along with other goals

[jonathanlal]

Any update on this? Probably one of the most needed features imo

[filipecabaco]

Not yet unfortunately, we're currently focusing the next steps for Realtime and this is part of what we will take into consideration and we will keep this thread up to date

[jacktsin1]

Filtering for Presence and Broadcast would be extremely helpful too, this is a fundamental functionality for building even a simple app that's missing right now. For example on a messaging app, to use presence, you would have to either:

1. Create a separate channel for each user and then concurrently subscribe to a bunch of channels from a single browser, or
2. Create a channel for all users where each subscribed browser will be getting a tone of irrelevant updates from irrelevant users

*Also, as mentioned on 22484 the "filter" ({ event }) option on Broadcast is filtering result on the browser which might be insecure depending on the use case (for private messages on a messaging app it is a security concern if you create a channel per user as mentioned above, and have filter event per chat) and also increases the number of messages sent/received + bandwidth (billing/usage-related).

[filipecabaco]

@jacktsin1 you can have different callbacks for your presence feature and your broadcast feature. Here's a quick Deno example (run with deno run --allow-net --allow-env --allow-read --allow-ffi --allow-sys anon.ts):

import { createClient } from "npm:@supabase/supabase-js@2.45.4";
const url = "<url>";
const anonKey = "<anon_key>";

const client = createClient(url, anonKey);
const config = { broadcast: { self: true } };
const channel = client.channel("public", { config });
channel
  .on("broadcast", { event: "test" }, console.log)
  .on("presence", { event: "join" }, console.log)
  .on("presence", { event: "sync" }, console.log)
  .subscribe((status: string, err: any) => {
    if (status === "SUBSCRIBED") {
      console.log("connected");
      channel.track();
      setInterval(() => {
        channel.send({
          type: "broadcast",
          event: "test",
          payload: { message: Date.now() },
        });
      }, 2000);
    } else {
      console.error({ status, err });
    }
  });

Here's the output

[jacktsin1]

@filipecabaco thanks for your reply!

Having different callbacks for the same channel doesn't address the challenge I'm talking about since in a real-case scenario where users are related to other users, you want to be able to get specific data on a user's browser.

With a channel named "public" while having every user subscribe to that channel as you described in your example, all users will be sending and getting updates/messages from and to all other users, which is at least not scalable and (depending on the use-case) not secure, and not cost-efficient.

Imagine having an app with many thousand concurrently active users. Each of them will end up receiving a tone of messages every few seconds or ms, from every other user that joins the channel or takes other actions that are being tracked, and those would arise:

1) Security-related concerns (users' browsers have access to information that probably shouldn't be there) 2) Performance issues (you would have to manipulate that list/dictionary from the presenceState/broadcast and store it in some local state in the browser every time a new message comes in) 3) Cost-related issues

So in real-world apps, we need to be able to filter the events received on the Realtime server, not on the client, since the cases where relationships between users are random (like https://multiplayer.dev), might be extremely rare.

One solution seemed to be Broadcast, with a separate channel for each user and the event set to something specific as a filter (eg: supabase.channel("userId").on("broadcast", {event: "someChatId"}, ...)), but:

1. That would be a (not very efficient) workaround since a user/browser would have to subscribe to multiple channels at the same time (eg: 20 or even more) to ensure that the sent/incoming data are specific

2. Apparently, filtering based on the {"event"} happens on the browser (22484) so still, a user's browser would receive every piece of information sent to that channel (no matter what the {"event"} filter is — I have tested this). For example, if you create a channel per user (say user0) and change the {"event"} per chat to restrict messages per conversation, still, all users that have chatted with user0 (are subscribed to the channel of user0) will receive every message sent to any chat from user0.

So this is not a solution either, and the current workaround would be to have a channel per user (Presence), and a channel for all of their chats (with Postgres Changes, or a channel for each of their chats with Broadcast) and subscribe to all of them, which is still not efficient for performance (please correct me if subscribing to multiple channels is not bad when it comes to a browser's performance, and thus the above is not a workaround but a solution).

Realtime allows building features with great potential without having to leave Supabase or seek other solutions, I just wanted to provide you with some feedback on the limitations that exist right now, and the challenges a common app might face when it comes to Realtime. If I'm wrong in any part please correct me, and please point out any other solution I missed.

[filipecabaco]

Just to clarify, why would a user prefer to use a global channel with filters vs multiple channels ?

would it be more on the side of "I want to have a global notification channel" ?

[jacktsin1]

Just to clarify, why would a user prefer to use a global channel with filters vs multiple channels ?

would it be more on the side of "I want to have a global notification channel" ?

When it comes to "Postgres Changes" filtering based on multiple conditions is necessary to subscribe/receive the events needed — not more or less. Eg: in a chat app when a user sends a message their message exists locally, there might be no need to receive it back because this doubles the No of messages sent/received, more DB processing, and more bandwidth (resources and cost-related). So filtering based on the chat_id and the user_id is necessary.

When it comes to Presence/Broadcast, subscribing to a channel and getting only the events/data you need by filtering the others with a line of code, compared to creating a channel per user or event and subscribing to it from all relevant clients is more convenient for the developer and would allow for more creativity, but beyond that, I have the impression that subscribing to many different channels at the same time from a single browser (say 20-50 different channels at the same time in addition to any other front-end operation of the app) may cause browser performance issues to the point that the user's browser will start lagging — especially on lower-end devices.

I haven't thoroughly investigated this though, so I would like to ask you to confirm if subscribing to as many channels you want at the same time will cause any performance issues on the user's browser, or if this won't be an issue at all (eg. Would that be an efficient approach: [1, ..., 100].map(id => () => { supabase.channel(id).on("presence", { event: "sync" } ...) }) ).