Mitchell Loeppky Mitchell Loeppky - 4 months ago 52
Node.js Question

Bookshelf.js, query on related table

I have a model similar to the following:

var ScholarlyPaper = Bookshelf.Model.extend({

tableName: 'papers',

paragraphs: function() {
return this.hasMany(Paragraph).through(Section);
},

sections: function() {
return this.hasMany(Section);
}

});

var Section = Bookshelf.Model.extend({

tableName: 'sections',

paragraphs: function() {
return this.hasMany(Paragraph);
}

scholarlyPaper: function() {
return this.belongsTo(ScholarlyPaper);
}

});

var Paragraph = Bookshelf.Model.extend({

tableName: 'paragraphs',

section: function() {
return this.belongsTo(Section);
},

scholarlyPaper: function() {
return this.belongsTo(ScholarlyPaper).through(Section);
},

author: function() {
return this.belongsTo(Author);
}

});

var Author = Bookshelf.Model.extend({

tableName: 'authors',

paragraphs: function() {
return this.hasMany(Paragraph);
}

});


Using Bookshelf.js, given a scholarlyPaper id and author id, how can I get all of the sections in the paper that the author did not write a single paragraph in?

The particular challenge I am facing is that there is no way that I am aware of to add a where clause on a related table (e.g 'where paragraphs.author_id != author_id).

Answer
function(authorId, paperId, success, failure) {
  new ScholarlyPaper({id: paperId}).load({sections: function(qb) {
    qb.whereNotExists(function() {
      this.from('paragraph')
        .whereRaw('paragraph.section = section.id')
        .where('paragraph.author_id', '=', authorId);
    });
  }}).then(function(paper) {
    success(paper.related('section'));
  }, failure);
};
Comments