Sequelize how to make a join request?

Question

I'm trying to make joined queries with Sequelize.

That's my db :

What I need is to select all of my relations and get this kind of result:

[
    {
        id: 1,
        State: true,
        FK_User: {
            id: 2,
            Name: "my name"
        },
        FK_Team: {
            id: 3,
            Name: "team name"
        }
    },
    ...
]

But today I've got this result:

[
    {
        id: 1,
        State: true,
        FK_User: 2,
        FK_Team: 3
    },
    ...
]

For each of my relations, I've go to do another request to get datas ...
So I putted a look in this Stack and in the doc.
Then I made this code :

let User = this.instance.define("User", {
  Name: {
    type: this.libraries.orm.STRING,
    allowNull: false
  }
});

let Team = this.instance.define("Team", {
  Name: {
    type: this.libraries.orm.STRING,
    allowNull: false
  }
});

let Relation = this.instance.define("Relation", {
  State: {
    type: this.libraries.orm.BOOLEAN,
    allowNull: false,
    defaultValue: 0
  }
});

Relation.hasOne(User, {as: "FK_User", foreignKey: "id"});
Relation.hasOne(Team, {as: "FK_Team", foreignKey: "id"});

With this code, I haven't got any relation between tables... So I added theses two lines. I don't understand why I need to make a two direction relation, because I don't need to access Relation From User and Team ...

User.belongsTo(Relation, {foreignKey: 'FK_User_id'});
Team.belongsTo(Relation, {foreignKey: 'FK_Team_id'});

When I do that, I've a FK_User_id in the User table and a FK_Team_id in the Team table ... I don't know how to make this simple relation and get all I need with my futur request and the include: [User, Team]} line.

score 0 · Answer 1 · answered Sep 09 '19 at 13:11

0

User.hasOne(Relation);
Team.hasOne(Relation);
Relation.belongsTo(Team);
Relation.belongsTo(User);

This code seems to work.
I don't know why ...

answered Sep 09 '19 at 13:11

Antoine Duval

342
3
20

score 0 · Answer 2 · answered Sep 09 '19 at 13:13

0

Here your associations are setup correctly you can join it with include :

Relation.findAll({
    where : {
        state : true
    }
    include:[
        {
            model : User
        },
        {
            model : Team
        }
    ]
})

answered Sep 09 '19 at 13:13

Vivek Doshi

56,649
12
110
122

Sequelize how to make a join request?

2 Answers2