Node.js sequelize associations include

Question

Is this a bug when I query models (short versions):

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' });

This query works fine (note include inside include):

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

But this query gives error (only "where" parameter added to the inner include):

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
  })

Code above gives error:
Unhandled rejection SequelizeDatabaseError: missing FROM-clause entry for table "Groups"

I checked the SQL code it produces, i can fix this by not using inner where clause, but adding some raw code to the where clause. How can I do something like this:

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
})

ADDED (code was prettified by an some online service):

Code generated without inner where(working fine):

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";

Code generated WITH inner where(wrong sql generated):

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'; 

Note on what really needed:

I don't need records that have users that are without groups or groups without section and so on. E.g. Attachment of groups to a users happens after that user was found (and it was decided that it will go into the results). Meaning that I need this "where" clause to be on the user model (at the same level as the first "inclusion" key in the object), but it needs to make check going through several tables (my real database is more complicated).

Answer 1

I had similar error. And I didn't find any answer for my problem. But I make it to work. I don't know if it will work also for you, but I write my solution.

Please try add required:false property in last include:

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
  })

Why this works for me, and should work for you? When you omit where in last subquery then by default required has value false. When you set where then by default required has value true. This guided me to this solution.

From docs as a confirmation:

[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

and

[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.

In short there is some problem with inner join. And when you set where property then this add subquery as inner join, unless you set required:false.