Postgres Join tables and create new field if join exists

Question

I'm new to Postgres and I have a confusing one. Basically, I want to find if my post is bookmarked by the user or not in a new field bookmarked = true / false

Here are my tables

CREATE TABLE blog_posts (
      post_id BIGSERIAL PRIMARY KEY NOT NULL,
      title VARCHAR(255) NOT NULL,
      body TEXT NOT NULL,
      pinned BOOLEAN DEFAULT FALSE,
      author VARCHAR(100) REFERENCES users(username) ON DELETE CASCADE NOT NULL,
      author_id BIGINT REFERENCES users(user_id) ON DELETE CASCADE NOT NULL,
      post_banner VARCHAR(255),
      tags VARCHAR[],
      description VARCHAR(255),
      avg_rating FLOAT,
      ratings_count INT DEFAULT 0 NOT NULL,
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    );

CREATE TABLE blog_bookmarks (
        user_id BIGINT REFERENCES users(user_id) ON DELETE CASCADE NOT NULL,
        post_id BIGINT REFERENCES blog_posts(post_id) ON DELETE CASCADE NOT NULL,
        PRIMARY KEY (user_id, post_id)   
      );

And this is my initial query

SELECT blog_posts.*, count(ratings.*) as ratings_count, avg(ratings.rating) as avg_rating, users.avatar as author_avatar,
     users.name as author_name, users.role as author_role, users.location as author_location, users.description as author_description
     FROM blog_posts
      LEFT JOIN ratings ON ratings.post_id = blog_posts.post_id
      LEFT JOIN users ON users.username = blog_posts.author
     WHERE blog_posts.post_id = $1
      GROUP BY blog_posts.post_id, users.avatar, users.name, users.role, users.location, users.description;

There might be a lot of nonsense of extra code but I leave it just in case I don't want to delete something useful.

Whenever I bookmark a post, I create a new record with the user_id and post_id so when I query for posts I want to match them somehow and see if exists and if exists I want a new field bookmarked = true / false

Thank you so much, I appreciate it

Answer 1

If anyone would encounter the same problem here is how I fixed it

SELECT blog_posts.* , count(ratings.*) as ratings_count, avg(ratings.rating) as avg_rating, users.avatar as author_avatar,
    users.name as author_name, users.role as author_role, users.location as author_location, users.description as author_description,
      CASE WHEN blog_bookmarks.user_id IS NOT NULL AND blog_bookmarks.post_id IS NOT NULL
      THEN true ELSE false
      END bookmarked
    FROM blog_posts
    LEFT JOIN ratings ON ratings.post_id = blog_posts.post_id
    LEFT JOIN users ON users.username = blog_posts.author
    LEFT JOIN blog_bookmarks ON blog_bookmarks.user_id = $1 AND blog_bookmarks.post_id = blog_posts.post_id
    WHERE blog_posts.post_id = $2
    GROUP BY blog_posts.post_id, users.avatar, users.name, users.role, users.location, users.description, blog_bookmarks.user_id, blog_bookmarks.post_id;

basically, I joined a few tables but the CASE is where the magic happens. $1 is the user's ID that actually makes the query and $2 is the post_id that has been queried for.