Bookshelf.js，相关表的查询

Question

我有一个类似于以下模型的模型：

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);
  }

});

使用Bookshelf.js，给定一篇学术论文的id和作者的id，如何获取作者没有写过任何段落的论文中的所有章节？

我面临的特定挑战是，据我所知，没有办法在相关表上添加where子句（例如'where paragraphs.author_id！= author_id'）。

- Mitchell Loeppky

Answer 1

这个有效吗？

new ScholarlyPaper({id: 1}).load({paragraphs: function(qb) {
  qb.where('paragraphs.author_id', '!=', author_id);
}}).then(function(paper) {
  console.log(JSON.stringify(paper.related('paragraphs')));
});

- tgriesser

---

这不完全是我所需要的，但它帮助我找到了解决方案。所以谢谢你。请看我的答案。 - Mitchell Loeppky

Answer 2

请查看bookshelf-eloquent扩展。其中的whereHas()和with()函数可能是您所需要的。您的函数应该类似于这样：

async function(authorId, paperId) {
    return await ScholarlyPaper.where('id', paperId)
        .with('sections', (q) {
            // Filter out sections in the paper that the author did not write a single paragraph in.
            q.whereHas('paragraphs', (q) => {
                q.where('author_id', authorId);
            }, '<=', 0);
        }).first();
}

- black21jack

Answer 3

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);
};

- Mitchell Loeppky

---

你可能想使用 .where('paragraph.author_id', authorId) 而不是 whereRaw，这样你的参数就会被绑定。 - tgriesser

嗨，我知道这是一个旧的答案。但是当我尝试上面的代码时，我遇到了以下编译错误。有人能帮忙吗？我正在使用TypeScript。参数类型“{ sections: (qb: any) => void; }”无法分配给类型“string | string[]”。对象文字只能指定已知属性，“sections”不存在于类型“string | string[]”中。 - Meiyappan Kannappa