postgresql - Eager Loading: How to include with two foreign keys

Question

Issue:

I have two tables "Transactions" and "Users" that have two OneToMany relationships with two foreign keys "senderId" and "recipientId". While getting the transaction history, I am trying to get the profile picture of the user that is "not me"(the user requesting transaction history).

an example would be: if a user with id 1 is a sender and a user with id 2 is a recipient, then when a user with id 1 is requesting transaction history I need to include the profile picture of the user with id 2 to that specific transaction.

User model assocciation:

    User.hasMany(models.Transactions, {
      as: 'senders',
      foreignKey: 'senderId',
    });

    User.hasMany(models.Transactions, {
      as: 'recipients',
      foreignKey: 'recipientId',
    });

Transaction model assocciation:

    Transactions.belongsTo(models.User, {
      as: 'sender',
      foreignKey: 'senderId',
    });

    Transactions.belongsTo(models.User, {
      as: 'recipient',
      foreignKey: 'recipientId',
    });

Current Implementation

await models.Transactions.findAndCountAll({
      where: {
        [Op.or]: [{ senderId: id }, { recipientId: id }],
      },
      offset,
      limit,
      include: [
        {
          model: models.User,
          as: 'sender',
          attributes: ['profilePic'],
        },
        {
          model: models.User,
          as: 'recipient',
          attributes: ['profilePic'],
        },
      ],
    });

Current result

    {
      "id": 2,
      "senderId": 1,
      "recipientId": 2,
      "createdAt": "2021-01-29T08:45:41.319Z",
      "updatedAt": "2021-01-29T08:45:41.319Z",
      "sender": {
        "profilePic": "profile picture url"
      },
      "recipient": {
        "profilePic": null
      }
    }

Desired result

    {
      "id": 2,
      "senderId": 1,
      "recipientId": 2,
      "createdAt": "2021-01-29T08:45:41.319Z",
      "updatedAt": "2021-01-29T08:45:41.319Z",
      "profilePic": "non requested user profile picture url"
    }

Questions: How can I avoid including the profile pictures of both the sender and recipient with eager loading and only get the profile picture of the other user.

Thanks

question from:https://stackoverflow.com/questions/65951830/eager-loading-how-to-include-with-two-foreign-keys

Answer 1

Waitting for answers