最新消息:雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客

javascript - Outer join of 2 tables in Bookshelf.js or Knex.js - Stack Overflow

programmeradmin1浏览0评论

I am new to Bookshelf.js and knex. I need to write a query equivalent to this in Bookshelf/ knex

SELECT Inv.*, Comp.* 
FROM  Inv,   Comp
WHERE Inv.uId =2 AND Comp.cId = Inv.cId;

Inv Table has:

Id     |  primary key, integer  not null
col1   | string data
cId    | integer, foreign key references C table
uId    | integer  foreign key reference U table      

Comp Table has:

cId     |  primary key, integer  not null 
col3    | string data

I am new to Bookshelf.js and knex. I need to write a query equivalent to this in Bookshelf/ knex

SELECT Inv.*, Comp.* 
FROM  Inv,   Comp
WHERE Inv.uId =2 AND Comp.cId = Inv.cId;

Inv Table has:

Id     |  primary key, integer  not null
col1   | string data
cId    | integer, foreign key references C table
uId    | integer  foreign key reference U table      

Comp Table has:

cId     |  primary key, integer  not null 
col3    | string data
Share Improve this question edited Dec 30, 2013 at 21:18 Rowland Shaw 38.1k15 gold badges104 silver badges168 bronze badges asked Dec 30, 2013 at 20:52 user3147531user3147531 511 silver badge3 bronze badges
Add a ment  | 

1 Answer 1

Reset to default 7

Use knex query builder. Assuming Invs is a collection for model Inv it may look something like:

Invs.forge().query(function(qb) {
  qb.join('Comp', 'Comp.cId', '=', 'Inv.cId');
  qb.where('Inv.uId', 2);
}).fetch({withRelated: 'ps'}).then(...)

Where ps would be a relation defined in the Inv model. You could also skip bookshelf altogether and use knex directly through bookshelf.knex to form the exact query you need:

bookshelf.knex('Inv')
  .join('Comp', 'Comp.cId', '=', 'Inv.cId')
  .where('Inv.id', 2)
  .select()
  .then(...)
发布评论

评论列表(0)

  1. 暂无评论