https://supabase.com/ logo
#javascript
Title
# javascript
s

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
g

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

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
so I still need the member data of the other person related to the chat
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;
}
g

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.