Flutter Supabase join queries

Question

I have three tables in my social media app: users (stores user details), connections, and posts.

The connections table has the following columns:

• id (Primary Key)
• uuid (Foreign Key from users table)
• circle (List of UUIDs)
• following (List of UUIDs)
• followers (List of UUIDs)

The posts table has the following columns:

• id (Primary Key)
• uuid (Foreign Key from users table)
• created_at (Timestamp)
• is_public (Boolean)
• content

I need assistance with the following scenarios:

1. Scenario: Retrieving posts from users I'm following

   • For example, let's say I am user 'A', and I want to retrieve all the posts from the users I am following.

2. Scenario: Handling private posts visible to specific users

   • Suppose I am not included in the circle list of user 'B'. User 'B' has two posts, one is private, and the other is public.
   • As per our app's rules, only users in the circle should be able to view private posts.
   • How can I implement a solution to ensure that only users in the circle can read or view private posts?

How can I achieve the required outcome in Flutter Supabase?. Thank you!

Answer 1

Any complex queries like this will require you to write database functions and calling it with the .rpc() method from the Flutter app.

final data = await supabase.rpc('get_following_posts');