Sequelize join table not retuning additional field

Question

I'm testing Sequelize and I'm liking it so far. Now I've come across a problem that I could not solve and not find any answer from the web.

So I've these three tables which are created through migrations

Users:

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      .
      . // All user data here
      .
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Users');
  }
};

Property:

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Properties', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      .
      . // All property data here
      .
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Properties');
  }
};

And PropertyUser:

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('PropertyUsers', {
      property_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        primaryKey: true,
        references: {
          model: 'Properties',
          key: 'id'
        }
      },
      user_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        primaryKey: true,
        references: {
          model: 'Users',
          key: 'id'
        }
      },
      role: {
        type: Sequelize.ENUM('admin', 'basic', 'read'),
        allowNull: false,
        defaultValue: 'read'
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('PropertyUsers');
  }
};

So we have Users and Property tables that are connected through PropertyUser table and PropertyUser table hold the information about which role user has in property (Admin, basic, read).

I've also created these models

User:

'use strict';
module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define('User', {
    firstname: DataTypes.STRING,
    .
    .
    .
  }, {});
  User.associate = function(models) {
    // associations can be defined here
    User.belongsToMany(models.Property, {through: 'PropertyUsers', foreignKey: 'user_id', as: 'properties'})
  };
  return User;
};

Property:

'use strict';
module.exports = (sequelize, DataTypes) => {
  const Property = sequelize.define('Property', {
    .
    .
    .
  }, {});
  Property.associate = function(models) {
    // associations can be defined here
    Property.belongsToMany(models.User, {through: 'PropertyUsers', foreignKey: 'property_id', as: 'users'})
  };
  return Property;
};

PropertyUser:

'use strict';
module.exports = (sequelize, DataTypes) => {
  const PropertyUser = sequelize.define('PropertyUser', {
    property_id: DataTypes.INTEGER,
    user_id: DataTypes.INTEGER,
    role: DataTypes.ENUM('admin', 'basic', 'read'),
  }, {});
  PropertyUser.associate = function(models) {
    // associations can be defined here
    PropertyUser.belongsTo(models.Property, {foreignKey: 'property_id'});
    PropertyUser.belongsTo(models.User, {foreignKey: 'user_id'});
  };
  PropertyUser.removeAttribute('id');
  return PropertyUser;
};

Now when I try to fetch Property with ID and include users that belong to the property it does return the join table data with every user but the join table data only includes user_id, property_id, createdAt and updatedAt and not the role.

Here is the code I use to fetch the data.

return new Promise(async(resolve, reject) => {
        try {
            const result = await models.Property.findOne({
                where: {
                    id: propertyId
                },
                include: [{
                    model: models.User,
                    as: 'users',
                }]
            });
            resolve(result);
        } catch (e) {
            reject(e);
        }
    });

What I'm I doing wrong? Thanks for the help already!

Edit: It seems that when Sequelize constructs the SQL statement it does not add the role to the statement. Here is a the part of the statement that gets the data from PropertyUser table

`users->PropertyUsers`.`createdAt` AS `users.PropertyUsers.createdAt`, `users->PropertyUsers`.`updatedAt` AS `users.PropertyUsers.updatedAt`, `users->PropertyUsers`.`property_id` AS `users.PropertyUsers.property_id`, `users->PropertyUsers`.`user_id` AS `users.PropertyUsers.user_id`

Answer 1

After few hours of trying different things I was able to track down the problem. I don't know why it did not work with the way it was implemented in my original post but here are the changes.

In User model I changed

User.belongsToMany(models.Property, {through: 'PropertyUsers', foreignKey: 'user_id', as: 'properties'})

to (notice the change in through)

User.belongsToMany(models.Property, {through: models.PropertyUser, foreignKey: 'user_id', as: 'properties'})

and also same in Property model I changed through: 'PropertyUsers' to through: models.PropertyUser

Answer 2

The model PropertyUser will be included with the User model.

That means you will get users with type Array<User & {PropertyUser: PropertyUser}>;

You can access PropertyUser by result.users[0].PropertyUser.role

Here is my implementation in typescript. I am using sequelize-typescript with sequelize.

Clients Table

@Table({
  timestamps: true,
  paranoid  : true,
  tableName : "clients"
})
export class Client extends Model<Client> {

  @Unique
  @PrimaryKey
  @AutoIncrement
  @Column(DataType.BIGINT)
  id: number;

  @Column(DataType.STRING)
  name: string;

  @BelongsToMany(() => User, () => ClientUser)
  users?: Array<User & {ClientUser: ClientUser}>;
}

Users Table

@Table({
  timestamps: true,
  paranoid  : true,
  tableName : "users"
})
export class User extends Model<User> {
  @Unique
  @PrimaryKey
  @AutoIncrement
  @Column(DataType.BIGINT)
  id: number;

  @Column(DataType.STRING)
  first_name: string;

  @Column(DataType.STRING)
  last_name?: string;


  @BelongsToMany(() => Client, () => ClientUser)
  clients?: Array<Client & { ClientUser: ClientUser }>;
}

ClientUser Table

@Table({
  timestamps: true,
  paranoid: false,
  tableName: "client_user"
})
export class ClientUser extends Model<ClientUser> {

  @Unique
  @PrimaryKey
  @AutoIncrement
  @Column(DataType.BIGINT)
  id: number;

  @ForeignKey(() => Client)
  @Column(DataType.BIGINT)
  client_id: number;

  @ForeignKey(() => User)
  @Column(DataType.BIGINT)
  user_id: number;

  @Column(DataType.STRING)
  role: string;

  @BelongsTo(() => Client)
  client: Client;

  @BelongsTo(() => User)
  user: User;
}

Query

const clients = await Client.findAll({
      include: [
        {
          model: User,
          as   : "users"
        }
      ],
    });

    console.log("clients", clients.map(c => {
      return {
        id   : c.id,
        name : c.name,
        users: c.users.map(u => {
          return {
            id        : u.id,
            first_name: u.first_name,
            role      : u.ClientUser.role
          };
        })
      };
    }));

Result