Bad Field Error using nested Includes w/ Where clauses

[cludden]

I'll preface this by saying I am by no means a SQL expert.

I have a model structure as follows:

Token.belongsTo(User)
User.belongsToMany(Group)
Group.belongsToMany(User)
Group.belongsToMany(Ability)
Ability.belongsToMany(Group)
Ability.belongsTo(Resource)

Nested includes work fine until a nested where clause is added, like so

   Token.findOne({
        where: {
            token: 'DECODED HASH GOES HERE',
            type: 'auth',
            expiresAt: {
                $gt: Date.now()
            }
        },
        include: [{
            model: User,
            as: 'user',
            include: [{
                model: Group,
                as: 'groups',
                include: [{
                    model: Ability,
                    as: 'abilities',
                    include: [{
                        model: Resource,
                        as: 'resource',
                        where: {        // works fine without this
                            name: 'SAMPLE RESOURCE NAME HERE'
                        },
                        attributes: ['name']
                    }]
                }]
            }]
        }]
    }).then(function(result) {
        return cb();
    }, function(err) {
        return cb(err);
    });

which results in the following error:

SequelizeDatabaseError: ER_BAD_FIELD_ERROR: Unknown column 'user.groups.abilities.resource.id' in 'field list'

and the following query (which is missing the nested criteria and seems to be generating incorrect sql):

SELECT `token`.*, 
       `user.groups`.`id` 
       AS `user.groups.id`, 
       `user.groups`.`name` 
       AS `user.groups.name`, 
       `user.groups`.`desc` 
       AS `user.groups.desc`, 
       `user.groups.group_users__user_groups`.`user_groups` 
       AS 
       `user.groups.group_users__user_groups.user_groups`, 
       `user.groups.group_users__user_groups`.`group_users` 
       AS 
       `user.groups.group_users__user_groups.group_users`, 
       `user.groups.abilities`.`id` 
       AS `user.groups.abilities.id`, 
       `user.groups.abilities`.`name` 
       AS `user.groups.abilities.name`, 
       `user.groups.abilities`.`desc` 
       AS `user.groups.abilities.desc`, 
       `user.groups.abilities`.`resource_id` 
       AS `user.groups.abilities.resource_id`, 
       `user.groups.abilities.ability_groups__group_abilities`.`group_abilities` 
       AS 
       `user.groups.abilities.ability_groups__group_abilities.group_abilities`, 
       `user.groups.abilities.ability_groups__group_abilities`.`ability_groups` 
       AS 
       `user.groups.abilities.ability_groups__group_abilities.ability_groups` 
FROM   (SELECT `token`.`id`, 
               `token`.`token`, 
               `token`.`type`, 
               `token`.`expiresat`, 
               `token`.`user_id`, 
               `user`.`id`                             AS `user.id`, 
               `user`.`email`                          AS `user.email`, 
               `user`.`first`                          AS `user.first`, 
               `user`.`last`                           AS `user.last`, 
               `user`.`mobile`                         AS `user.mobile`, 
               `user`.`office`                         AS `user.office`, 
               `user`.`hash`                           AS `user.hash`, 
               `user`.`status`                         AS `user.status`, 
               `user.groups.abilities.resource`.`id`   AS   // I don't think this is right
                      `user.groups.abilities.resource.id`,
               `user.groups.abilities.resource`.`name` AS
                      `user.groups.abilities.resource.name`
        FROM   `token` AS `token` 
               LEFT OUTER JOIN `user` AS `user` 
                            ON `token`.`user_id` = `user`.`id` 
        WHERE  `token`.`token` = 'DECODED HASH GOES HERE' 
AND `token`.`type` = 'auth' 
AND `token`.`expiresat` > 1437069434934 
 LIMIT  1) AS `token` 
LEFT OUTER JOIN (`group_users__user_groups` AS 
                `user.groups.group_users__user_groups` 
                 INNER JOIN `group` AS `user.groups` 
                         ON `user.groups`.`id` = 
                `user.groups.group_users__user_groups`.`group_users`) 
             ON `user.id` = `user.groups.group_users__user_groups`.`user_groups` 
LEFT OUTER JOIN (`ability_groups__group_abilities` AS 
                `user.groups.abilities.ability_groups__group_abilities` 
                 INNER JOIN `ability` AS `user.groups.abilities` 
                         ON `user.groups.abilities`.`id` = 
`user.groups.abilities.ability_groups__group_abilities`.`ability_groups`) 
             ON `user.groups`.`id` = 
`user.groups.abilities.ability_groups__group_abilities`.`group_abilities`; 

any help would be greatly appreciated!

[mickhansen]

The problem is that the where needs to happen in a subquery because of the cartesian product limiting.
Unfortuneatly because it's that nested it would need to do a full join in a subquery to check, and that's not something we support at the moment.

You'll likely have to wait till we support include.seperate which will run the include in it's own call removing this issue.

[cludden]

Thanks for the quick response! I'll keep an eye out for #4125. Keep up the good work!