javascriptsqlnode.jspostgresqlnode-pg-pool

How to increment postgres table column after selecting the current value qith Node pg?


I am creating an app that has a user, question, answer, comment, and voting tables. Not sure if it's good decision, but I've decided to make the voting table into a join table containing IDs of all the other tables instead of having every other table have a vote_count column because votes would belong-to every other table.

The voting table look like this-

    CREATE TABLE vote (
     "questionVoteCount"       SERIAL,
     "answerVoteCount"         SERIAL,
     "commentVoteCount"        SERIAL,
     "accountId"               INTEGER REFERENCES account(id),
     "questionId"              INTEGER REFERENCES question(id),
     "answerId"                INTEGER REFERENCES answer(id),
     "commentId"               INTEGER REFERENCES comment(id),
     PRIMARY KEY ("questionVoteCount", "answerVoteCount", 
     "commentVoteCount")
     );

My model look like this-

    class Vote {
constructor({
    questionVoteCount,
    answerVoteCount,
    commentVoteCount,
    accountId,
    questionId,
    answerId,
    commentId
} = {}) {
    this.questionVoteCount =
        this.questionVoteCount || VOTE_DEFAULTS.questionVoteCount
    this.answerVoteCount = this.answerVoteCount || VOTE_DEFAULTS.answerVoteCount
    this.commentVoteCount =
        this.commentVoteCount || VOTE_DEFAULTS.commentVoteCount
    this.accountId = accountId || VOTE_DEFAULTS.accountId
    this.questionId = questionId || VOTE_DEFAULTS.questionId
    this.answerId = answerId || VOTE_DEFAULTS.answerId
    this.commentId = commentId || VOTE_DEFAULTS.commentId
}

static upVoteQuestion({ accountId, questionId }) {
    return new Promise((resolve, reject) => {
        pool.query(
            `UPDATE vote SET "questionVoteCount" = 
                               "questionVoteCount" + 1 WHERE 
                               "questionId" = $1 AND "accountId" = 
                               $2`,
            [questionId, accountId],
            (err, res) => {
                if (err) return reject(err)
                resolve()
            }
        )
    })
}

I wanted each question/answer/comment to have a vote count, and a user posting on the voting route would increment or decrement the votes of any of the above. How can I go about doing that? I have a feeling that I've made some error with the voting table itself. Should I have stuck with my original idea of having a vote_count column in each table?


Solution

  • UPDATED TABLE- thanks to alfasin

    CREATE TABLE vote (
     "questionVoteCount"       INTEGER DEFAULT 0 NOT NULL,
     "answerVoteCount"         INTEGER DEFAULT 0 NOT NULL,
     "commentVoteCount"        INTEGER DEFAULT 0 NOT NULL,
     "accountId"               INTEGER REFERENCES account(id),
     "questionId"              INTEGER REFERENCES question(id),
     "answerId"                INTEGER REFERENCES answer(id),
     "commentId"               INTEGER REFERENCES comment(id),
    

    );

    Instead of running an ''UPDATE' statement, 'INSERT'-ing "voteCount" with 1 for upvote and -1 for downvote worked for me.

    Now I can run a 'SELECT SUM("voteCount")' to get all the votes for questions, answers, comments, users and whatever.