4

I'm trying to determine if a user has voted on a question. The user clicks on an upvote/downvote button which makes a request to an API that pulls out their id form a cookie and then stores it within an upvoters or downvoters array. The upvote/downvote logic works as expected -- no problem there -- however, I'm having trouble determining whether or not the upvoters or downvoters array contains their id when the user queries questions by a specific tag.

The question model:

 Column                  │ Type                      │  Modifiers
─────────────────────────┼───────────────────────────┼──────────────────────────────────────────────
 key                     │ Serial                    │  PRIMARY KEY
 userid                  │ UUID                      │  NOT NULL REFERENCES users(id)
 date                    │ TIMESTAMP                 │  WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP
 answered                │ BOOLEAN                   │  DEFAULT FALSE
 views                   │ INTEGER                   │  DEFAULT 0
 upvoters                | TEXT[]                    |  DEFAULT array[]::text[]
 downvoters              | TEXT[]                    |  DEFAULT array[]::text[]
 title                   | TEXT                      |  NOT NULL DEFAULT ''
 uniquetitle             | VARCHAR                   |  NOT NULL DEFAULT ''
 body                    | TEXT                      |  NOT NULL DEFAULT ''
 tags                    | TEXT[]                    |  DEFAULT array[]::text[]
 comments                | JSONB                     |

The API endpoint:

import get from "lodash.get";
import isEmpty from "lodash.isempty";
import db from "~db"; // connection to Postgres using pg-promise

const findAllQuestionsByTagLimitAndOffset = `
  SELECT 
     questions.key, 
     questions.userid, 
     questions.date, 
     questions.body, 
     questions.answered, 
     questions.views, 
     cardinality(questions.upvoters)-cardinality(questions.downvoters) as votes, 
     EXISTS(
       SELECT FROM questions WHERE questions.upvoters @> $4
     ) as upvoted, // check whether or not the logged in user has upvoted this question
     EXISTS(
        SELECT FROM questions WHERE questions.upvoters @> $4
     ) as downvoted, // check whether or not the logged in user has downvoted this question
     questions.title, 
     questions.uniquetitle, 
     questions.tags, 
     users.username, // appends original poster's username
     users.reputation as userrep, // appends original poster's rep
     users.key as userkey // appends original poster's id
  FROM questions 
  INNER JOIN users ON questions.userid=users.id 
  WHERE questions.tags @> $1 
  ORDER BY questions.date DESC 
  LIMIT $2 
  OFFSET $3
`;

/**
 * Fetches questions by tag.
 *
 * @function fetchNewestQuestionsByTag
 * @param {object} req - request object
 * @param {object} res - response object
 * @returns {array} data - questions that contain the queried tag with original poster populated data and some logged in user data
 * @throws {string} err
 */
const fetchNewestQuestionsByTag = async (req, res) => {
  try {
    const { tag } = req.query;
    if (!tag) throw String("Unable to locate questions because the tag is not valid.");
    const userid = get(req.session, ["id"]);

    const data = await db.any(findAllQuestionsByTagLimitAndOffset, [
      [tag],
      10,
      0,
      [userid],
    ]);
    if (isEmpty(data)) throw String("Unable to locate any questions.");

    res.status(201).send(data);
  } catch (err) {
    res.status(404).send(err);
  }
};

Here's how the question looks after an upvote:

answered: false
body: "Test"
date: "2020-06-19T20:24:46.496Z"
downvoters: []
comments: []
title: "Test Title"
tags: ["reactjs"]
uniquetitle: "test-title"
upvoters ["d17a33f4-b26a-11ea-9372-131fa959a01b"] // logged in user id is stored
userid: "d17a33f4-b26a-11ea-9372-131fa959a018"
views: 1

After a user has upvoted and refreshes the page and/or queries the question they upvoted by a tag, the following (incorrect) data structure is returned:

answered: false
body: "Test"
date: "2020-06-19T20:24:46.496Z"
downvoted: false
tags: ["reactjs"]
title: "Test Title"
uniquetitle: "test-title"
upvoted: false // this should be true as the logged in user is "d17a33f4-b26a-11ea-9372-131fa959a01b" and their id is contained within this array, yet it is still false
userid: "d17a33f4-b26a-11ea-9372-131fa959a018"
userkey: 1
username: "Matt"
userrep: 0
views: 1
votes: 1

The problem is that these queries always return false:

  EXISTS(
    SELECT FROM questions WHERE questions.upvoters @> $4
  ) as upvoted,
  EXISTS(
    SELECT FROM questions WHERE questions.downvoters @> $4
  ) as downvoted,

I've tried the following and none of them returns the correct boolean status:

How to make a select with array contains value clause in psql

Check if value exists in Postgres array

Postgres: check if array field contains value?

What am I doing wrong?

Matt Carlotta
  • 18,972
  • 4
  • 39
  • 51

1 Answers1

3

Your use of EXISTS would check in all table rows instead of just the row at hand.

Also, you had upvoters twice. Must be downvoters for downvoted.

I suggest:

  SELECT 
     q.key, 
     q.userid, 
     q.date, 
     q.body, 
     q.answered, 
     q.views, 
     cardinality(q.upvoters)-cardinality(q.downvoters) AS votes, 
     $4 = ANY (q.upvoters) AS upvoted,
     $4 = ANY (q.downvoters) AS downvoted,
     q.title, 
     q.uniquetitle, 
     q.tags, 
     u.username,
     u.reputation AS userrep,
     u.key as userkey
  FROM   questions q
  JOIN   users     u ON q.userid = u.id 
  WHERE  q.tags @> $1 
  ORDER  BY q.date DESC 
  LIMIT  $2 
  OFFSET $3

The array operator @> expects array types on both sides. Seems like you got that right, passing [userid] (constructs an array, right?). With my query, just pass the plain string value userid.

WHERE q.tags @> $1 may still make sense as is, if you aim for index usage. See:

Also, d17a33f4-b26a-11ea-9372-131fa959a01b suspiciously looks like a UUID. Don't use data type text / text[] for it. Use uuid / uuid[]. Much better in multiple respects. See:

But all of these grave issues still cannot explain what you report. You should have seen true for both upvoted and downvoted. There may be more mistakes ...

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228