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:

**User_Course:**
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() {
        return;
    })

})

      

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.

+3


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.

Update:

OK, here's an example:



    new Town().where({
            town_number: 2301
        }).fetchAll({
            columns: ['town_title', 'id'],
            withRelated: [{
                'address': function(qb) {
                    qb.select('addresses.town_id', 'addresses.participant_id');
                    qb.columns('addresses.street_name');
                    // here you could simply do qb.where({something_id: 1}); as well
                }
            }]
        }).then(function(result) {
            res.json(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!

+7


source


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) {

        });
    },
})

      

0


source







All Articles