我正在嘗試制作一個實時信使應用程式。我將 mysql 用于我的資料庫并使用 Sequelize 作為 ORM。我不確定我應該如何設定我的對話模式。
一個用戶可以有很多對話，一個對話可以有很多用戶（只有 2 個），所以我假設我選擇多對多關系是正確的。
嘗試此操作時，我最終得到了一個帶有 user_id 和 conversation_id 的聯結表。因此，當將插入 2 行對話時，每個 user_id 一個。我的問題在于查詢某些用戶的對話。當我這樣做時，我還想獲取有關對話中第二個用戶的 user_id 和其他詳細資訊。
目前，我的查詢看起來像這樣......
const user_convos = await db.models.conversations.findAll({
include: [{
model: db.models.users,
through: {
where: {userId: uid},
attributes: ['userId']
}
}]
});
我的 json 輸出看起來像這樣......
{
"id": 2,
"latest_message": "Hello World",
"createdAt": "2022-04-06T00:11:08.000Z",
"updatedAt": "2022-04-06T00:11:08.000Z",
"users": [
{
"id": 1,
"email": "[email protected]",
"username": "john101",
"first_name": "John",
"last_name": "Smith",
"conversation_user": {
"userId": 1
}
}
]
}
As you can see in the users key of the json object, I only get the data on the user making the query, but I would also like the data of the other user in the conversation. As aforementioned, this other user will have his own row in the junction table, so I the most obvious approach to me is to then query for all conversations using the 'id' in the output and then get the other users id and then query for their information.
Though this will probably work, this sounds longwinded and unsustainable in the long run since I multiple queries will be made. I am sure there is a much easier approach to this (as there always is), I would very much appreciate any feedback pertaining easier methods to go about this (Maybe some sort of advanced query method or a different database schema...).
Thanks in advance!
uj5u.com熱心網友回復：
如果始終是一對一的對話，那么您根本不需要連接表。只需在對話表中添加兩個用戶 ID 列。
在這種情況下，關聯可能如下所示：
conversations.belongsTo(users, { foreignKey: 'initiator_id', as: 'initiator' })
conversations.belongsTo(users, { foreignKey: 'other_person_id', as: 'otherPerson' })
Sequelize 查詢可能如下所示：
const user_convos = await db.models.conversations.findAll({
include: [{
model: db.models.users,
as: 'initiator',
where: { id: uid },
}, {
model: db.models.users,
as: 'otherPerson',
}]
});
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/457344.html