NodeJS Sequelize：如何快速加载，关联，原始：真的吗？

cnjp1d6j 于 2023-01-12 发布在 Node.js

关注(0)|答案(2)|浏览(116)

在Node.js中快速加载是我知道如何使用Sequelize的唯一方法。我正在尝试从一个带有嵌套包含的MySQL数据库中导出所有用户调查数据。调查答案超过500k行，由于为返回的每一行创建一个示例，我的脚本因内存不足而崩溃。
我想让查询“原始”，只获取简单的对象数据，但它只返回每个include的第一个关联记录，而不是包含所有关联记录的数组。有没有办法获取所有关联记录，同时也让它成为原始的？或者Sequelize不应该以这种方式用于大型查询？下面是我的查询：

db.User.findAll({
  include: [
    { model: db.Referrer }, // has one Referrer
    { model: db.Individual }, // has many Individuals (children)
    {
      model: db.UserSurvey, // has many UserSurveys
      include: {
        model: db.Answer, // UserSurveys have many Answers
        include: {
          model: db.Question // survey question definition
        }
      }
    }
  ],
  raw: true // only returns first Individual, UserSurvey, Answer, etc.
  nest: true // unflattens but does not fix problem
})

如果我限制返回的行数，这个查询可以正常工作。如果我不限制它，它只会因为数据集的大小而崩溃。我试过将raw添加到顶层和各种include中的任何地方，但似乎都不起作用。我应该尝试将查询基于Answer吗？所以所有的关系只需要一个单一的记录？或者有没有一种方法可以使这些复杂的查询原始，并包括所有相关的记录？感谢阅读，这件事把我难倒了好几天。

node.js

来源：https://stackoverflow.com/questions/64546830/sequelize-how-to-eager-load-with-associations-raw-true

2条答案

按热度按时间

vohkndzv1#

正如Sequelize文档中关于raw选项所述：
sequelize不会尝试设置查询结果的格式，也不会根据结果构建模型的示例
这意味着如果你有1个主记录和2个关联记录，你会得到2个记录，因为这是Sequelize从SQL-query得到的。
我想在你的情况下，你应该在一个循环中使用limit和offset选项，通过块来获取记录。这样你就不会导致内存不足的结果。
同样，要获取普通对象而不是模型，请对每个模型使用get({ plain: true })，如下所示：

const users = db.User.findAll({
  include: [
    { model: db.Referrer }, // has one Referrer
    { model: db.Individual }, // has many Individuals (children)
    {
      model: db.UserSurvey, // has many UserSurveys
      include: {
        model: db.Answer, // UserSurveys have many Answers
        include: {
          model: db.Question // survey question definition
        }
      }
    }
  ]
})
const plainUsers = users.map(x => x.get({ plain: true }))

赞(0）回复(0）举报 2023-01-12

z9smfwbn2#

不要使用raw: true，这将导致大量的序列化问题。
使用toJSON()代替

const usersDao = await models.sequelize.query("SELECT `External_Profile`.*, AVG(Connections.rating) AS rating, `Connections`.`id` AS `Connections.id`, `Connections`.`known_type` AS `Connections.known_type`, `Connections`.`rating` AS `Connections.rating`, `Connections`.`createdAt` AS `Connections.createdAt`, `Connections`.`updatedAt` AS `Connections.updatedAt`, `Connections`.`UserId` AS `Connections.UserId`, `Connections`.`ExternalProfileId` AS `Connections.ExternalProfileId`, `Connections->User`.`id` AS `Connections.User.id`, `Connections->User`.`first_name` AS `Connections.User.first_name`, `Connections->User`.`last_name` AS `Connections.User.last_name`, `Connections->User`.`email` AS `Connections.User.email`, `Connections->User`.`password` AS `Connections.User.password`, `Connections->User`.`linkedinUrl` AS `Connections.User.linkedinUrl`, `Connections->User`.`createdAt` AS `Connections.User.createdAt`, `Connections->User`.`updatedAt` AS `Connections.User.updatedAt` FROM (SELECT `External_Profile`.`id`, `External_Profile`.`profile_data`, `External_Profile`.`name`, `External_Profile`.`headline`, `External_Profile`.`image`, `External_Profile`.`linkedinUrl`, `External_Profile`.`createdAt`, `External_Profile`.`updatedAt` FROM `External_Profiles` AS `External_Profile`) AS `External_Profile` LEFT OUTER JOIN `Connections` AS `Connections` ON `External_Profile`.`id` = `Connections`.`ExternalProfileId` LEFT OUTER JOIN `Users` AS `Connections->User` ON `Connections`.`UserId` = `Connections->User`.`id` GROUP BY External_Profile.name HAVING AVG(Connections.rating) > 3",  
        { 
            type: models.sequelize.QueryTypes.SELECT,
            model: [models.External_Profile, models.Connection],
            mapToModel: true,
            nest: true,
            raw: true
        })

const cleanUser = usersDao.toJSON()

赞(0）回复(0）举报 2023-01-12

我来回答

NodeJS Sequelize：如何快速加载，关联，原始：真的吗？

2条答案

相关问题

热门标签

最新问答