postgresql - sequelize.query 返回空数组

Question

使用 Sequelize 4.38.1 对 PostgreSQL 10.5 ，我有以下代码:

const criteria = {
  attended: '2018-09-21',
  location: 'Squirrel Hill',
  teacher: 'Doe, John',
  classTitle: 'Yoga 1'
};

const sql = `
  SELECT
    "Attendances"._id,
    "Attendances"."UserId",
    "Users"."lastName" || ', ' || "Users"."firstName" AS name
  FROM
    "Attendances" INNER JOIN "Users" ON "Attendances"."UserId" = "Users"._id
  WHERE
    "Attendances".attended = :attended AND
    "Attendances".location = :location AND
    "Attendances".teacher = :teacher AND
    "Attendances"."classTitle" = :classTitle
  ORDER BY "Users"."lastName", "Users"."firstName";`;

  sequelize.query(
    sql,
    {
      replacements: criteria,
      type: sequelize.QueryTypes.SELECT
    })
    .then(attendeeList => {
      console.log(attendeeList);
      return res.status(200).json(attendeeList);
    })

打开 sequelize 日志记录后，我的查询将正确地参数化为替换并执行:

SELECT
      "Attendances"._id,
      "Attendances"."UserId",
      "Users"."lastName" || ', ' || "Users"."firstName" AS name
    FROM
      "Attendances" INNER JOIN "Users" ON "Attendances"."UserId" = "Users"._id
    WHERE
      "Attendances".attended = '2018-09-21' AND
      "Attendances".location = 'Squirrel Hill' AND
      "Attendances".teacher = 'Doe, John' AND
      "Attendances"."classTitle" = 'Yoga 1'
    ORDER BY "Users"."lastName", "Users"."firstName";

问题:我的结果(attendeeList)作为 [] 返回。当我使用 pgAdmin 运行相同的查询时，我得到一行:

_id UserId  name
40  24601   "Doe, John"

有人对我做错了什么有任何想法吗？

Answer 1

我将上面的示例作为 Sequelize 的 Github 存储库上的问题提交，因为我能够使其与不包含 INNER JOIN 的查询一起使用。

对于我的情况， pg-native 更合适(考虑在其他地方撕掉 Sequelize ，因为它更慢)...

const client = new PGNativeClient();
  client.connect(config.sequelize.uri, err => {
    if(err) {
      res.status(401).json({ message: 'Not able to connect to database to get list of attendees.' });
      return;
    }
    const sql = `
      SELECT
        "Attendances"._id,
        "Attendances"."UserId",
        "Users"."lastName" || ', ' || "Users"."firstName" AS name
      FROM
        "Attendances" INNER JOIN "Users" ON "Attendances"."UserId" = "Users"._id
      WHERE
        "Attendances".attended = $1::DATE AND
        "Attendances".location = $2 AND
        "Attendances".teacher = $3 AND
        "Attendances"."classTitle" = $4
      ORDER BY "Users"."lastName", "Users"."firstName";`;
    const { attended, location, teacher, classTitle } = req.query;
    client.query(sql, [attended, location, teacher, classTitle], (err, rows) => {
      if(err) {
        res.status(424).json({ message: 'Not able to retrieve attendees from database (but connected successfully).' });
        return;
      }
      res.status(200).json(rows);
    });