对Sequelize关联感到困惑 [英] Confused about Sequelize associations

问题描述

我刚刚发现Sequelize是一个可以在我的节点+ MySQL webapp中使用的很好的ORM框架.但是当我设计数据库表时，我对Sequelize关联感到非常困惑.

只需考虑一个简单的案例即可了解Webapp中的用户和系统通知:

• 该网站有很多用户
• 该网站会将通知发送给一些或每个用户，并且同一条通知仅保存为一条记录
• 用户可以知道他/她收到的任何通知是否已阅读(在阅读日期之前)

然后我按预期设计了3张桌子:

1. 用户: id ，名称
2. 通知: id ， title ， content ， sendAt
3. Notify : id ， UserId (作为接收者)， NotificationId ， readAt

我可以通过Sequelize轻松定义 User 和 Notification 模型.但是我只是不知道如何通过外键 UserId 和 NotificationId 使用关联使 Notify 与这两个表相关.

我试图像这样使用 hasOne 关联:

  Notify.hasOne(User);Notify.hasOne(Notification); 

然后，在 User 和 Notification 表中都有一个 NotifyId 列.这不是我的期望.也许我想到了使用关联的错误方法，所以我想知道如何正确使用关联?

此外，如果我想以JSON格式获取结果:

  [{id:123，用户:[对象某用户]，通知:[对象某通知]，readAt:null}，{id:124，用户:[反对另一个用户]，通知:[反对某些通知]，readAt:"2012年10月29日星期一20:44:54 GMT + 0800"}] 

如何像以前使用的SQL中一样使用 find 方法查询:

  SELECTNotify.id为"Notify.id"，User.name为"User.name"，Notification.title为"Notification.title"，Notification.sendAt作为"Notification.sendAt"，Notify.readAt作为"Notify.readAt"来自用户，通知，通知在哪里Notify.UserId = User.id ANDNotify.NotificationId = Notification.id; 

解决方案

我找到了正确的方法.我意识到我的Notify表是一个完整表，因为它包含一个 readAt 列.因此，关联应定义为:

  Notification.hasMany(db.Notify);User.hasMany(db.Notify); 

然后一切正常.

查找中的第二个问题也已解决.只是这样使用:

  Notify.findAll({包括:['用户'，'通知']}).success(函数(通知){console.log(notify.user);console.log(notify.notification);}); 

I just found Sequelize as a good ORM framework to use in my node + MySQL webapp. But when I was designing my database tables, I got so confused about Sequelize associations.

Just think about user and system notification in a webapp as a very simple case:

• The site has many users
• The site would send notifications to some or every users, and the same notification just save as one record
• Users could know any notification he/she received is read or not(by a read date)

Then I design 3 tables as expect:

1. User: id, name
2. Notification: id, title, content, sendAt
3. Notify: id, UserId(as receiver), NotificationId, readAt

I can simply define User and Notification models by Sequelize. But I just don't know how to use associations to make Notify relate to the 2 tables, by foreign key UserId and NotificationId.

I have tried to use hasOne associations like this:

Notify.hasOne(User);
Notify.hasOne(Notification);

Then there comes a NotifyId column in both User and Notification tables. And this is not as my expect. Maybe I thought a wrong way to use associations, so I wanna know how to use it correctly?

Additionally, if I want to get results as the JSON:

[
    {id: 123, user: [Object some user], notification: [Object some notification], readAt: null},
    {id: 124, user: [Object another user], notification: [Object some notification], readAt: "Mon Oct 29 2012 20:44:54 GMT+0800"}
]

how can I use find method query just once like in a SQL I used before:

SELECT
    Notify.id as 'Notify.id',
    User.name as 'User.name',
    Notification.title as 'Notification.title',
    Notification.sendAt as 'Notification.sendAt',
    Notify.readAt as 'Notify.readAt'
FROM User, Notification, Notify
WHERE
    Notify.UserId = User.id AND
    Notify.NotificationId = Notification.id;

解决方案

And I found right way. I realized that my Notify table is a entiry table because containing a readAt column. So the association should be defined as:

Notification.hasMany(db.Notify);
User.hasMany(db.Notify);

then everything became OK.

The second problem in find has been resolved too. Just use like this:

Notify.findAll({
    include: ['User', 'Notification']
}).success(function (notify) {
    console.log(notify.user);
    console.log(notify.notification);
});

这篇关于对Sequelize关联感到困惑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！