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 · Accepted Answer · 2019-04-17 16:49:19Z

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, 2019 at 16:49

answered Apr 17, 2019 at 3:29

Nir Alfasi

53.6k11 gold badges94 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Dean Over a year ago

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.

Nir Alfasi Over a year ago

@Dean see revised answer.

Dean Over a year ago

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)

Nir Alfasi Over a year ago

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

Dean Over a year ago

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 · Accepted Answer · 2019-04-20 02:32:13Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Related