Feature Request: A tutorial of custom Operations (to do a sophisticated insert operation)

[laurisvan]

Related to my earlier #2096 ticket of optimising whereIn queries. Thanks to the support of overriding methods, we could speed up queries drastically.

Now we would like to eliminate thousands of parameters in batch insert. I envision this could be done in form of
INSERT INTO <TABLE> (<COLS>) VALUES (<VALUES>) where VALUES would support raw expression (I would pass JSON string that would be decoded with postgres function).

Now I understand insert is implemented with KnexOperation that extends ObjectionToKnexConvertingOperation, and knex insert itself does not seem to support specifying cols or values part, either. I believe that might be a bit difficult to support in Objection?

How could I override insert with a custom operation with signature compatible with KnexOperation, but the practical implementation would be something in line with knex.raw()? E.g. what is the minimal implementation of a custom Operation that could execute a custom query?

[koskimas]

In this case I think it would be easiest (and cleanest) to just have a separate method that builds a raw query. Something like:

export class MyQueryBuilder<M extends Model, R = M[]> extends QueryBuilder<M, R> {

  ArrayQueryBuilderType!: MyQueryBuilder<M, M[]>
  SingleQueryBuilderType!: MyQueryBuilder<M, M>
  NumberQueryBuilderType!: MyQueryBuilder<M, number>
  PageQueryBuilderType!: MyQueryBuilder<M, Page<M>>

  batchInsert(items: PartialModelObject<M>[]>): Promise<M[]> {
    const result = await this.knex().raw('your query... returning *')

    return this.modelClass().ensureModelArray(result.rows)
  }
}

The method doesn't return a query builder, and thus isn't chainable, but there aren't that many things you can chain into an insert query.

[laurisvan]

Hmm chaining was exactly what I was hoping to achieve, so that I could say .returning('*'). What is the simplest operation that I could fork and start modifying for the needs? ObjectionToKnexConvertingOperation or perhaps something else?

[laurisvan]

@koskimas As discussed earlier, would you think it were possible to support raw insert, so that it would still return a query builder? I think it would serve the batch insert just fine.