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