我可以在没有在 Seuqelize 中定义 assocaition 的情况下进行连接查询吗?
我尝试通过 Sequelize 进行如下 sql 查询
SELECT * FROM MessageRecipient INNER JOIN MESSAGE ON MessageRecipient.messageIdno = MESSAGE.Idno
我确实在使用 findAll 时使用添加的"包含"来做到这一点,如下所示,我必须在使用 include 之前定义关联
消息.js
var Message = sequelize.define('Message', {
idno: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true
},
subject: DataTypes.STRING,
messageBody: DataTypes.STRING,
creatorId: DataTypes.INTEGER,
parentMessageId: DataTypes.INTEGER,
expiryDate:DataTypes.DATE,
isActive:DataTypes.INTEGER}, {
classMethods: {
associate: function (models) {
Message.hasMany(models.MessageRecipient, { foreignKey: 'idno' });
}}});
return ChatMessage;
};
消息收件人.js
var MessageRecipient = sequelize.define('MessageRecipient', {
idno: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true
},
recipientId: DataTypes.INTEGER,
recipientGroupId: DataTypes.INTEGER,
messageId: DataTypes.INTEGER,
isRead:DataTypes.INTEGER
}, {
classMethods: {
associate: function (models) {
MessageRecipient.belongsTo(models.Message, { foreignKey: 'messageId' });
}
}
}); return MessageRecipient;
查找所有包含
db.MessageRecipient
.findAll({
include: [{
model: db.Message,
required: true
}]
})
.then((MessageRecipients) => {
res.json(MessageRecipients);
})
.error((error) => {
res.sendStatus(500);
});
我会得到我想要的查询结果,如下所示
[
{
"idno": 1,
"recipientId": null,
"recipientGroupId": 1,
"messageId": 1,
"isRead": 0,
"createdAt": "2017-04-14T01:24:31.000Z",
"updatedAt": "2017-04-14T01:24:31.000Z",
"ChatMessage": {
"idno": 1,
"subject": "my message subject",
"messageBody": "my message content here",
"creatorId": 1,
"parentMessageId": 0,
"expiryDate": null,
"isActive": 1,
"createdAt": "2017-04-14T01:24:30.000Z",
"updatedAt": "2017-04-14T01:24:30.000Z"
}
}
]
如果我在找到所有之前没有定义关联,我会得到错误
Unhandled rejection Error: ChatMessage is not associated to ChatMessageRecipient!
我怎样才能跳过 hasmany,属于部分,仍然得到像上面这样的漂亮结果?有没有解决方法可以做到这一点?
谢谢
我已经搜索了文档,发现没有办法指定您加入的字段。据我所知,没有办法使用标准查询来做到这一点。您要么必须执行单独的(且效率低下的(查询,要么必须使用原始查询:
sequelize.query('SELECT * FROM MessageRecipient INNER JOIN MESSAGE ON MessageRecipient.messageIdno = MESSAGE.Idno').then(([results, metadata]) => {
// ...
})
此示例也来自文档。