QUERY ERROR sequelize Association LEFT OUTER JOIN API NODEJS

Asked

Viewed 63 times

0

I have the following SQL command: 

select u.name_user, a.type_administrator from users u inner join administrators a on u.id_user = a.id_administrator;

and I need to turn this into my API into Nodejs so I have in the user model:

    static associate(models) {
    this.hasOne(models.Administrator, {foreignKey: 'id_administrator', as: 'administrator'});
    }

in the Administrator model:

static associate(models) {
    this.hasOne(models.User, {foreignKey: 'id_user', as: 'user'})
}

and finally in the Administrator controller I have:

async indexById(req, res) {
    const { id_user } = req.params;

    const user = await User.findByPk(id_user, {
      include: { association: 'administrator'}
    });

    return res.json(user.administrator);
},

the problem is that the result of this is:

SELECT "User"."id_user", "User"."name_user", "administrator"."id_administrator" "administrator"."type_administrator" FROM "users" AS "User" LEFT OUTER JOIN "administrators" AS "administrator" ON "User"."id_user" = "administrator"."id_administrator" WHERE "User"."id_user" = '1';

As you can see, it generates a query with LEFT OUTER JOIN that does not list the user with Administrator and I need to generate an INNER JOIN in order to show the complete information. Someone knows how to fix?

recalling that id_administrator and id_user are the same [PFK]

sql node.js api sequelize-js inner-join

1 answer

0

See if it works like this: 

  async indexById(req, res) {
    const { id_user } = req.params;

    const user = await User.findByPk(id_user, {
      include: { association: 'administrator',required: true}
    });

    return res.json(user.administrator);
},

Browser other questions tagged sql node.js api sequelize-js inner-join

You are not signed in. Login or sign up in order to post.