Node.js sequelize 关联包括

Question

这是我查询模型(简短版本)时的错误吗:

var User = db.define('User', {
  login: Sequelize.STRING(16),
  password: Sequelize.STRING,
});

var Group = db.define('Group', {
  name: Sequelize.STRING,
});

var GroupSection = db.define('GroupSection', {
  name: Sequelize.STRING,
});

Group.belongsTo(GroupSection, { as: 'GroupSection',
  foreignKey: 'GroupSectionId' });
GroupSection.hasMany(Group, { as: 'Groups', foreignKey: 'GroupSectionId' });

Group.belongsTo(Group, { as: 'ParentGroup', foreignKey: 'ParentGroupId' });
Group.hasMany(Group, { as: 'ChildGroups', foreignKey: 'ParentGroupId' });

User.belongsToMany(Group, { as: 'Groups', through: 'UsersToGroups' });
Group.belongsToMany(User, { as: 'Users', through: 'UsersToGroups' });

这个查询工作正常(注意 include ininclude):

User.findOne({
    include: [{
      model: Group,
      as: 'Groups',
      where: {
        name: 'Group name',
      },
      include: [{
        model: GroupSection,
        as: 'GroupSection',
      }]
    }]
  }).then(function(user) {
    // some code
  })

但是这个查询给出了错误(只有“where”参数添加到内部包含):

User.findOne({
    include: [{
      model: Group,
      as: 'Groups',
      where: {
        name: 'Group name',
      },
      include: [{
        model: GroupSection,
        as: 'GroupSection',
        where: {
          name: 'Some section name',
        },
      }]
    }]
  }).then(function(user) {
    // some code
  })

上面的代码给出了错误:
未处理的拒绝 SequelizeDatabaseError:缺少表“Groups”的 FROM 子句条目

我检查了它生成的 SQL 代码，我可以通过不使用内部 where 子句来解决这个问题，而是向 where 子句添加一些原始代码。我怎样才能做这样的事情:

User.findOne({
  include: [{
    model: Group,
    as: 'Groups',
    where: {
      name: 'Admin',
      $somethin_i_need$: 'raw sql goes here',
    },
    include: [{
      model: GroupSection,
      as: 'GroupSection',
    }]
  }]
}).then(function(user) {
  // some code
})

添加(代码由一些在线服务美化):

没有内部 where 生成的代码(工作正常):

SELECT "User".*,
       "groups"."id"                      AS "Groups.id",
       "groups"."name"                    AS "Groups.name",
       "groups"."createdat"               AS "Groups.createdAt",
       "groups"."updatedat"               AS "Groups.updatedAt",
       "groups"."groupsectionid"          AS "Groups.GroupSectionId",
       "groups"."parentgroupid"           AS "Groups.ParentGroupId",
       "Groups.UsersToGroups"."createdat" AS "Groups.UsersToGroups.createdAt",
       "Groups.UsersToGroups"."updatedat" AS "Groups.UsersToGroups.updatedAt",
       "Groups.UsersToGroups"."groupid"   AS "Groups.UsersToGroups.GroupId",
       "Groups.UsersToGroups"."userid"    AS "Groups.UsersToGroups.UserId",
       "Groups.GroupSection"."id"         AS "Groups.GroupSection.id",
       "Groups.GroupSection"."name"       AS "Groups.GroupSection.name",
       "Groups.GroupSection"."createdat"  AS "Groups.GroupSection.createdAt", 
       "Groups.GroupSection"."updatedat"  AS "Groups.GroupSection.updatedAt"
FROM   (SELECT "User"."id",
               "User"."login",
               "User"."password",
               "User"."createdat",
               "User"."updatedat"
        FROM   "users" AS "User"
        WHERE  (SELECT "userstogroups"."groupid"
                FROM   "userstogroups" AS "UsersToGroups"
                       INNER JOIN "groups" AS "Group"
                               ON "userstogroups"."groupid" = "Group"."id"
                WHERE  ( "User"."id" = "userstogroups"."userid" )
                LIMIT  1) IS NOT NULL
        LIMIT  1) AS "User"
       INNER JOIN ("userstogroups" AS "Groups.UsersToGroups"
                   INNER JOIN "groups" AS "Groups"
                           ON "groups"."id" = "Groups.UsersToGroups"."groupid")
               ON "User"."id" = "Groups.UsersToGroups"."userid"
                  AND "groups"."name" = 'Group name'
       LEFT OUTER JOIN "groupsections" AS "Groups.GroupSection"
                    ON "groups"."groupsectionid" = "Groups.GroupSection"."id";

