I am trying to get all posts from followed users such that the posts have not been liked by the following user (aka me). how can i create a schema and query such a thing in supabase?

My current tables are like this:

User Table {
  id        
  username 
}

Follows table {
  followerId 
  followingId 
}

Post table {
  id
  title 
}

liked_by table {
 user_id,
 post_id,
}

CodePudding user response：

Assuming youru post table also has user_id column where the user_id represents user_id of the user who created the post, you can create a postgres function that looks something like this:

create or replace function not_liked_posts()
returns table(post_id uuid, title text)
as
$func$
    select
        post.id as post_id,
        post.title
    from post
    inner join users on post.user_id = users.id
    where post.user_id in (select followerId from follows where auth.uid() = followingId)
    where post.id not in (select post_id from liked_by where user_id = auth.uid());
$func$
language sql security invoker;

Note that auth.uid() is a function that Supabase provides out of the box to get the currently signed in user's user id.

Once you create the above function, you should be able to call it using the rpc() method of Supabase like this:

const { data, error } = await supabase
  .rpc('not_liked_posts')