Skip to content

Instantly share code, notes, and snippets.

@jordaaash
Last active June 19, 2021 13:03
Show Gist options
  • Save jordaaash/aa376fbe45606681f9ae to your computer and use it in GitHub Desktop.
Save jordaaash/aa376fbe45606681f9ae to your computer and use it in GitHub Desktop.
Bookshelf.js/Knex.js innerJoin withRelated
var Promise = require('bluebird'),
User = require('./user'),
knex, query;
knex = User.prototype._builder(User.prototype.tableName);
query = function (q) {
q.distinct()
.innerJoin('orders', function () {
this.on('users.id', '=', 'orders.user_id')
.andOn('orders.amount', '>', 100);
})
.innerJoin('addresses', function () {
this.on('users.id', '=', 'addresses.user_id')
.andOn('addresses.state', '=', 'New York');
});
return q;
};
Promise.all([
query(knex.count()).first().then(function (row) {
return +row.count;
}),
User.query(query).fetchAll({ withRelated: ['orders', 'addresses'] })
]).spread(function (count, users) {
// success
});
var Promise = require('bluebird');
Model.countAndFetch = function (query, options) {
return Promise.all([
query(knex.count()).first().then(function (row) {
return +row.count;
}),
this.query(query).fetchAll(options)
]);
};
User.query(function (q) {
q.distinct()
.innerJoin('orders', function () {
this.on('users.id', '=', 'orders.user_id')
.andOn('orders.amount', '>', 100);
})
.innerJoin('addresses', function () {
this.on('users.id', '=', 'addresses.user_id')
.andOn('addresses.state', '=', 'New York');
});
})
.fetchAll({ withRelated: ['orders', 'addresses'] })
.then(function (users) {
var user = users.at(0),
orders = user.related('orders'),
addresses = user.related('addresses');
// do stuff with the user, their orders, and their addresses
});
@kenshido
Copy link

Solution I've found: .andOn('code.searchcode', '=', bookshelf.knex.raw("'" + code + "'"));

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment