Is there a way to implement count in left join

Question

I'm setting up a chat-Group application using a simple database. I want to know if there is a way for me to see the people that liked/disliked a certain post, which can have a picture or a video attached to it. The important information I want to know is the post ID, message of the post, date of the post, url of the picture I want to post, url of the video, and the id of the user posting said post. I'm using left joins to see all this information, but I'm missing if the posts have likes or dislikes.

I already tried using the aggregate count to see each like and dislike of the posts, but I don't know how to mix this query with the previous information.

This is my query to see the information listed above:

select P.post_ID, P.post_msg, P.post_date, F.photo_url,V.video_url, P.user_ID
from ((Post as P left join Photo as F on P.post_ID = F.post_ID ) left join Video as V 
on P.post_ID = V.post_ID)
where P.chat_ID = 1

Here is the output of the Query:

Here is an example of the output I want:

The query above displays everything I'm looking for except each like and dislike of each post. Is there anyway to see this information mixed with what I want?

Here is the structure of the DataBase:

create table Login(login_ID serial primary key, login_Date DATE NOT NULL DEFAULT CURRENT_DATE, user_name varchar(20) NOT NULL);

create table Users(user_ID serial primary key, user_name varchar(20) NOT NULL, user_password varchar(20) NOT NULL);

create table Contact_List(contactlist_ID serial primary key, user_name varchar(20) NOT NULL,user_ID integer references Users(user_ID) on delete cascade);

create table Person(person_ID serial primary key, person_name varchar(20) NOT NULL, person_lastname varchar(30) NOT NULL, person_phone varchar(12), person_email varchar(50) NOT NULL, user_ID INTEGER references Users(user_ID) on delete cascade);

create table Admin(admin_ID serial primary key, user_ID INTEGER references Users(user_ID) on delete cascade);

create table Chat_Group(chat_ID serial primary key, chat_name varchar(50) NOT NULL, admin_Id INTEGER references Admin(admin_ID) on delete cascade);

create table Group_List(user_ID INTEGER references Users(user_ID) on delete cascade, chat_ID integer references Chatgroup(chat_id) on delete cascade, primary key(user_id, chat_id));

create table Post(post_ID serial primary key, post_msg varchar(280), post_date DATE NOT NULL DEFAULT CURRENT_DATE, user_ID INTEGER references Users(user_ID) on delete cascade, chat_ID INTEGER references Chatgroup(chat_ID) on delete cascade);

create table Video(video_ID serial primary key, video_url varchar(280) NOT NULL, post_ID INTEGER references Post(post_ID) on delete cascade);

create table Photo(photo_ID serial primary key, photo_url varchar(280) NOT NULL, post_ID INTEGER references Post(post_ID) on delete cascade);

create table Reply(reply_ID serial primary key, reply_msg varchar(280) NOT NULL, reply_Date DATE NOT NULL DEFAULT CURRENT_DATE, post_ID INTEGER references Post(post_ID) on delete cascade, user_ID INTEGER references Users(user_ID) on delete cascade);

create table HashTag(hashtag_ID serial primary key, hashtag_msg varchar(200) NOT NULL,post_ID INTEGER references Post(post_ID) on delete cascade NOT NULL, user_ID INTEGER references Users(user_ID) on delete cascade);

create table Reaction(reaction_ID serial primary key,reaction_date DATE NOT NULL DEFAULT CURRENT_DATE, reaction_like BOOLEAN, reaction_dislike BOOLEAN, post_ID INTEGER references Post(post_ID) on delete cascade, user_ID INTEGER references users(user_ID) on delete cascade);

create table React_to(reaction_ID INTEGER references Reaction(reaction_ID) on delete cascade, reply_ID INTEGER references Reply(reply_ID) on delete cascade, primary key(reaction_ID,reply_ID));

Excuse me for my bad english, english is not my main language.

Answer 1

One way of doing this would use correlated subquery.

SELECT p.post_id,
       p.post_msg,
       p.post_date,
       f.photo_url,
       v.video_url,
       p.user_id,
       (SELECT count(*)
               FROM reaction r
               WHERE r.post_id = p.post_id
                     AND r.reaction_like) likes,
       (SELECT count(*)
               FROM reaction r
               WHERE r.post_id = p.post_id
                     AND r.reaction_dislike) dislikes
       FROM post p
            LEFT JOIN photo f
                      ON p.post_id = f.post_id
            LEFT JOIN video v 
                      ON p.post_id = v.post_id
       WHERE p.chat_id = 1;

However I wonder if a reaction can be both a like and a dislike at once. At least your data model allowes such a thing...