Bookshelf.js - How to get a column from a join table?

I have a user model and a course model and they have a many-to-many relationship, so basically users can join multiple courses and courses have many users (students). I have a join table and I am trying to add more information to the join table. Basically, users have a monthly quota of questions they can ask per course. So I added a quota column to the User_Course join table. I am having trouble accessing the quota column. Below is the relevant code to give an idea.

var User = DB.Model.extend({
    Courses: function() {
        return this.belongsToMany('Course', 'User_Course', 'userId', 'courseId');

var Course = DB.Model.extend({
    Users: function() {
        return this.belongsToMany('User', 'User_Course', 'courseId', 'userId');


And User_Course is my join table with an extra quota column:

userId: int
courseId: int
quota: int


I want to decrease the value of the quota. I can update it using updatePivot, but I can't just get the value given in the quota. Here is the code I'm using to update the value:

var userPromise = new User.User({userId: userId}).fetch({withRelated: ['Courses']});
userPromise.then(function(_user) {
    _user.related('enrolledCourses').updatePivot({courseId:courseId, quota:1}).then(function() {



As you can see, I am updating the quota value to 1 every time here. I would like to programmatically determine what the current quota value is and then decrease it.


source to share

2 answers

Try it withPivot

. For example, return this.belongsToMany(Comment).withPivot(['created_at', 'order']);

you can place your quota here and you will get it when you load a relationship.


OK, here's an example:

    new Town().where({
            town_number: 2301
            columns: ['town_title', 'id'],
            withRelated: [{
                'address': function(qb) {
          'addresses.town_id', 'addresses.participant_id');
                    // here you could simply do qb.where({something_id: 1}); as well
        }).then(function(result) {


So basically, instead of just saying withRelated: ['table_name']

, you are defining this table name as an object, and the property table_name

and function

has the query constructor ( qb

) as the value , and you can query that table separately.

Hope this helps!



Until I figure out a more suitable way to do this, I use knex.raw to use a mysql statement to decrease the quota column. In my controller, I call decmentQuota and pass in userId and courseId:

function decrementQuota(userId, courseId) {
    new User.User({userId: userId}).decrementQuota(userId, courseId);


My user model uses code where the call to knex.raw updates the User_Course, decreasing the quota value.

var User = DB.Model.extend({
    decrementQuota: function(uid, cid) {
        DB.knex.raw('update User_Course set quota = quota -1 where userId=' + uid + ' and courseId=' + cid).then(function(quota) {





All Articles