I am attempting to model post upvotes and posts in MySQL. Currently I have an elements table for posts and a likes table for upvotes, structured as follows.
likes (id, elementID, googleID)
elements (id, googleID, title, body, type)
I have a URL route that will either return the most recent posts, or the posts with the most upvotes depending on a parameter. I want to query for a set of posts, each listing how many upvotes they have. The website won't display who upvoted, but the database should keep track of this to prevent multiple upvotes.
I tried to do something such as:
SELECT elements.id, elements.googleID, elements.title, likes.id, likes.elementID
INNER JOIN likes
This did not work well.
How would I get a set of posts, each showing how many upvotes they have when the upvotes are stored in a separate table?
I want to query for a set of posts, each listing how many upvotes they have.
You can try this query. I used
LEFT JOIN so you can still get the posts without likes.
SELECT E.id , E.googleID , E.title , L.likeCount FROM elements E LEFT JOIN ( SELECT elementId , COUNT(id) AS likeCount FROM likes GROUP BY elementId ) L ON L.elementId = E.id
Or (not pretty sure if this will run properly in
SELECT E.id , E.googleID , E.title , COUNT(L.id) FROM elements E LEFT JOIN likes L ON L.elementID = E.id GROUP BY E.id
The website won't display who upvoted, but the database should keep track of this to prevent multiple upvotes
You can create a
UNIQUE CONSTRAINT in your
LIKES table, for
googleID columns. This will make sure that a
googleID can only like one
elementID. Otherwise, it will throw a unique constraint violation.
With that, when a user is upvoting a post, you can check in the database first if the user has already an existing record in the
LIKES table before inserting one.