Creating a 'many-to-many' relationship

Question

I have three tables: Schools (id, school_name), Schools_Focuses (id, school_id, focus_id) and Focuses (id, focus) and want to create a method on my Schools model that returns all related Focuses.

I can perform what I want to do with this SQL QUERY:

SELECT focus FROM focuses INNER JOIN schools_focuses ON focuses.id = schools_focuses.focus_id INNER JOIN schools ON schools.id = schools_focuses.school_id WHERE schools.id = 36;

Model code:

// Define Models
exports.School = School = Bookshelf.PG.Model.extend({
  tableName: 'schools',

  focuses: function() {
    return this.hasMany(Focus).through(Schools_Focuses);
  }
});

Error:

Possibly unhandled Error: column focuses.schools_focuse_id does not exist, sql: select "focuses".*, "schools_focuses"."id" as "_pivot_id", "schools_focuses"."school_id" as "_pivot_school_id" from "focuses" inner join "schools_focuses" on "schools_focuses"."id" = "focuses"."schools_focuse_id" where "schools_focuses"."school_id" in (?)

I don't want to have this extra column (focuses.schools_focuse_id) in Focuses because a focus can belongTo more than one school.

How can I correctly set this up? I have played around with the foreign keys and other keys for hasMany() and through() but no luck.

Thanks!

Answer 1

Sounds like you need to use belongsToMany() instead of hasMany().

// Define Models
exports.School = School = Bookshelf.PG.Model.extend({
  tableName: 'schools',

  focuses: function() {
    return this.belongsToMany(Focus, 'schools_focuses');
  }
});

The second parameter is needed since the join table isn't in alpha order ('focuses_schools'). And if it has problems identifying the join keys (school_id, focus_id) you can override those as parameters as well. Also, since it uses the join table internally, you don't need to create a separate model for Schools_Focuses.