hi

First msg in #869405720934744086

Second

is it a competition or what lol

Hey, I'm new to postgres and am struggling on a rls policy. I've built a chat app... my tables are: profiles (contains profile data of user), chatrooms (contains the chatroom details), participants (joins profiles with chatrooms), messages (contains the chat messages, joins the participant with the chatroom). I want to secure profiles so a user can only view the profiles of participants they share a chatroom with. I've got the policies set for securing chatrooms, participants and messages. For whatever reason (which maybe I'm just due for a nap) I am stumped on the policy for profiles. Does anyone want to take a few minutes to help me out?

This sounds pretty simple. You just want to check if the profile exists in any of the chatrooms the current user is 'in'.

Pseudo SQL: profile_id IN (select profile_id from chatrooms where current_user_id exists in the chatroom)

Basically you're saying "return TRUE if the current user's id is in the list of all the ids of all the chatrooms this user belongs to"

Hey, if I have a table called 'profiles' like so:

create table if not exists public.profiles (
  id uuid references auth.users primary key,
  project text
);

When doing a query via the supabase JS client, how do you join on some of the data from the auth.users table?

Querying from the auth schema

Hey, can someone share using supabase for sql(postgres) access alone makes sense or worth it?

python or js?

js

ah I Hope someone else will help you 👍

Can you clarify your question please? it's not very clear what you are asking for help with

I have two options currently Hasura + Postgres or Supabase db. Would love to know some insights from the experts here.

Use the one that fits your use case best, no expert can decide this for you as they aren't the ones working on your project.

Ok I understand. Im just looking for a high level opinion to see what is more manageable and scalable.

I've got a performance related question. I've got a fairly large object of data I'd like to store in each row. Previously I extracted out the data into their own columns, but since I can query

jsonb

, I technically don't need to do this anymore. Question is; are queries slower / costly when using jsonb vs querying a single value in a column?

Query performance for JSONB

thank you so much for responding. I really appreciate it! however I don't think it's quite right. I don't have profile_id on the chatrooms table, that's on the participants table which has the chatroom_id and profile_id. I was thinking something like this but again, I'm a noob at trying to write SQL...

(
  select
    profile_id
  from
    participants
  where
    chatroom_id = (
      select
        chatroom_id
      from
        participants
      where
        profile_id = uid()
    )

I think I'm on the right track? 😬

hey guys, is it possible to run sql thought supabase's cli? I wish to let the sql migration files on a git repository, and then execute it though cli if possible

That should be possible as long as you have the correct info in your CLI secrets

I'm curious if there's a solution for a query filter I have in mind. I have an articles data base and each article can have up to five tags. I'd like users to be able to search articles by tags, and order the results by the number of matching tags. I have done this in mongo by adding a match count in the application code after doing a query for posts using the tags array. As a note, I'll be storing the tags in an array on the post schema. So essentially, I need a way to query posts by tags. I'm not sure how to do this in SQL.

Oh, some more detail:

select * from mytable where pub_types @> '{"Journal", "Book"}';

That appears to be an example that would handle the query, but how would you then order by the number of matches?

I suppose if there's not a sql query solution I can do it in the application code.

Quick question, how to update only one column in a row?

Oh sorry, you meant in a row right?

im using the supabase sdk