Sequelize ORM M-M Relationship-CodePudding

I am trying to make a real time messenger app. I'm using mysql for my database and Sequelize as the ORM. I am unsure on how I should set up my schema for conversations.

A user can have many conversations, and a conversation can have many users (only 2), and so I assuming my choosing a Many-To-Many relationship is correct.

When trying this I end up with a junction table with a user_id and conversation_id. So when a conversation 2 rows will be inserted, one for each user_id. My issue lies in querying for a certain users conversations. When I do this, I would like to also get the user_id and other details about the second user in the conversation.

Currently, my query looks like this....

         const user_convos = await db.models.conversations.findAll({
            include: [{
                model: db.models.users,
                through: {
                    where: {userId: uid},
                    attributes: ['userId']
                }
            }]
        });

My json output looks like this...

{
    "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!

CodePudding user response：

If it's always a one-to-one conversation then you don't need a junction table at all. Just add two user id columns in the conversation table.
In this case assocaitions might look like this:

conversations.belongsTo(users, { foreignKey: 'initiator_id', as: 'initiator' })
conversations.belongsTo(users, { foreignKey: 'other_person_id', as: 'otherPerson' })

And a Sequelize query might look like this:

 const user_convos = await db.models.conversations.findAll({
            include: [{
                model: db.models.users,
                as: 'initiator',
                where: { id: uid },
            }, {
                model: db.models.users,
                as: 'otherPerson',
            }]
        });