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

Question

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?

Nir Alfasi · Answer 1 · 2019-04-17T16:49:19.943

1

You declared questionVoteCount as type SERIAL which means auto-increment. Looks like what you want to do is define it as INTEGER.

edited Apr 17 '19 at 16:49

answered Apr 17 '19 at 03:29

Nir Alfasi

53,191
11
86
129

I'm using double quotes to make postgresql use the camel-cased column names. Postgres wouldn't otherwise recognize the names. It's just my style I guess. Anyway that doesn't solve my issue. – Dean Apr 17 '19 at 04:10
@Dean see revised answer. – Nir Alfasi Apr 17 '19 at 16:49
Thank you. I've used INTEGER DEFAULT 0. It's giving me the 'success' message. But the table isn't updating. This is what I have after the 'success' message. quoverflow=# select * from vote; questionVoteCount | answerVoteCount | commentVoteCount | accountId | questionId | answerId | commentId -------------------+-----------------+------------------+-----------+------------+----------+----------- (0 rows) – Dean Apr 17 '19 at 17:02
1

Looks like you have no records in the table - so there's nothing to update. Try to add a log print to the query before you run it (see what arguments you're injecting to the query as accountId and questionId – Nir Alfasi Apr 17 '19 at 17:47
Looks like right arguments are being injected. This is what the log says- query: UPDATE vote SET "questionVoteCount" = "questionVoteCount" + 1 WHERE "questionId" = $1 AND "accountId" = $2 [ 1, 1 ] success Maybe I need to populate the "questionVoteCount" column to populate with 0 to begin with? – Dean Apr 19 '19 at 22:10

Dean · Accepted Answer · 2019-04-20T02:32:13.083

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.

`UPDATE vote SET "questionVoteCount" = "questionVoteCount" + 1 WHERE "questionId" = $1 AND "accountId" = $2` won't work. You'll have to come up with a subquery that first gets the current value of `questionVoteCount` and use it in order to update. — Nir Alfasi, Apr 21 '19 at 18:17

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

2 Answers2