Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Outer join of 2 tables in Bookshelf.js or Knex.js

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
like image 812
user3147531 Avatar asked Dec 30 '13 20:12

user3147531


People also ask

What is Bookshelf js?

Bookshelf is a JavaScript ORM for Node. js, built on the Knex SQL query builder. It features both Promise-based and traditional callback interfaces, transaction support, eager/nested-eager relation loading, polymorphic associations, and support for one-to-one, one-to-many, and many-to-many relations.

Is KNEX js an ORM?

Sequelize is an ORM that includes some query builder stuff; Knex is just a query builder, not an ORM.

What is KNEX in node js?

js (pronounced /kəˈnɛks/) is a "batteries included" SQL query builder for PostgreSQL, CockroachDB, MSSQL, MySQL, MariaDB, SQLite3, Better-SQLite3, Oracle, and Amazon Redshift designed to be flexible, portable, and fun to use.


1 Answers

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: 'comps'}).then(...)

Where comps 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(...)
like image 177
user2847643 Avatar answered Oct 16 '22 18:10

user2847643