当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - How to query for both posts and post upvotes in MySQL?

问题描述:

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

FROM elements

INNER JOIN likes

ON elements.id=likes.elementID

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 MySQL)

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 elementID and 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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: