include when adding model.

[lilling]

I have a user:

@Table
export class User extends Model<User> {
    @Column({ primaryKey: true, autoIncrement: true, type: DataType.INTEGER, allowNull: false })
    user_id: number;

    @ForeignKey(() => Role)
    @Column({ type: DataType.INTEGER, allowNull: false })
    role_id: number;

    @BelongsTo(() => Role)
    role: Role;

    @Length({ min: 2, msg: 'user name must be at least 2 characters' })
    @Column({ unique: true, type: DataType.STRING, allowNull: false })
    username: string;

    @Column(DataType.STRING)
    firstname: string;

    @Column(DataType.STRING)
    lastname: string;

    @CreatedAt
    @Column({ type: DataType.DATE })
    created_at: Date;

    @UpdatedAt
    @Column({ type: DataType.DATE })
    updated_at: Date;
 }

how do I include the role model to the user when adding new user? ( the role is already in the DB.)

[RobinBuschmann]

There are multiple ways to achieve this.

You could add the foreign key when creating a new user:

const user = new User({firstname: 'bob', role_id: 1234, ...});
const user = User.build({firstname: 'bob', role_id: 1234, ...});
User.create({firstname: 'bob', role_id: 1234, ...}).then(...);

or could add a role model instance afterwards:

Role
  .findOne({where: ...})
  .then(role => {
    user.$add('role', admin);
  });

Most of this can be found in the sequelize docs: http://docs.sequelizejs.com/

[lilling]

this is the addUser function

addUser = (req: Request, res: Response, next: NextFunction) => {
        const user = new User({
            role_id: req.body.role_id,
            username: req.body.username,
            firstname: req.body.firstname,
            lastname: req.body.lastname
        });

        return user.validate().then(ValidationErr => {
            if (ValidationErr) {
                res.status(400).send(Utils.BuildSequelizeError(ValidationErr));
            }
            return user.save()
              .then(dbUser => 
                res.status(201).send(dbUser))
              .catch(saveErr =>
                res.status(400).send(Utils.BuildSequelizeError(saveErr)))
        });
    };

dbUser hasn't got role

[RobinBuschmann]

@lilling So, you mean that the role_id value isn't stored into the database?

[lilling]

@RobinBuschmann the role id is stored. if I call get all users and include Role. it will get all users with its role

[RobinBuschmann]

@lilling So, it is working now?

[lilling]

no the role_id is stored but I want to get the entire Role object

[RobinBuschmann]

@lilling If you want to extract users including its roles, you need to specify the includes like: User.findAll({include: [Role]})

[lilling]

there no option to add a user and get its role immediately?

[RobinBuschmann]

You want to save the model (write operation) and retrieve (read operation) its related models at once? Unfortunately this is not possible. You need to do it separately:

user
  .save()
  .then(dbUser => User.findById(dbUser.user_id, {include: [Role]}))

[lilling]

thanks

[lilling]

Hey sorry for reopening but its very related.

I have a third model Permission witch is linked to role in many to many relationship.

this is the Role:

import { Table, Column, Model, HasMany, DataType, Default, BelongsToMany, CreatedAt, UpdatedAt, Length } from 'sequelize-typescript';
//
import { RoleStatus } from './role-status.type';
import { Permission } from './permission';
import { RolePermission } from './role-permission';
import { User } from './user';

@Table
export class Role extends Model<Role> {

  @Column({ primaryKey: true, autoIncrement: true, type: DataType.INTEGER, allowNull: false })
  role_id: number;

  @Length({ min: 2, msg: 'role name must be at least 2 characters' })
  @Column({ type: DataType.STRING, allowNull: false })
  role_name: string;

  @Default('ACTIVE')
  @Column(DataType.ENUM('ACTIVE', 'DELETED'))
  role_status: RoleStatus;

  @Column(DataType.STRING)
  description: string;

  @CreatedAt
  @Column({ type: DataType.DATE, allowNull: false })
  created_at: Date;

  @UpdatedAt
  @Column({ type: DataType.DATE, allowNull: false })
  updated_at: Date;

  @BelongsToMany(() => Permission, () => RolePermission)
  permissions: Permission[];

  @HasMany(() => User)
  users: User[];
}

and this is the Permission:

import { Table, Column, Model, HasMany, DataType, Default, BelongsToMany } from 'sequelize-typescript';
//
import { RolePermission } from './role-permission';
import { Role } from './role';

@Table
export class Permission extends Model<Permission> {

  @Column({primaryKey: true, autoIncrement: true, type: DataType.INTEGER, allowNull: false})
  permission_id: number;

  @Column({unique: true, type: DataType.STRING, allowNull: false})
  permission_name: string;

  @Default(false)
  @Column(DataType.BOOLEAN)
  is_default: boolean;

  @Column(DataType.INTEGER)
  child_of: number;

  @Column(DataType.STRING)
  description: string;

  @BelongsToMany(() => Role, () => RolePermission)
  roles: Role[];
}

I have RolePermission to map between them.

Now for adding a new Role with couple of permissions. the role is stored and RolePermission table also has the role id to permission id stored. but when I want to read the new role with it's permissions' the permissions returns an empty array.

this is the function for adding a role:

addRole(req: RgRequest<AddUpdateRoleRequest>, res: Response, next: NextFunction) {
        const findPermissionsOptions = {
            where: {
                permission_id: { $in: req.body.permissions }
            }
        };
        return Permission.findAll<Permission>(findPermissionsOptions).then(permissions => {
            const newRole = new Role({
                role_name: req.body.role_name,
                description: req.body.description,
                permissions: permissions
            });

            return newRole.save()
                .then(dbRole => {
                    permissions.forEach(permission => {
                        dbRole.$add('Permission', permission)
                        permission.$add('Role', dbRole)
                    });
                    return Role.findById(dbRole.role_id, { include: [Permission] }).then(roleWithPermissions =>
                        res.status(201).send(roleWithPermissions));
                })
                .catch(error => res.status(400).send(Utils.BuildSequelizeError(error)));
        });
    }

[RobinBuschmann]

Hey @lilling.

$add returns a promise. So it is an async process, which is not considered in your code. Furthermore it is not necessary to add each model vice versa. One of dbRole.$add('Permission', permission), permission.$add('Role', dbRole) would be enough. Additionally there is another option: You also can add all permissions at once:

dbRole.$add('permissions', permissions)

Putting it all together:

return newRole
  .save()
  .then(dbRole =>
    dbRole
      .$add('permissions', permissions)
      .then(() => Role.findById(dbRole.role_id, { include: [Permission] }))
      .then(roleWithPermissions => res.status(201).send(roleWithPermissions))
  )
  .catch(error => res.status(400).send(Utils.BuildSequelizeError(error)));

[lilling]

so the reason why I didn't receive the permission list is because I didn't saw $add as a promise?

[RobinBuschmann]

@lilling yeah, that's the problem