使用&Quot;Limit&Quot;和&Quot;Include&Quot;选项时,序列化中出现未知列&Quot;错误(&QOOT;INCLUDE"OPTIONS)

quot;Unknown Columnquot; error in Sequelize when using quot;limitquot; and quot;includequot; options(使用Quot;LimitQuot;和Quot;IncludeQuot;选项时,序列化中出现未知列Quot;错误(QOOT;INCLUDEQUOT;OPTIONS))

本文介绍了使用&Quot;Limit&Quot;和&Quot;Include&Quot;选项时,序列化中出现未知列&Quot;错误(&QOOT;INCLUDE"OPTIONS)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个相对复杂的序列化查询,结果是"ERROR:ER_BAD_FIELD_ERROR:UNKNOWN COLUMN‘EventImage.EventId’in‘WHERE子句’"。我可以清楚地看到生成的SQL中的问题,但是我不知道如何解决它。 错误是准确的,因为子查询中生成的WHERE子句引用了子查询中未包括的列。据我所知,Sequelize生成子查询是作为实现行限制的一种方式。

我使用的是Sequelize@2.0.0-dev11、MySQL 5.5.35-0ubuntu0.13.10.2和Node v0.10.21。

以下是基本代码:

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’中 (1,2)"
偏移=0
行=12
orderby="Image.createdAt 说明"

使用以下选项初始化Sequelize:

下划线:false,
FreezeTableName:true,
paranoid:true,
syncOnAssociation:true,
字符集:‘utf8’,
排序: ‘utf8_General_ci’,
时间戳:true

以下是生成的SQL:

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;

以下是相关表格的EER图:

如有任何帮助,我们将不胜感激。

推荐答案

我不确定是否还需要它,但对于那些正在受苦的人,请尝试添加 subQuery: false到查询。Source.

如果不起作用,请尝试将separate: true添加到子查询中。Source.

希望对您有所帮助,gl.

这篇关于使用&Quot;Limit&Quot;和&Quot;Include&Quot;选项时,序列化中出现未知列&Quot;错误(&QOOT;INCLUDE"OPTIONS)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本文标题为:使用&Quot;Limit&Quot;和&Quot;Include&Quot;选项时,序列化中出现未知列&Quot;错误(&QOOT;INCLUDE"OPTIONS)

基础教程推荐