How to query likes and user details for posts?

Question

I'm pretty new to the world of databases and SQL.

I'm trying to build a system for users to submit posts and like posts. I've been looking at a few similar questions on this site, reading about third normal form, etc. and I think I have a pretty solid DB schema at this point:

CREATE TABLE IF NOT EXISTS USERPROFILES (
  ID INT AUTO_INCREMENT PRIMARY KEY,
  USERNAME VARCHAR(64) NOT NULL,
  EMAIL_ADDRESS VARCHAR(255) NOT NULL,
  COUNTRY_CODE CHAR(2) NOT NULL,
  CREATED_DATE DATETIME(9) NOT NULL
)
ALTER TABLE USERPROFILES ADD CONSTRAINT USERPROFILES_USERNAME_UNIQUE UNIQUE (USERNAME)
ALTER TABLE USERPROFILES ADD CONSTRAINT USERPROFILES_EMAIL_ADDRESS_UNIQUE UNIQUE (EMAIL_ADDRESS)

CREATE TABLE IF NOT EXISTS USERPOSTS (
  ID INT AUTO_INCREMENT PRIMARY KEY,
  THREAD_ID INT NOT NULL,
  PARENT_POST_ID INT NULL,
  PROFILE_ID INT NOT NULL,
  WRITTEN_TEXT VARCHAR(500) NOT NULL,
  MEDIA_LOCATION VARCHAR(255) NULL,
  CREATED_DATE DATETIME(9) NOT NULL,
  CONSTRAINT FK_USERPOSTS_PROFILE_ID__ID FOREIGN KEY (PROFILE_ID) REFERENCES USERPROFILES(ID) ON DELETE RESTRICT ON UPDATE RESTRICT
)

CREATE TABLE IF NOT EXISTS USERVOTES (
  POST_ID INT,
  PROFILE_ID INT,
  CREATED_DATE DATETIME(9) NOT NULL,
  CONSTRAINT pk_UserVotes PRIMARY KEY (POST_ID, PROFILE_ID),
  CONSTRAINT FK_USERVOTES_POST_ID__ID FOREIGN KEY (POST_ID) REFERENCES USERPOSTS(ID) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT FK_USERVOTES_PROFILE_ID__ID FOREIGN KEY (PROFILE_ID) REFERENCES USERPROFILES(ID) ON DELETE RESTRICT ON UPDATE RESTRICT
)

Now what I want to do is perform a single query for my application to fetch all USERPOSTS with a given THREAD_ID (on behalf of a requesting user), but also join with the USERVOTES and USERPROFILES tables such that I can get a list of posts combined with the following data:

• total like count for the post
• if the requesting user liked the post or not
• the username of the submitter of the post (not just their userprofile ID)

With this I can render everything I need in the application.

Can anyone help construst a single query like this given my table schema? I'm assuming I will need at least two INNER JOINs (one on USERVOTES and one on USERPROFILE) as well as a COUNT on the USERVOTES (but also, in addition, how do I query USERVOTES with a WHERE for a specific requesting USERID)?

Thanks!

Answer 1

Something like this should do the trick (note the aliases, and feel free to adjust the casing to your preferred format):

SELECT 
    Posts.ID, 
    SUM(CASE WHEN Likes.ID IS NULL THEN 0 ELSE 1 END) AS TotalLikesCount,
    SubmitterProfiles.USERNAME,
    MAX(CASE WHEN Likes.PROFILE_ID = 98765 THEN 1 ELSE 0 END) AS DidRequestingUserLikeThisPost
FROM USERPOSTS AS Posts
INNER JOIN USERPROFILES AS SubmitterProfiles -- Inner Join should since a Post must be submitted by a User
    Posts.PROFILE_ID = SubmitterProfiles.ID
LEFT JOIN USERVOTES AS Likes -- Outer (Left) Join because not all Posts necessarily have Likes
    ON Posts.ID = Likes.POST_ID
LEFT JOIN USERPROFILES AS VoterProfiles -- Need to continue with an Outer (Left) Join here because we're pulling the profile of the User who liked the Post (if there is a like)
    ON Likes.PROFILE_ID = VoterProfiles.ID
WHERE Posts.THREAD_ID = 12345
GROUP BY Posts.ID, SubmitterProfiles.USERNAME

Please read the comments for more information, but basically this starts with your USERPOSTS table and inner joins your USERPROFILES table (which should always have a many-to-one relationship), then outer joins the remaining information (because some posts may not have likes).

Since the relationship between USERPOSTS and USERVOTES is one-to-many (when there are likes) the results set becomes a single row per like, so we need to group up by the ID of the post so we can count how many likes (rows) there are which I'm doing using the SUM() function with a CASE statement to handle NULL cases (when there's no likes for a given post).

We also have to group by the USERPROFILES.USERNAME so that we can SELECT it and this doesn't change the cardinality of our results, since every post can only be submitted by a single user.