时间戳

首页 > 解决方案 > 仅从 sequelize 原始查询而不是模型实例中获取 dataValues

database - 仅从 sequelize 原始查询而不是模型实例中获取 dataValues

问题描述

我正在使用 sequelize 并使用原始查询从表中获取数据。但是我得到了所有模型实例，而我只需要 dataValues。我的设置如下所示：

const sequelize = new Sequelize({
  database: process.env.PGDATABASE,
  username: process.env.PGUSER,
  password: process.env.PGPASS,
  host: process.env.PGHOST,
  port: process.env.PGPORT,
  dialect: "postgres"
});

getPostGres: () => {
    return sequelize;
  }

我查询数据库的方式如下所示：

let messageRatingsArr = await getPostGres().query(
    `SELECT mr.support_email, mr.support_name,
    (select count(mrn."chatId") as total FROM message_ratings as mrn WHERE mrn."ratingType"='NEGATIVE' and mr.support_email = mrn.support_email) as negative,
 (select count(mrp."chatId") as total FROM message_ratings as mrp WHERE mrp."ratingType"='POSITIVE' and mr.support_email = mrp.support_email) as positive,
 (select count(mrm."chatId") as total FROM message_ratings as mrm WHERE mrm."ratingType"='MIXED' and mr.support_email = mrm.support_email) as mixed,
 (select count(mru."chatId") as total FROM message_ratings as mru WHERE mru."ratingType"='NEUTRAL' and mr.support_email = mru.support_email) as neutral
      FROM message_ratings mr 
      WHERE mr."createdAt" >= '${properFromDate}' AND mr."createdAt" <= '${properToDate}'
     group by mr.support_email, mr.support_name 
     limit ${args.count} offset ${args.offset} `,
    {
      model: MessageRatingPG,
      mapToModel: true
    }
  );
  let messageRatings = messageRatingsArr.map(item=>{
    return item.dataValues;
  })
  let result = connectionFromArray(messageRatings, args);
  result.totalCount = messageRatings.length;
  return result;

如您所见，由于我正在映射查询中的数据，其中包含各种数据，如 dataValues、_options、isNewRecord 等，如果我有一个大数据集，则循环遍历数组效率不高，所以我能做什么只获取dataValues？

标签： databasepostgresqlormsequelize.js

解决方案

来自https://sequelize.org/master/manual/raw-queries.html：

如果您不需要访问元数据，您可以传入查询类型来告诉 sequelize 如何格式化结果。例如，对于一个简单的选择查询，您可以这样做：

sequelize.query("SELECT * FROM `users`", { type: sequelize.QueryTypes.SELECT})
  .then(users => {
    // We don't need spread here, since only the results will be returned for select queries
  })

现在，查看您的代码，并与文档中的下一段进行比较：

第二个选项是模型。如果您传递模型，则返回的数据将是该模型的实例。

// Callee is the model definition. This allows you to easily map a query to a predefined model
sequelize
  .query('SELECT * FROM projects', {
    model: Projects,
    mapToModel: true // pass true here if you have any mapped fields
  })
  .then(projects => {
    // Each record will now be an instance of Project
  })

我建议从您的原始代码中删除以下内容：

{
  model: MessageRatingPG,
  mapToModel: true
}

并将其替换为{ type: sequelize.QueryTypes.SELECT }

推荐阅读