本文介绍了如何使用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 }); }更多推荐
如何使用sequelize.js从关联的模型加载属性
发布评论