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 dataShare 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
1 Answer
Reset to default 7Use 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(...)