How to search for multiple OR with AND in Knex.js + PostgreSQL?

Question

Say I have a table like this:

create table object_properties {
  uuid id;
  uuid source_id; // the object which has this property
  string name; // the property name
  uuid value_id; // the property value object
}

I want to find all object_properties:

WHERE source_id IN [1, 2, 3, 4, 5] AND name IN ['foo', 'bar']
OR WHERE source_id IN [6, 7, 8, 9, 1, 2] AND name IN ['hello', 'world']
OR WHERE source_id IN [6, 2, 4, 9, 1, 5] AND name IN ['x', 'y', 'z']

How can I do this in Knex.js with PostgreSQL?

Doing this:

const knex = require('../knex')

console.log(knex('object_properties')
  .orWhereIn('source_id', [1, 2, 3, 4, 5])
  .whereIn('name', ['foo', 'bar'])
  .orWhereIn('source_id', [6, 7, 8, 9, 1, 2])
  .whereIn('name', ['hello', 'world']).toString())

I get this:

select * from "object_properties" where "source_id" in (1, 2, 3, 4, 5) and "name" in ('foo', 'bar') or "source_id" in (6, 7, 8, 9, 1, 2) and "name" in ('hello', 'world')

Not sure if that is correct, is that the correct SQL query? Do I need to use raw instead, and/or wrap the OR in parentheses or anything like that? Never really used OR before in SQL.

Answer 1

You look to be missing the final part of your desired query OR WHERE source_id IN [6, 2, 4, 9, 1, 5] AND name IN ['x', 'y', 'z'] but other than that syntax wise it all looks correct. As for how the query will execute - In SQL AND takes precendence over OR, so without changing anything your query would execute like so:

select * from object_properties
where (
    source_id in (1, 2, 3, 4, 5) and name in ('foo', 'bar')
) or (
    source_id in (6, 7, 8, 9, 1, 2) and name in ('hello', 'world')
)

which does appear to be what you want.

However when in doubt and for additional clarity it's always good to wrap things in parentheses, you can achieve that in knex by wrapping your where clauses in functions like so

console.log(knex('object_properties')
  .where(function() {
    this.whereIn('source_id', [1, 2, 3, 4, 5])
    .whereIn('name', ['foo', 'bar']);
  })
  .orWhere(function() {
    this.whereIn('source_id', [1, 2, 3, 4, 5])
    .whereIn('name', ['hello', 'world']);
  })
  .orWhere(function() {
    this.whereIn('source_id', [6, 2, 4, 9, 1, 5])
    .whereIn('name', ['x', 'y', 'z']);
  }).toString());