通过内部 where 生成的代码(生成了错误的 sql):

SELECT "User".*, 
       "groups"."id"                      AS "Groups.id", 
       "groups"."name"                    AS "Groups.name", 
       "groups"."createdat"               AS "Groups.createdAt", 
       "groups"."updatedat"               AS "Groups.updatedAt", 
       "groups"."groupsectionid"          AS "Groups.GroupSectionId", 
       "groups"."parentgroupid"           AS "Groups.ParentGroupId", 
       "Groups.UsersToGroups"."createdat" AS "Groups.UsersToGroups.createdAt", 
       "Groups.UsersToGroups"."updatedat" AS "Groups.UsersToGroups.updatedAt", 
       "Groups.UsersToGroups"."groupid"   AS "Groups.UsersToGroups.GroupId", 
       "Groups.UsersToGroups"."userid"    AS "Groups.UsersToGroups.UserId" 
FROM   (SELECT "User"."id", 
               "User"."login", 
               "User"."password", 
               "User"."createdat", 
               "User"."updatedat", 
               "Groups.GroupSection"."id"        AS "Groups.GroupSection.id", 
               "Groups.GroupSection"."name"      AS "Groups.GroupSection.name", 
               "Groups.GroupSection"."createdat" AS 
               "Groups.GroupSection.createdAt", 
               "Groups.GroupSection"."updatedat" AS 
               "Groups.GroupSection.updatedAt" 
        FROM   "users" AS "User" 
               INNER JOIN "groupsections" AS "Groups.GroupSection" 
                       ON "groups"."GroupSectionId" = "Groups.GroupSection"."id" 
                          AND "Groups.GroupSection"."name" = 'Section name' 
        WHERE  (SELECT "userstogroups"."groupid" 
                FROM   "userstogroups" AS "UsersToGroups" 
                       INNER JOIN "groups" AS "Group" 
                               ON "userstogroups"."groupid" = "Group"."id" 
                WHERE  ( "User"."id" = "userstogroups"."userid" ) 
                LIMIT  1) IS NOT NULL 
        LIMIT  1) AS "User" 
       INNER JOIN ("userstogroups" AS "Groups.UsersToGroups" 
                   INNER JOIN "groups" AS "Groups" 
                           ON "groups"."id" = "Groups.UsersToGroups"."groupid") 
               ON "User"."id" = "Groups.UsersToGroups"."userid" 
                  AND "groups"."name" = 'Group name'; 

注意真正需要什么:

我不需要包含没有组的用户或没有部分的组等的记录。例如。在找到该用户后，将组附加到用户(并决定将其放入结果中)。这意味着我需要这个“where”子句在用户模型上(与对象中的第一个“inclusion”键处于同一级别)，但它需要检查几个表(我的真实数据库更复杂) .

Answer 1

我有类似的错误。而且我没有找到我的问题的任何答案。但我让它工作。我不知道它是否也适用于您，但我写了我的解决方案。

请尝试在最后的 include 中添加 required:false 属性:

User.findOne({
    include: [{
      model: Group,
      as: 'Groups',
      where: {
        name: 'Group name',
      },
      include: [{
        model: GroupSection,
        as: 'GroupSection',
        required: false,
        where: {
          name: 'Some section name',
        },
      }]
    }]
  }).then(function(user) {
    // some code
  })

为什么这对我有用，对你也应该有用？当您在最后一个子查询中省略 where 时，默认情况下 required 的值为 false。当您设置 where 时，默认情况下 required 的值为 true。这引导我找到了这个解决方案。

来自 docs作为确认:

[options.include[].where] Where clauses to apply to the child models. Note that this converts the eager load to an inner join, unless you explicitly set required: false

和

[options.include[].required] If true, converts to an inner join, which means that the parent model will only be loaded if it has any matching children. True if include.where is set, false otherwise.

简而言之，内部连接存在一些问题。当您设置 where 属性时，除非您设置了 required:false，否则这会将子查询添加为内部联接。