Bookshelf: Many to many is working partially

[Oxyrus]

Many to many is working partially

Introduction

I'm trying to establish a many-to-many relationship between the models User and Group using a join table users_groups, I can fetch the users from a group with no problem whatsoever, however, I can't fetch the groups of a user.

Issue Description

I've defined two models:

// User model
const User = bookshelf.Model.extend({
  tableName: 'users',
  hasTimestamps: true,

  groups() {
    return this.belongsToMany('Group', 'users_groups', 'group_id', 'user_id');
  },
  ...

module.exports.User = bookshelf.model('User', User); // to use registry

// Group model
const Group = bookshelf.Model.extend({
  tableName: 'groups',
  hasTimestamps: true,

  users() {
    return this.belongsToMany('User', 'users_groups', 'group_id', 'user_id');
  },
}
...
module.exports.Group = bookshelf.model('Group', Group);

This query gives me the users of a group just fine:

Group.forge({ id: groupid }).fetch({ withRelated: ['users'] })

{
    "id": 3,
    "name": "Chingu",
    "created_at": "2018-01-02T18:43:50.743Z",
    "updated_at": "2018-01-02T18:43:50.743Z",
    "users": [
        {
            "id": 1,
            "username": "oxyrus",
            "firstName": "Andres",
            "lastName": "Perez",
            "email": "[email protected]",
            "created_at": "2018-01-02T16:43:16.247Z",
            "updated_at": "2018-01-02T16:43:16.247Z",
            "_pivot_group_id": 3,
            "_pivot_user_id": 1
        }
    ]
}

However, I can't get the groups starting from the user, i.e. this fails:

User.forge({ username }).fetch({ withRelated: ['groups'] })

{
    "username": "oxyrus",
    "id": 1,
    "firstName": "Andres",
    "lastName": "Perez",
    "email": "[email protected]",
    "created_at": "2018-01-02T16:43:16.247Z",
    "updated_at": "2018-01-02T16:43:16.247Z",
    "groups": []
}

This is how I'm currently attaching the models (just in case that might be helpful):

Group.forge(data).save()
        .then((group) => {
          // user is an instance I found earlier
          user.groups().attach(group);
          res.json(group);
        })
        .catch((err) => {
          res.json(err);
        });

This creates a row in the users_groups table just fine:

select * from users_groups;
user_id | group_id 
---------+----------
       1 |        3
(1 row)

Here are the logs:

{ method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ 'oxyrus', 1 ],
  __knexQueryUid: 'f39bddee-a104-4bc0-acc0-9898a4ea5b2d',
  toNative: [Function: toNative],
  sql: 'select "users".* from "users" where "users"."username" = ? limit ?' }
{ method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ 1 ],
  __knexQueryUid: 'c2223735-21c3-4e26-ae1d-81d66c4e80af',
  toNative: [Function: toNative],
  sql: 'select "groups".*, "users_groups"."group_id" as "_pivot_group_id", "users_groups"."user_id" as "_pivot_user_id" from "groups" inner join "users_groups" on "users_groups"."user_id" = "groups"."id" where "users_groups"."group_id" in (?)' }
ModelBase {
  attributes: 
   { username: 'oxyrus',
     id: 1,
     firstName: 'Andres',
     lastName: 'Perez',
     email: '[email protected]',
     created_at: 2018-01-02T16:43:16.247Z,
     updated_at: 2018-01-02T16:43:16.247Z },
  _previousAttributes: 
   { username: 'oxyrus',
     id: 1,
     firstName: 'Andres',
     lastName: 'Perez',
     email: '[email protected]',
     created_at: 2018-01-02T16:43:16.247Z,
     updated_at: 2018-01-02T16:43:16.247Z },
  changed: {},
  relations: 
   { groups: 
      CollectionBase {
        model: [Object],
        length: 0,
        models: [],
        _byId: {},
        relatedData: [Object],
        attach: [Function: attach],
        detach: [Function: detach],
        updatePivot: [Function: updatePivot],
        withPivot: [Function: withPivot],
        _handler: [Function],
        _processPivot: [Function],
        _processPlainPivot: [Function],
        _processModelPivot: [Function] } },
  cid: 'c1',
  _knex: null,
  id: 1 }

Expected behaviour

The relationship should work both ways.

Actual behaviour

The relationship only works when fetching from the Group model.

[ricardograca]

You are defining both relations in the exact same way, but they are not the exact same. Try removing the foreignKey and otherKey arguments to your belongsToMany() calls since they are wrong in the User model and you seem to be using the default names so Bookshelf should pick up the correct keys for you:

// ...
users() {
  return this.belongsToMany('User', 'users_groups');
}

// and

groups() {
  return this.belongsToMany('Group', 'users_groups');
}

[Oxyrus]

Thank you so much @ricardograca !

[ricardograca]

It's @ricardograca.... the other Ricardo must be getting tired of getting bombarded with mentions meant for me :rofl:

[Oxyrus]

Oops, blame GitHub's autocompletion! 🤣