Convert Raw SQL to Bookshelf/Knex

Question

I'm trying to convert a simple raw SQL to use Bookshelf/Knex in JavaScript:

Original SQL: select * from o where o.id = 1 or o.id = 2 and (o.is_a = true or o.is_b = true) and o.status = 'good';

I've tried to rewrite it in multiple ways using .orWhere .andWhere but cannot get the same result that I get from SQL. What am I doing wrong?

Some of the attempts:

await O.forge()
         .query({
             where: {is_a: true},
             orWhere: {is_b: true},
         })
         .query('whereIn', 'id', ids)
         .query('whereIn', 'status', goodStatuses)

Another attempt:

.query(async qb => {
            qb.where('is_a', '=', true)
                .orWhere('is_b', '=', true)
                .andWhere('id', 'in', ids)
                .andWhere('status', 'in', goodStatuses);
        })

Answer 1

In order to match the brackets in your original query you will need to nest functions into your kenx query. Either of these should work depending whether you want to use Bookshelf or just knex.

knex only:

await knex('o')
    .where('o.id', 1)
    .orWhere('o.id', 2)
    .andWhere(function() {
        this.where('o.is_a', true)
        .orWhere('o.is_b', true)
    })
    .andWhere('o.status', 'good');

Or you can use your bookshelf model and essentially pass the same query into the query builder

await model.query(qb => {
    qb.where('o.id', 1)
    .orWhere('o.id', 2)
    .andWhere(function() {
        this.where('o.is_a', true)
        .orWhere('o.is_b', true)
    })
    .andWhere('o.status', 'good');
}).fetch();

Or as it seems you have improved the original query to use in instead of multiple ors

await knex('o')
    .where('o.id', 'in', ids)
    .andWhere(function() {
        this.where('o.is_a', true)
        .orWhere('o.is_b', true)
    })
    .andWhere('o.status', 'in', goodStatuses);