如何使用sequelize.js从关联的模型加载属性

本文介绍了如何使用sequelize.js从关联的模型加载属性的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我使用两个模型简化了当前项目.

I am simplify the current project with two models.

信息亭模式

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 }); }