如何使用 sequelize.js 从关联模型中加载属性 [英] How to load attributes from associated models with sequelize.js

问题描述

我正在用两个模型简化当前项目.

自助服务终端模式

module.exports = function(sequelize, DataTypes) {
    return sequelize.define('kiosk', {
        id: {
            type: DataTypes.BIGINT,
            allowNull: false,
            primaryKey: true,
            autoIncrement: true
        },
        name: {
            type: DataTypes.STRING(150),
            allowNull: false
        },
    }, {
        tableName: 'kiosk',
        timestamps: false,
        underscored: true
    });
};

收据模型

module.exports = function(sequelize, DataTypes) {
    return sequelize.define('receipt', {
        id: {
            type: DataTypes.STRING(255),
            allowNull: false,
            primaryKey: true
        },
        kiosk_id: {
            type: DataTypes.BIGINT,
            allowNull: false,
            references: {
                model: 'kiosk',
                key: 'id'
            }
        },
        customer_id: {
            type: DataTypes.BIGINT,
            allowNull: false,
            references: {
                model: 'customer_type',
                key: 'id'
            }
        },
        total: {
            type: DataTypes.DECIMAL,
            allowNull: false
        },
    }, {
        tableName: 'receipt',
        timestamps: false,
        underscored: true
    });
};

模型被缩小以简化问题

我的协会

models.receipt.belongsTo(models.kiosk, {
    as: 'kiosk'
});
models.kiosk.hasMany(models.receipt, {
    as: 'receipt'
});

最终查询:

const Kiosk = require('../models').kiosk;
const R = ReceiptModel = require('../models').receipt;
try {
        let receiptModels =  await ReceiptModel.findAll({
            raw: true,
            attributes: [['kiosk.name', 'KioskName']],
            include: [{
                model: Kiosk,
                required: false,
                as: 'kiosk',
                attributes: []
            }],
        });
        return res.json(receiptModels);
    }catch(err){
        // I am getting Error
        return res.status(500).send({ msg: err.message });
    }

我的期望:

receiptModels = [{
    KioskName: 'MyKioskName#1'
    },{
    KioskName: 'MyKioskName#2'
    },{
    KioskName: 'MyKioskName#3'
    },
];

但是，我收到以下错误:

But, I am getting the following errors:

{
  "name": "SequelizeDatabaseError",
  "parent": {
    "code": "ER_BAD_FIELD_ERROR",
    "errno": 1054,
    "sqlState": "42S22",
    "sqlMessage": "Unknown column 'kiosk.name' in 'field list'",
    "sql": "SELECT `kiosk.name` AS `KioskName` FROM `receipt` AS `receipt` LEFT OUTER JOIN `kiosk` AS `kiosk` ON `receipt`.`kiosk_id` = `kiosk`.`id` WHERE `receipt`.`created_at` >= '2020-01-20 05:00:00' LIMIT 2;"
  },
  "original": {
    "code": "ER_BAD_FIELD_ERROR",
    "errno": 1054,
    "sqlState": "42S22",
    "sqlMessage": "Unknown column 'kiosk.name' in 'field list'",
    "sql": "SELECT `kiosk.name` AS `KioskName` FROM `receipt` AS `receipt` LEFT OUTER JOIN `kiosk` AS `kiosk` ON `receipt`.`kiosk_id` = `kiosk`.`id` WHERE `receipt`.`created_at` >= '2020-01-20 05:00:00' LIMIT 2;"
  },
  "sql": "SELECT `kiosk.name` AS `KioskName` FROM `receipt` AS `receipt` LEFT OUTER JOIN `kiosk` AS `kiosk` ON `receipt`.`kiosk_id` = `kiosk`.`id` WHERE `receipt`.`created_at` >= '2020-01-20 05:00:00' LIMIT 2;"
}

注意:我的帖子与这篇文章类似，尽管它(帖子)不能解决我的问题.这是紧急情况，根据 StackOverflow 政策，我们不应该在关闭的问题上发布.从 sequelize.js 中的关联模型中加载属性

NB: My post is similar with this one though it (the post) doesn't fix my problem. And this an emergency and according to StackOverflow policy we shouldn't post on a close issue. Load attributes from associated model in sequelize.js

推荐答案

我使用 sequelize.col() 函数让它工作.

I have it working by using sequelize.col() function.

以下是最终/有效的查询代码:

Following is the final/working query code:

try {
        let receiptModels =  await ReceiptModel.findAll({
            raw: true,
            attributes: [[Sequelize.col('kiosk.name'), 'Kiosk']],
            include: [{
                model: Kiosk,
                required: false,
                as: 'kiosk',
                attributes: []
            }],
        });
        return res.json(receiptModels);
    }catch(err){
        return res.status(500).send({ msg: err.message });
    }

这篇关于如何使用 sequelize.js 从关联模型中加载属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！