how to merge response attributes of other table in node js

Question

I have 3 Tables User, Cars and UserCars

User{id, name, phone, email}
Cars{id, name, manufacturer}
UserCars{id, car_id, user_id, role}

User have many cars(through UserCars)
Cars have many users(through UserCars)

I am using express js


    router.get('/', async (req, res) => {

        try {

            let car = await Car.findOne({
                where: {
                id: req.car_id
            }});

            let users = await car.getUsers({joinTableAttributes: ['role']})
            res.send(users)

        } catch (e) {
            console.log(e)
            res.status(400).send(e)
        }

    })

and this my response

[
    {
        "id": 1,
        "name": "test",
        "email": null,
        "phone": null,
        "createdAt": "2019-07-09T09:38:11.859Z",
        "updatedAt": "2019-07-12T04:34:20.922Z",
        "User_car": {
            "role": "driver"
        }
    }
]

but any idea how to include role in the user object, rather then specifying it separately in User_car table, Is there a way where i can get the below output


[
    {
        "id": 1,
        "name": "test",
        "email": null,
        "phone": null,
        "role": 'driver'
        "createdAt": "2019-07-09T09:38:11.859Z",
        "updatedAt": "2019-07-12T04:34:20.922Z"
    }
]

score 0 · Answer 1 · answered Jul 12 '19 at 16:24

You can use sequelize.literal to get that field when getting your attributes.

attributtes: [
  // define your other fields
  [sequelize.literal('`users->User_car`.`role`'), 'role'],
]

Now, I not sure if that is going to work with car.getUsers. I usually do a single query with include and also define the "join" table so I can know how is name it on sequelize. Let me show you an example.

module.exports = (sequelize, DataTypes) => {
  const UserCar = sequelize.define('UserCar', {
    // id you don't need and id field because this is a N:M relation
    role: {
      type: DataTypes.STRING
    },
    carId: {
      field: 'car_id',
      type: DataTypes.INTEGER
    },
    userId: {
      field: 'user_id',
      type: DataTypes.INTEGER
},
  }, {
    tableName: 'User_car',
    underscored: true,
    createdAt: 'created_at',
    updatedAt: 'updated_at',
  });

  UserCar.associate = (models) => {
    models.user.belongsToMany(models.car, { as: 'cars', through: User_car, foreignKey: 'user_id' });
    models.car.belongsToMany(models.user, { as: 'users',    through: User_car, foreignKey: 'car_id' });
  };

  return UserCar;
};

router.get('/', async (req, res) => {

  try {
    const users = await User.findAll({ 
      include: [{
        model: Car,
        as: 'cars',
        where: { id: req.car_id }
      }],
      attributtes: [
        'id',
        'name',
        'email',
        'phone',
        [sequelize.literal('`cars->User_car`.`role`'), 'role'],
      ]
    })
    res.send(users)

  } catch (e) {
    console.log(e)
    res.status(400).send(e)
  }
});

how to merge response attributes of other table in node js

1 Answers1