Bookshelf.js - 如何从连接表中获取列？

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.

我有一个用户模型和一个课程模型,他们有多对多的关系,所以基本上用户可以加入多个课程,课程有很多用户(学生)。我有一个连接表,我正在尝试向连接表添加其他信息。基本上,用户每个课程可以提出每月的问题配额。所以我在User_Course连接表中添加了一个配额列。我无法访问配额列。以下是给出想法的相关代码。

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是我的连接表,其中包含额外的配额列:

**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:

我希望能够减少配额值。我可以使用updatePivot更新它,但我无法简单地获得配额中的值。这是我用来更新值的代码:

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.

如您所见,我每次都将配额值更新为1。我想以编程方式确定当前的配额值,然后减少它。

2 个解决方案

#1

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.

尝试使用Pivot。例如,返回this.belongsToMany(Comment).withPivot(['created_at','order']);你可以在这里放置你的配额,你可以在加载关系时得到它。

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.

所以基本上不是简单地说withRelated:['table_name'],而是将表名定义为对象,使用属性table_name和function作为值,它具有查询构建器(qb),您可以单独查询该表。

Hope this helps!

希望这可以帮助!

#2

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:

在我学习更合适的方法之前,我正在使用knex.raw来使用mysql语句来减少配额列。在我的控制器中,我调用decrementQuota并传入userId和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.

在我的用户模型中,knex.raw调用通过递减配额值来更新User_Course。

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

        });
    },
})

#1