How should I subscribe to a query with embedded resources?

[jameshfisher]

Thanks for this library!

The docs don't describe embedded resources / joins, or have any examples of this. I need to implement a realtime subscription to a query that includes an embedded resource. I see that useSubscriptionQuery works, but it's unclear to me whether this is the recommended way to implement this.

To Reproduce

1. Create a hook like this:

import {
  useQuery,
  useSubscription,
} from "@supabase-cache-helpers/postgrest-swr";
import { useSupabase } from "./supabase-provider";

export function useRealtimeEmployees() {
  const { supabase } = useSupabase();
  useSubscription(
    supabase,
    `some-channel-name`,
    { event: "*", table: "employee", schema: "public" },
    ["id"]
  );
  const { data: realtimeEmployees } = useQuery(
    supabase.from("employee").select("id, name, company (name)").throwOnError()
  );
  return realtimeEmployees;
}

2. Insert a row in the employee table.

Expected behavior

The realtimeEmployees is updated to include the inserted row.

Actual behavior

The client receives a WebSocket message from Supabase, but the realtimeEmployees is not updated.

Additional context

The cause is that the query has a join ("embedded resource"?) with another table company. So when the new row is received, the client doesn't have information about the corresponding row in company. If the query is changed to just .select("id, name"), inserts work as expected.

So in a sense, this is not a bug, and the code couldn't possibly work. What I'm looking for is advice on the best way to implement a subscription that includes embedded resources.

One option that seems to work is useSubscriptionQuery. My main confusion is that the documentation says "The main use case for this hook is Computed Columns", and doesn't mention joins at all - implying there is some better way to handle joins.

[jameshfisher]

Another possible approach: don't use embedded resources / joins, and instead do the join client-side, like:

• Every employee row corresponds to a React element
• Each element has a realtime subscription to the company of its employee

But this follows the bad "N+1 queries" pattern that I think would result in pretty bad performance.

[jameshfisher]

Ok, I've discovered a reason that useSubscriptionQuery doesn't work for this: delete events don't seem to re-trigger the query. I see that the client receives the delete event, but it does not re-run the query, and the realtimeEmployees is never updated.

Strictly, I'm not sure it would need to re-run the query against the Supabase API. A delete event gives supabase-cache-helpers everything it needs to just drop the row from the query result. But it doesn't do that.

[psteinroe]

hey, thanks for opening the issue.

One option that seems to work is useSubscriptionQuery. My main confusion is that the documentation says "The main use case for this hook is Computed Columns", and doesn't mention joins at all - implying there is some better way to handle joins.

actually - its also the preferred way to handle joins! would you mind adding this to the docs?

Ok, I've discovered a reason that useSubscriptionQuery doesn't work for this: delete events don't seem to re-trigger the query. I see that the client receives the delete event, but it does not re-run the query, and the realtimeEmployees is never updated.

can you share your code for this? this sounds like a bug to me, because DELETE events should indeed update related queries.