I have a Users table and a Groups table, when I retrieve a single user, I want to get a list of all the groups that user belongs to. I created a joins table named GroupUsers and it has userId and groupId on it, this is how I know that a user belongs to a group. Now, when I try to get the list of groups a user belongs to using the sequelize include on find, it only returns the first match, if the user belongs to various groups. How do I solve this?

Users controller

return models.Users
.find({
  include: [{
    model: models.Groups,
    as: 'groups',
    limit: null,
    required: false
  }],
  where: { username }
})

Returns this:

{
    "id": 1,
    "username": "John",
    "phone": "xxxxxxxx",
    "email": "[email protected]",
    "createdAt": "2017-07-16T20:14:04.744Z",
    "updatedAt": "2017-07-16T20:14:04.744Z",
    "groups": [
        {
            "id": 1,
            "name": "Test Group",
            "type": "Public",
            "createdAt": "2017-07-16T20:13:18.392Z",
            "updatedAt": "2017-07-16T20:13:18.392Z",
            "GroupUsers": {
                "userId": 1,
                "groupId": 1,
                "last_seen": null,
                "createdAt": "2017-07-16T20:14:27.903Z",
                "updatedAt": "2017-07-16T20:14:27.903Z"
            }
        }
    ]
}

Instead of this:

    {
        "id": 1,
        "username": "John",
        "phone": "xxxxxxxx",
        "email": "[email protected]",
        "createdAt": "2017-07-16T20:14:04.744Z",
        "updatedAt": "2017-07-16T20:14:04.744Z",
        "groups": [
            {
                "id": 1,
                "name": "Test Group",
                "type": "Public",
                "createdAt": "2017-07-16T20:13:18.392Z",
                "updatedAt": "2017-07-16T20:13:18.392Z",
                "GroupUsers": {
                    "userId": 1,
                    "groupId": 1,
                    "last_seen": null,
                    "createdAt": "2017-07-16T20:14:27.903Z",
                    "updatedAt": "2017-07-16T20:14:27.903Z"
                }
            },
            {
                "id": 2,
                "name": "Test Group 2",
                "type": "Public",
                "createdAt": "2017-07-16T20:13:18.392Z",
                "updatedAt": "2017-07-16T20:13:18.392Z",
                "GroupUsers": {
                    "userId": 1,
                    "groupId": 2,
                    "last_seen": null,
                    "createdAt": "2017-07-16T20:14:27.903Z",
                    "updatedAt": "2017-07-16T20:14:27.903Z"
                }
            }
        ]
    }

I'm sure I'm doing something wrong somewhere I just don't know where, that same thing may also be the cause of sequelize including the joins table in the result: GroupUsers

I have a Users table and a Groups table, when I retrieve a single user, I want to get a list of all the groups that user belongs to. I created a joins table named GroupUsers and it has userId and groupId on it, this is how I know that a user belongs to a group. Now, when I try to get the list of groups a user belongs to using the sequelize include on find, it only returns the first match, if the user belongs to various groups. How do I solve this?

Users controller

return models.Users
.find({
  include: [{
    model: models.Groups,
    as: 'groups',
    limit: null,
    required: false
  }],
  where: { username }
})

Returns this:

{
    "id": 1,
    "username": "John",
    "phone": "xxxxxxxx",
    "email": "[email protected]",
    "createdAt": "2017-07-16T20:14:04.744Z",
    "updatedAt": "2017-07-16T20:14:04.744Z",
    "groups": [
        {
            "id": 1,
            "name": "Test Group",
            "type": "Public",
            "createdAt": "2017-07-16T20:13:18.392Z",
            "updatedAt": "2017-07-16T20:13:18.392Z",
            "GroupUsers": {
                "userId": 1,
                "groupId": 1,
                "last_seen": null,
                "createdAt": "2017-07-16T20:14:27.903Z",
                "updatedAt": "2017-07-16T20:14:27.903Z"
            }
        }
    ]
}

Instead of this:

    {
        "id": 1,
        "username": "John",
        "phone": "xxxxxxxx",
        "email": "[email protected]",
        "createdAt": "2017-07-16T20:14:04.744Z",
        "updatedAt": "2017-07-16T20:14:04.744Z",
        "groups": [
            {
                "id": 1,
                "name": "Test Group",
                "type": "Public",
                "createdAt": "2017-07-16T20:13:18.392Z",
                "updatedAt": "2017-07-16T20:13:18.392Z",
                "GroupUsers": {
                    "userId": 1,
                    "groupId": 1,
                    "last_seen": null,
                    "createdAt": "2017-07-16T20:14:27.903Z",
                    "updatedAt": "2017-07-16T20:14:27.903Z"
                }
            },
            {
                "id": 2,
                "name": "Test Group 2",
                "type": "Public",
                "createdAt": "2017-07-16T20:13:18.392Z",
                "updatedAt": "2017-07-16T20:13:18.392Z",
                "GroupUsers": {
                    "userId": 1,
                    "groupId": 2,
                    "last_seen": null,
                    "createdAt": "2017-07-16T20:14:27.903Z",
                    "updatedAt": "2017-07-16T20:14:27.903Z"
                }
            }
        ]
    }

I'm sure I'm doing something wrong somewhere I just don't know where, that same thing may also be the cause of sequelize including the joins table in the result: GroupUsers

• javascript
• join
• sequelize.js
• postgresql-9.4

Share Improve this question Follow edited Sep 2, 2022 at 12:43 Riza Khan 3,17055 gold badges2828 silver badges6363 bronze badges asked Jul 17, 2017 at 10:11 THEozmicTHEozmic 5111 silver badge77 bronze badges

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 3

It appears that in my associations, I did:

Users.belongsToMany(models.Groups, {
  through: 'GroupUsers',
  as: 'groups',
  foreignKey: 'groupId'
});

Instead of :

Users.belongsToMany(models.Groups, {
  through: 'GroupUsers',
  as: 'groups',
  foreignKey: 'userId'
});

Note the foreignKey attribute

And as for the GroupUsers object that is also returned, I removed that by doing:

include: [{
    model: models.Groups,
    as: 'groups',
    attributes: ['id', 'name'],
    through: { attributes: [] }
  }]

Note the through key which has attributes set to an empty array.