`WHERE IN (...)` support

[clschnei]

From the docs:

Multiple objects passed to .where are joined with or.

sq.from`person`.where({ name: 'Rob' }, { name: 'Bob' }).query

{ text: 'select * from person where (name = $1 or name = $2)',
  args: ['Rob', 'Bob'] }

Any reason we couldn't join the objects with IN (?, ?) notation?

[eejdoowad]

I'm not sure what you mean by joining objects with IN. An example of how you would expect the API to work would help.

I have some ideas on supporting IN operations:

1. If a .where object value is an array, use IN instead of equality.

sq.from`book`.where({ title: ['a', 'b', 'c'] }).query

{ text: 'select * from book where title in ($1, $2, $3)',
  args: ['a', 'b', 'c']

2. Add dedicated operators for IN, NOT IN, ANY, ALL, BETWEEN, NOT BETWEEN, EXISTS, NOT EXISTS.

sq.from`book`.where({ title: sq.in(['a', 'b', 'c']) }).query

{ text: 'select * from book where title in ($1, $2, $3)',
  args: ['a', 'b', 'c']

3. Add a dedicated helper for constructing argument lists.

sq.from`book`.where({ c: sq.l`title in`.args(...['a', 'b', 'c'])) }).query
// or
sq.from`book`.where({ c: sq.l`title in ${sq.args(...['a', 'b', 'c'])}` }).query

{ text: 'select * from book where title in ($1, $2, $3)',
  args: ['a', 'b', 'c']

1 is the simplest, but it would make the library inconsistent and make it difficult to test arrays and json for equality.

2 is what I'm leaning toward.

3 is something I'll probably also add eventually

For now, you can follow Node Postgres's guidelines for constructing IN conditions.

[clschnei]

Thanks for the detailed response! I think the addition of new operators (number 2) would be a great way to go.

The solution I was thinking of would be to:

sq.from`person`.where({ name: 'Rob' }, { name: 'Bob' }).query

{ text: 'select * from person where (name = ANY ($1))',
  args: [['Rob', 'Bob']] }

// OR
{ text: 'select * from person where (name IN ($1, $2))',
  args: ['Rob', 'Bob'] }

However, I'm now noticing that it could present issues if you're trying to apply multiple (possibly mutually exclusive) conditionals. ex:

sq.from`person`.where(
  { name: 'Rob', zipCode: 12345 }, 
  { name: 'Bob', zipCode: 67890 },
).query

// ???

[dzdrazil]

I'm writing some helper functions that generate queries with sqorn (think something like ruby's filterrific) and I like option 2. At first glance I liked option 1, but that would definitely get confusing when you also happen to have array type columns, and support for further operators beyond in seems useful.

[eejdoowad]

Yep, I'm definitely going with 2. The current plan is to provide an expression builder.

I'm currently debating whether to provide a fluent interface or an expression tree interface.

// option 1: fluent interface
// pros: reads like SQL
// cons: confusing operator precedence, more difficult implementation
const condition =
  e('book.genre').in(['Fiction', 'History', 'Fantasy'])
  .and(e('book.publish_year').lt(1980))

// option 2: expression tree
// pros: explicit operator ordering, simpler implementation, easier typing
// cons: not like SQL
const condition = e.and(
  e.in('book.genre', ['Fiction', 'History', 'Fantasy']),
  e.lt('book.publish_year', 1980)
)

// both would be used like follows:
sq.from('book').where(condition).return('title', 'id').query
{ text: 'select title, id from book where (book.genre in ($1, $2, $3) and book.publish_year < $4',
  args: ['Fiction', 'History', 'Fantasy', 1980] }

I'm probably going with 2.

[eejdoowad]

Started work on this in #51. ETA 2-3 days.

The PR packages major breaking changes to make query building more consistent and secure.

The plan is to provide a functional, not fluent, Expression Builder.

Example 1. Expression Values: build expressions from raw values

const { e } = sq

sq.return(23) // no longer allowed, throws error

sq.return('hello', e('world'), e.eq(1, 2), e.eq(sq.raw('name'), 'Jo')).query

{ text: 'select hello, $1, $2 = $3, (name = $4)',
  args: ['world', 1, 2, 'Jo'] }

Example 2. Expression trees: build expressions from expressions

sq.return(e.and(e.eq(e.plus(3, 4), 7), true).query

{ text: 'select ((($1 + $2) = $3) and $4)',
  args: [3, 4, 7, true] }

Example 3. Currying: expressions builders can be curried and called as tagged templates

sq.return(
  e.eq(1)(2),
  e.eq`genre`('fantasy'),
  e.eq`hello``${'world'}`
).query

{ text: 'select ($1 = $2), (genre = $3), (hello = $4)'
  args: [1, 2, 'fantasy', 'world'] }

Example 4. Set Expressions: support for operators like in, any, some, exists, etc.

sq.from`book`.return`title`
   where(e.in`book.genre`(['Fiction', 'History', 'Fantasy']))

{ text: 'select title where book.genre in ($1, $2, $3)',
  args: ['Fiction', 'History', 'Fantasy'] }

Example 5. Subquery Expressions: build expressions from subqueries

sq.return(e.plus(sq.return(e(2)), sq.return(e(3)))).query

{ text: 'select ((select $1) + (select $2))'
  args: [2, 3] }

Array, JSON, Aggregate, and String expressions will also be supported.

[eejdoowad]

A little bit behind schedule, and with MANY breaking API changes, but I ended up implementing a much more ambitious proposal: a functional, strongly typed expression builder.

See https://sqorn.org/docs/expressions.html.

// All the queries below produce:
// 'select * from book where id in ($1, $2, $3)'

// pass multiple arguments to .sql to build a values list
sq.sql`select * from book where id in`.sql(1, 2, 3)

// object form detects array and builds in
sq.from`book`.where({ id: [1, 2, 3] })

// e.in operation accepts two arguments
sq.from`book`.where(e.in(e`id`, [1, 2, 3]))

// operations can be curried
sq.from`book`.where(e.in`id`([1, 2, 3]))

// operations can be chained
sq.from`book`.where(e`id`.in([1, 2, 3]))

Switch to package '@sqorn/pg' v0.0.45. See the updated tutorial setup: https://sqorn.org/docs/setup.html