Looking for some help writing supabase db subscribe queries Supabase #javascript

Looking for some help writing supabase db/subscrib...

Scott Prins

04/21/2022, 6:33 PM

Looking for some help writing supabase db/subscribe queries needed for a messaging feature. The auth users are from a firebase DB, so I have a reference table in supabase for users holding just the ID, and first/last name. I need to be able to query conversations for a specific user, messages in a specific conversation referencing the current user, and to post messages to the messages column, which the last one should be simple. For Context I have 4 tables:

Copy code

conversations{
  id,
  created_at
}

conversation_members {
  id,
  user_id (FK: user.id)
  conversation_id (FK: conversation.id)
  joined_time,
  left_time,
  created_at
}

messages {
  id
  conversation_id (FK:conversation.id)
  from_id (FK: user.id)
  body,
  timestamp
}

users {
  id
  first_name
  last_name
}

I have tried this with Firebase with just a "Conversations" collection and a nested array of messages, but it was inefficient as hell and not scalable at all, so Relational database was my next option here, but I am having trouble writing the queries to make it happen effectively

garyaustin

04/22/2022, 12:24 AM

You requests seem pretty simple if I'm reading it correctly: This shows some examples on loading data with .select https://supabase.com/docs/reference/javascript/select I believe both of your read cases are covered in that. "Filtering with inner joins" for your conversations by user:

Copy code

const { data, error } = await supabase
  .from('conversations')
  .select('*, conversation_members!inner(*)')
  .eq('conversation_members.user_id', userid)

Just a select with multiple filters for messages from user and conversation:

Copy code

const { data, error } = await supabase
  .from('messages')
  .select('*')
  .eq('conversation_id', convid)
  .eq('from_id',userid)

or something close to those.

Scott Prins

04/22/2022, 2:17 AM

so that first suggestion is in the right direction, but I only get this data in return which shows only the user as a member for the id in which I provide

Scott Prins

04/22/2022, 2:18 AM

so I still need the member data of the other person related to the chat

Scott Prins

04/22/2022, 2:40 AM

this sort of gets me what I want in a users array

Copy code

export function getUserConversationsSB(_userId) {
  const [{ data, error }] = useRealtime(CONVERSATIONS_TABLE, {
    select: {
      columns: '*, conversation_members!inner(*), users!conversation_members(*)',
      filter: useFilter((q) => {
        return q.eq('conversation_members.user_id', _userId);
      }),
    },
  });
  if (error) {
    console.error(error);
  }

  return data;
}

garyaustin

04/22/2022, 2:46 AM

This is the root of the relations that can be handled in PostgREST which Supabase uses. https://postgrest.org/en/stable/api.html#resource-embedding It shows multilevel foreign key relations and gives a hint on format. Unfortunately it is in the URL format which is slightly different that the supabase.js format but should give you ideas.

Previous Next