"Unknown Column" error in Sequelize when using "limit" and "include" options
mysql, node.js, sequelize.js
Solution
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.
Problem
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.