"Unknown Column" error in Sequelize when using "limit" and "include" options

Question

I have a relatively complex Sequelize query that results in "Error: ER_BAD_FIELD_ERROR: Unknown column 'EventImage.EventId' in 'where clause'". I can clearly see the problem in the SQL that's generated, but I don't know how to resolve it.

The error is accurate as the WHERE clause that's generated in the sub-query is referencing a column not included in the sub-query. As far as I can tell, Sequelize is generating the sub-query as a way to implement the row limit.

I'm using sequelize@2.0.0-dev11, MySQL 5.5.35-0ubuntu0.13.10.2, and Node v0.10.21.

Here's the basic code:

var orm = require('../model');
var i = orm.Image;

i.findAll({
    where: where,
    offset: offset,
    limit: rows,
    order: orderby,
    include: [{
        model: orm.User,
        as: 'User'
    },
    {
        model: orm.Event,
        as: 'Events'
    },
    {
        model: orm.Comment,
        as: 'Comments'
    },
    {
        model: orm.Favorite,
        as: 'Favorites'
    }
    ]
})

where = "'Image'.'Category'='gallery' AND 'EventImage'.'EventId' in (1,2)"
offset = 0
rows = 12
orderby = "Image.createdAt DESC"

Sequelize is initialized with the following options:

underscored: false,
freezeTableName: true,
paranoid: true,
syncOnAssociation: true,
charset: 'utf8',
collate: 'utf8_general_ci',
timestamps: true

Here's the SQL that gets generated:

SELECT 
    `Image` . *,
    `User`.`id` AS `User.id`,
    `User`.`LoginName` AS `User.LoginName`,
    `User`.`FirstName` AS `User.FirstName`,
    `User`.`LastName` AS `User.LastName`,
    `User`.`EmailAddress` AS `User.EmailAddress`,
    `User`.`ProfileImage` AS `User.ProfileImage`,
    `User`.`Password` AS `User.Password`,
    `User`.`Enabled` AS `User.Enabled`,
    `User`.`Expiry` AS `User.Expiry`,
    `User`.`createdAt` AS `User.createdAt`,
    `User`.`updatedAt` AS `User.updatedAt`,
    `User`.`deletedAt` AS `User.deletedAt`,
    `Events`.`id` AS `Events.id`,
    `Events`.`StartDate` AS `Events.StartDate`,
    `Events`.`EndDate` AS `Events.EndDate`,
    `Events`.`Title` AS `Events.Title`,
    `Events`.`Description` AS `Events.Description`,
    `Events`.`createdAt` AS `Events.createdAt`,
    `Events`.`updatedAt` AS `Events.updatedAt`,
    `Events`.`deletedAt` AS `Events.deletedAt`,
    `Events`.`UserId` AS `Events.UserId`,
    `Events`.`ImageId` AS `Events.ImageId`,
    `Events.EventImage`.`createdAt` AS `Events.EventImage.createdAt`,
    `Events.EventImage`.`updatedAt` AS `Events.EventImage.updatedAt`,
    `Events.EventImage`.`ImageId` AS `Events.EventImage.ImageId`,
    `Events.EventImage`.`EventId` AS `Events.EventImage.EventId`,
    `Comments`.`id` AS `Comments.id`,
    `Comments`.`Body` AS `Comments.Body`,
    `Comments`.`createdAt` AS `Comments.createdAt`,
    `Comments`.`updatedAt` AS `Comments.updatedAt`,
    `Comments`.`deletedAt` AS `Comments.deletedAt`,
    `Comments`.`UserId` AS `Comments.UserId`,
    `Comments`.`ImageId` AS `Comments.ImageId`,
    `Comments`.`EventId` AS `Comments.EventId`,
    `Favorites`.`id` AS `Favorites.id`,
    `Favorites`.`createdAt` AS `Favorites.createdAt`,
    `Favorites`.`updatedAt` AS `Favorites.updatedAt`,
    `Favorites`.`UserId` AS `Favorites.UserId`,
    `Favorites`.`ImageId` AS `Favorites.ImageId`
FROM
    (SELECT 
        `Image` . *
    FROM
        `Image` AS `Image`
    WHERE
        `Image`.`Category` = 'gallery'
            AND `EventImage`.`EventId` in (2)
            AND `Image`.`deletedAt` IS NULL
    LIMIT 12) AS `Image`
        LEFT OUTER JOIN
    `User` AS `User` ON `User`.`id` = `Image`.`UserId`
        LEFT OUTER JOIN
    `EventImage` AS `Events.EventImage` ON `Image`.`id` = `Events.EventImage`.`ImageId`
        LEFT OUTER JOIN
    `Event` AS `Events` ON `Events`.`id` = `Events.EventImage`.`EventId`
        LEFT OUTER JOIN
    `Comment` AS `Comments` ON `Image`.`id` = `Comments`.`ImageId`
        LEFT OUTER JOIN
    `Favorite` AS `Favorites` ON `Image`.`id` = `Favorites`.`ImageId`
ORDER BY Image.createdAt DESC;

Here's an EER diagram of the relevant tables:

Any help would be appreciated.

Answer 1

when a model is included with an alias and its inside an another model also included then the issue arises because of inside model which does not support the given alias given for the parent included model, so the solution is to use the subquery = false for model on which you want to set order and the inner model then does not call for that purpose.

include: [
                {
                    model: TeacherDetail.scope('teacherListing'),
                    where: {
                    },
                    subQuery: false,

                    include: [
                        {
                            model: Education,
                            attributes: [],
                        }
                    ]
                },
]

Answer 2

I'm not sure if it still needs it, but for those who's suffering, try adding subQuery: false to the query. Source.

If it won't work, try adding separate: true to the subqueries. Source.

Hope it helps, gl.