sequelize.js - Sequelize：如果声明不是基于 PK 并且适用于 PK 搜索，getOne() 将抛出“字段列表中的未知列 (...)”

Question

我正在使用getOne()函数来提取具有关联模型的用户数据。如果我PK在这样的where子句中使用：where: { ID: 128 }- 它工作正常。这意味着关联和查询配置是正确的。

但是，如果我试图按email这样的字段查找用户：where: { email: 'email_of@user_with_ID_128.com' }- Sequelize 会为最深关联模型的字段引发错误Unknown column (...) in field list。电子邮件是正确的，它指向与按 ID 搜索相同的用户。如果我从查询配置或设置中排除关联required: false- 查询成功并且可以使用email.

这是查询行：

return models.User.findOne(config).then(user => { console.log(user) });

和选项/配置对象：

config = {
    where: { ID: 128 }, // THIS WORKS
    // OR
    where: { email: 'email_of@user_with_ID_128.com' }, // THIS THROWS ERROR
    include: [
        {
            nested: true,
            model: models.UserComponent,
            attributes: ['ID', 'isDisplayed', 'displayIndex'],
            include: [
                {
                    model: models.Component,
                    required: true,
                    include: [
                        {
                            model: models.ComponentType, // error relates to this model
                            required: true // doesn't throw error if set to false
                        },
                        {
                            model: models.ObjectType, // and error relates to this model
                            required: true // doesn't throw error if set to false
                        },
                        {
                            model: models.ComponentContent,
                            required: false
                        }
                    ]
                }
            ]
        }
    ],
    nested: true    
};

型号定义：

const User = connectionPool.define('user', {
    ID: {
        type: Sequelize.INTEGER,
        autoIncrement: true,
        allowNull: false,
        primaryKey: true
    },
    name: {
        type: Sequelize.STRING,
        allowNull: false,
    },
    email: {
        type: Sequelize.STRING,
        allowNull: false
    },
    surname: {
        type: Sequelize.STRING,
        allowNull: false,
    }
});
const Component = connectionPool.define('component', {
    ID: {
        type: Sequelize.INTEGER,
        autoIncrement: true,
        allowNull: false,
        primaryKey: true
    },
    defaultIsDisplayed: {
        type: Sequelize.BOOLEAN,
        allowNull: false
    },
    defaultDisplayIndex: {
        type: Sequelize.INTEGER,
        allowNull: false
    },
    obligatory: {
        type: Sequelize.BOOLEAN,
        allowNull: false,
        default: false
    }
}, {
    defaultScope: {
        attributes: {
            exclude: ['createdAt', 'updatedAt',
                'componentContentID', 'objectTypeID',
                'componentTypeID']
        }
    }
}
);

const UserComponent = connectionPool.define('user_component', {
    ID: {
        type: Sequelize.INTEGER,
        autoIncrement: true,
        allowNull: false,
        primaryKey: true
    },
    isDisplayed: {
        type: Sequelize.BOOLEAN,
        allowNull: false
    },
    displayIndex: {
        type: Sequelize.INTEGER,
        allowNull: false
    }
}, {
    defaultScope: {
        attributes: { exclude: ['createdAt', 'updatedAt', 'componentID', 'userID', 'ID'] }
    }
});

const ComponentType = connectionPool.define('component_type', {
    ID: {
        type: Sequelize.INTEGER,
        autoIncrement: true,
        allowNull: false,
        primaryKey: true
    },
    name: {
        type: Sequelize.STRING,
        allowNull: false
    }
}, {
    defaultScope: {
        attributes: { exclude: ['ID', 'createdAt', 'updatedAt'] }
    }
});
const ComponentContent = connectionPool.define('component_content', {
    ID: {
        type: Sequelize.INTEGER,
        autoIncrement: true,
        allowNull: false,
        primaryKey: true
    },
    content: {
        type: Sequelize.STRING,
        allowNull: false
    }
});

协会：

models.Component.belongsTo(models.ComponentContent, { foreignKey: 'componentContentID', onDelete: 'CASCADE' });
models.Component.belongsTo(models.ComponentType, { foreignKey: 'componentTypeID', onDelete: 'CASCADE' });
models.Component.belongsTo(models.ObjectType, { foreignKey: 'objectTypeID', onDelete: 'CASCADE' });

models.Component.hasMany(models.UserComponent, { foreignKey: 'componentID', onDelete: 'CASCADE' });
models.UserComponent.belongsTo(models.Component, { foreignKey: 'componentID', onDelete: 'CASCADE' });
models.User.hasMany(models.UserComponent, { foreignKey: 'userID', onDelete: 'CASCADE' });

where如果语句基于PK并且未能将其他字段作为条件，我认为查询将正确附加关联没有逻辑上的原因。文档没有给出任何提示。有谁知道这种行为可能是什么原因？我想避免预先user查找ID或不需要相关模型。

标签： sequelize.js