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.
source to share
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!
source to share
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) {
});
},
})
source to share