Sequelize (node.js) - Eager Loading a model from additional foreign key in join table

Question

I'm having an issue - I would like to eager load an additional model that is included in the join table for a M:N relationship. I've tried a few things but can't get it to work.

Setup:

const User = sequelize.define('user', {
  id: DataTypes.STRING,
  username: DataTypes.STRING,
  points: DataTypes.INTEGER
}, { timestamps: false });

const Profile = sequelize.define('profile', {
  id: DataTypes.STRING,
  name: DataTypes.STRING
}, { timestamps: false });

const UserProfiles = sequelize.define('user_profile', {
  approved: DataTypes.BOOLEAN
}, { timestamps: false });

User.belongsToMany(Profile, { through: 'User_Profiles' });
Profile.belongsToMany(User, { through: 'User_Profiles' });
UserProfiles.belongsTo(User, { as: 'Approver', foreignKey: { name: "approvedBy" }});

Current Query:

const user = await User.findOne({
  where: { username },
  include: [
    {
      model: Profile
    },
  ],
});

Returns:

{
  id: 'asdf-1234-5678',
  username: 'test',
  points: 10,
  Profiles: [{
    id: '1',
    name: 'testname',
    UserProfiles: {
      approved: true,
      UserId: 'asdf-1234-5678',
      ProfileId: '1',
      approvedBy: 'qwer-7890-uiop'
    }
  }]
}

Note that this query does return the approvedBy field, but I would like it to eager load the associated User [Approver] model

Desired Return:

{
  id: 'asdf-1234-5678',
  username: 'test',
  points: 10,
  Profiles: [{
    name: 'testname',
    UserProfiles: {
      approved: true,
      UserId: 'asdf-1234-5678',
      ProfileId: '1',
      approvedBy: 'qwer-7890-uiop'
      Approver: {                        <-----
        id: 'qwer-7890-uiop',            <-----
        username: 'approver_username',   <-----
        points: 50                       <-----
      }                                  <-----
    }
  }]
}

Thanks!

Answer 1

To accomplish the ideal return with the energetic stacked related Client [Approver] model, you can change your question and incorporate setup. You want to add one more settled incorporate for the Client model, which addresses the "Approver" for this situation.

This is the way you can refresh your inquiry:

const user = await User.findOne({
  where: { username },
  include: [
    {
      model: Profile,
      include: [
        {
          model: UserProfiles,
          include: [
            {
              model: User,
              as: 'Approver', // Alias for the User model representing the approver
              attributes: ['id', 'username', 'points'], // Include only specific attributes you need
            },
          ],
        },
      ],
    },
  ],
});

By adding the settled incorporate for the Client model with the nom de plume 'Approver', you ought to get the ideal return with the related Client [Approver] model data remembered for the outcome.

To enthusiastically stack the related Client [Approver] model in the Sequelize question, you want to utilize the incorporate choice. This choice permits you to determine the connected models that ought to be brought alongside the primary model in a question. For this situation, we need to incorporate the "Profile" model and its related "UserProfiles" through the "User_Profiles" join table.

To accomplish the ideal return, we really want to settle numerous degrees of incorporate for the different related models. In the first place, we start with the Profile model, and afterward we go further to incorporate the UserProfiles model. At this level, we utilize one more settled incorporate to stack the related Client model addressing the approver.

In the deepest incorporate, we determine the Client model with the pseudonym 'Approver', which assists us with separating it from the first Client model. We can likewise utilize the properties choice to choose explicit fields from the "Approver" model that we need to remember for the eventual outcome.

By appropriately settling the incorporate designs, Sequelize will produce a SQL question with the essential joins to productively stack the connected information. The end-product will incorporate the related "Client [Approver]" model data under the "UserProfiles" trait of each "Profile". Along these lines, we can get to the supporting client's subtleties alongside different information in the reaction.