Search code examples
databasepostgresqlpostgrest

How to model PostgREST database to achieve social-network-like behaviour

This question might have a super easy answer, but as I am new to PostgreSQL/PostgREST, I am not sure how to achieve it.

What I want to create is a schema/REST endpoint which allows me to query for posts posted by followed users.

Currently, the schema looks like this:

USERS

- first_name
, last_name
, id

POSTS

- id
, text
, author_id --foreign key to a user

CONNECTIONS

- id
, follower --foreign key to a user
, followed --foreign key to a user

I want to be able to query for the posts posted by users which given user follows, but I am not sure how to achieve this using postgREST.

Solution

  • posts posted by users which given user follows,

    Try it like:

    GET /posts?select=*,users!inner(connections!inner!followed(*))&users.connections.follower=eq.<your_user_id>

    With that you'll get the posts you want though with some extra data.