Sequelize include with an OR statement on the join

Question

I have a user entity with the following relationship:

  User.associate = (models) => {
    User.belongsToMany(User, {
      as: "friends",
      through: "user_friend",
      onDelete: "CASCADE",
    })
    User.hasMany(models.Message, { as: "sentMessages", foreignKey: "senderId" })
    User.hasMany(models.Message, {
      as: "receivedMessages",
      foreignKey: "recipientId",
    })
  }

and a messages entity with relationship:

  Message.associate = (models) => {
    Message.belongsTo(models.User, { foreignKey: "senderId" })
    Message.belongsTo(models.User, { foreignKey: "recipientId" })
  }

and I'm attempting to do something like: get me a user, include their friends, include their conversation history with each friend using the following:

    let user = await User.findOne({
      where: {
        auth_key: auth_key,
      },
      include: {
        association: "friends",
        attributes: ["first_name", "last_name", "chat_name", "profile_picture"],
        through: {
          attributes: [],
        },
        include: [
          {
            required: false,
            association: "sentMessages",
            where: {
              recipientId: 1,
            },
          },
          {
            required: false,
            association: "receivedMessages",
            where: {
              senderId: 1,
            },
          },
        ],
      },
    })

which does kind of work but it seems really messy and I don't like having the messages split into sent and received, I would just like them grouped together. Currently the response looks like this:

{
    "id": 1,
    "username": "ajsmith",
    "first_name": "Anthony",
    "last_name": "Smith",
    "chat_name": "Anthony.Smith",
    "email": "a@gmail.com",
    "createdAt": "2020-06-15T19:59:58.000Z",
    "updatedAt": "2020-06-15T19:59:58.000Z",
    "friends": [
        {
            "first_name": "Sarah",
            "last_name": "Smith",
            "chat_name": "Sarah.Smith",
            "sentMessages": [
                {
                    "id": 2,
                    "senderId": 2,
                    "recipientId": 1,
                    "type": "text",
                    "content": "Hi",
                    "createdAt": "2020-06-15T19:59:58.000Z",
                    "updatedAt": "2020-06-15T19:59:58.000Z"
                }
            ],
            "receivedMessages": [
                {
                    "id": 1,
                    "senderId": 1,
                    "recipientId": 2,
                    "type": "text",
                    "content": "Hello",
                    "createdAt": "2020-06-15T19:59:58.000Z",
                    "updatedAt": "2020-06-15T19:59:58.000Z"
                }
            ]
        }
    ]
}

I'm not sure if my relationships are wrong, my querying is wrong or it's just not possible to do but over the last week I've searched the sequelize docs, stackoverflow and everything in between and haven't found a solution. It seems like if I could include an OR statement on the LEFT OUTER JOIN which is occurring, then I could have my messages include both sent and received where the senderId or recipientId is my user.

Sequelize include with an OR statement on the join

0 Answers