CODEWITHSUNDEEP
mysqlnode.jsbookshelf.js
kaustubhb
kaustubhb

Reputation: 419

Bookshelf.js - How to get a column from 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'm trying to add additional information to the join table. Basically, users have a monthly quota of questions they can ask per course. So i've added a quota column to the User_Course join table. I'm 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 that has the additional quota column:

**User_Course:**
userId: int
courseId: int
quota: int

I want to be able to decrement my quota value. I'm able to update it using updatePivot, but I'm unable to simply get the value thats in 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'm updating the quota value to 1 every time here. I'd like to programmatically determine what the current quota value is, and then decrement it.

Upvotes: 3

Views: 6548

Answers (2)

uglycode
uglycode

Reputation: 3082

Try withPivot. For example, return this.belongsToMany(Comment).withPivot(['created_at', 'order']); you can place here your quota and you'll get it when loading the relation.

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 define that table name as an object, with the property table_name and function as a value, that has a query builder (qb) and you can query that table separately.

Hope this helps!

Upvotes: 8

kaustubhb
kaustubhb

Reputation: 419

Until I learn a more appropriate way to do this, I'm using knex.raw to use a mysql statement to decrement the quota column. In my controller I call decrementQuota and pass in the userId and courseId:

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

In my user model is where the knex.raw call updates the User_Course by decrementing the value of quota.

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

        });
    },
})

Upvotes: 0

Related Questions