Bookshelf.js / Knex.js nested where in single query

Question

How can I combine queries of related models in bookshelf.js?

As it stands, the ORM fires two separate queries to the database which I'd like to combine. Coming from sequelize it was possible like this:

Model.find({
  where: {
    someField: 'someValue'
  },
  include: [{
    model: OtherModel,
    as: 'otherModel',
    where: {
      someOtherField: 'someOtherValue' <--- part of the same query
    }
  }]
})

My current setup in bookshelf.js (relationships between Model and OtherModel are set):

Model
  .where({ someField: 'someValue' })
  .fetch({ withRelated: [{
    otherModel: q => q.where({ 
      someOtherField: 'someOtherValue' 
    })
  }] });

This works, except the knex.js debugger shows two separate queries executed against the database. I expect bookshelf to be smart enough to build SQL that joins in a single query.

Is this something that can be avoided through configuration or any other means?

Answer 1

This took me a long time to wrap my head around, but the multiple query behavior is by design. Bookshelf favors multiple un-joined round trips over joins, but doesn't N+1. It queries once per table. By breaking the queries up, it's possible to run some in parallel if there are multiple joins to a single table due to the asynchronous nature of node. It's a tradeoff that doesn't make sense for deeply nested relationships, but may for data models with many 1st generation relationships. I've never checked whether there is any sort of transaction or row locking.

You can force the join using Knex (http://knexjs.org/#Builder-join) and mix it into your bookshelf code, but for all but the most time sensitive and optimized applications, you probably won't notice the overhead unless your latency to the DB is poor.

If you're doing really complex queries where the joins are required for performance, I might suggest using just knex or knex.raw instead of bookshelf.