bookshelf: .through() does not work as expected

Here are my models:

// models/Org.js
var Org = bookshelf.Model.extend({
  tableName: 'orgs',
  admins: function () {
    return this.hasMany('User').through('Admin')
  }
})

module.exports = bookshelf.model('Org', Org)
// models/User.js
var User = bookshelf.Model.extend({
  tableName: 'users',
  org: function () {
    return this.belongsTo('Org').through('Admin', 'id')
  }
})

module.exports = bookshelf.model('User', User)
// models/Admin.js
var Admin = bookshelf.Model.extend({
  tableName: 'admins'
})

module.exports = bookshelf.model('Admin', Admin)

The admins table referenced in the models/Admin.js model has a user_id column and an org_id column. When I invoke Org.forge({ id: 1 }).fetch({ withRelated: ['admins'] }), my expectation is that bookshelf will do the following:

  • query the admins table to find all rows with a value of 1 in the org_id field
  • query the users table to find all users with ids found in the user_id column of the rows found in the admins table
  • return all of rows found in the users table when I perform `org.related(‘admins’).

This doesn’t seem to be the case, however; I get the following error message when I try the fetch statement above:

Error: select “users”.*, “admins”.“id” as “_pivot_id”, “admins”.“farm_id” as “_pivot_farm_id” from “users” inner join “admins” on “admins”.“id” = “users”.“admin_id” where “admins”.“farm_id” in (1) - SQLITE_ERROR: no such column: users.admin_id

This is puzzling; why would it try to find admin_id inside the users table to begin with? Then I tried making the following change to the Org model:

admins: function () {
  return this.hasMany('User').through('Admin', 'id')
}

This does not result in a formal error, but the resulting admins array is incorrectly populated. It returns a user model whose id is the same as the org I am fetching instead of the user_id in the admins table. Simply put, my org’s id is 1, and its admin (as per the only row in the admins table) should be user with id 2, but the user that gets put into my org.admins array is the user with id 1.

Adding a third argument to the .through() method has no effect (as documented in issue #673) on the above results. Is this a bug, or am I doing something wrong?

Thanks!

About this issue

  • Original URL
  • State: open
  • Created 9 years ago
  • Comments: 27 (12 by maintainers)

Most upvoted comments

Hey guys, firstly I’d like to apologise for the horrid wording on the .through() docs - as I’m reading it even I am confused. Let’s leave this issue open to clean up the docs there.

So, just to clarify the use of language here:

This = Model.extend({
  targets: function () { this.hasMany(Target).through(Interim, throughForeignKey, otherKey); }
});

Now, the throughForeignKey is the column that is used to join Interim to This.

Our tables look exactly like this, essentially:

@agarzola, I’m going to show you the Model definitions one would use with the diagram you’ve provided.

Bookshelf doesn’t support models without a primary key, so actor_film_mapping wouldn’t typically attract a Model. You can just use belongsToMany.

bookshelf.model('Actor', Model.extend({
  tableName: 'actor',
  idAttribute: 'actor_id',
  films: function() { return this.belongsToMany('Film', 'actor_film_mapping'); }
});

bookshelf.model('Film', Model.extend({
  tableName: 'film',
  idAttribute: 'film_id',
  actors: function() { return this.belongsToMany('Actor', 'actor_film_mapping'); }
});

However, if you do want to create a join model using through, you should be able to do the following:

bookshelf.model('Casting', Model.extend({
  idAttribute: ['actor_id', 'film_id'], // Composite keys not actually supported, but you need something I guess...
  tableName: 'actor_film_mapping',
  actor: function() { return this.hasOne('Actor'); }
  film: function() { return this.hasOne('Film'); }
});

bookshelf.model('Actor', Model.extend({
  tableName: 'actor',
  idAttribute: 'actor_id',
  casting: function() { return this.hasMany('Casting'); }
  films: function() { return this.belongsToMany('Film').through('Casting'); }
});

bookshelf.model('Film', Model.extend({
  tableName: 'film',
  idAttribute: 'film_id',
  casting: function() { return this.hasMany('Casting'); }
  films: function() { return this.belongsToMany('Actor').through('Casting'); }
});