supporting "where in" with multiple columns

[tgriesser]

Looking for a way to support a multi-column where in clause like this:

const cols = ['colA', 'colB']
const values = [[1, 2], [3, 4]]

qb.where(cols, 'in', values)

Which gives a TypeError: exp.toOperationNode is not a function

This seems to work instead:

const whereCol = Array.isArray(whereInCol)
  ? sql`(${sql.join(whereInCol.map((c) => sql.ref(c)))})`
  : whereInCol;
const whereVals = Array.isArray(whereInCol)
  ? sql`(${sql.join(whereInIds.map((ids) => sql`(${sql.join(ids)})`))})`
  : whereInIds;

qb = qb.where(whereCol, "in", whereVals);

But wanted to see if there was either a simpler approach I was missing that would be better here, or if this is something that could added to be be handled by kysely if not.

[igalklebanov]

Hey 👋

Your implementation works, but it is not type-safe.

Personally, I support having this functionality in Kysely's core, as it is supported by all built-in dialects.

For now, something like this should do the trick.

[koskimas]

Since we are about to add a more comprehensive expression builder, at least something like this could be easily supported in a type-safe way:

.where(({ bin, ref, tuple }) => bin(
  tuple(ref('colA'), ref('colB')),
  'in', 
  [tuple(1, 2),  tuple(3, 4)]
)) 

I'm not loving the readability though.

[igalklebanov]

I like the idea of eb.tuple. We should provide a helper, roughly like this:

function inTuples<DB, TB extends keyof DB>(items: any[] /*should be records with all possible columns from context as keys, and values ValueExpression?*/) {
  return ({ bin, ref, tuple }: ExpressionBuilder<DB, TB>) => {
    const keys = resolveKeys(items)

    return bin(
      tuple(...keys.map((key) => ref(key))),
      'in',
      items.map((item => tuple(...keys.map((key) => isUndefined(item[key]) ? null : item[key])))),
    )
  }
}

Pretty close to how we handle InsertQueryBuilder.values().

And usage:

.where(inTuples(items))

[koskimas]

@tgriesser In the next version you can say:

.where(({ eb, refTuple, tuple }) => eb(
  refTuple('colA', 'colB'),
  'in', 
  [tuple(1, 2),  tuple(3, 4)]
))

and

.where(({ eb, refTuple, selectFrom }) => eb(
  refTuple('colA', 'colB'),
  'in', 
  selectFrom('some_table')
    .select(['colC', 'colD'])
    .$asTuple('colC', 'colD') // <-- Necessary evil because typescript
))

[vladshcherbin]

@koskimas any insight on release date of the next version?

I'm also using where in in codebase and this addition is awesome, will help simplify writing queries a lot 😍