Question

0

467

Vistas

Knex - Combine multiple dot functions into one

How can I combine multiple knex dot functions into one large dot function?

For example I have this query:

await knex
    .from(common)
    .select('x','y')
    .innerJoin(table1, `${table2}.id`, `${table1}.table2_id`)
    .whereNull(`${table1}.date`)
    .whereNull(`${table1}.file`)
    .where({
        field1: 1,
        field2: 2
    })

The inner join and whereNull statements are common to a few other queries. Is it possible to extract this part

.innerJoin(table1, `${table2}.id`, `${table1}.table2_id`)
.whereNull(`${table1}.date`)
.whereNull(`${table1}.file`)

and replace with one function, .joinWhereNull.

Then the original query could be rewritten like this:

await knex
    .from(common)
    .select('x','y')
    .joinWhereNull()
    .where({
        field1: 1,
        field2: 2
    })

I have tried writing custom functions like this

const joinWhereNull = () => {
    return (query) => {
        return query
           .innerJoin(table1, `${table2}.id`, `${table1}.table2_id`)
           .whereNull(`${table1}.date`)
           .whereNull(`${table1}.file`)
    }
}

However this feels clunky to use and I found the select statement needs its own function too, otherwise it is called prematurely.

Is there an easier way to combine knex functions?

Edit Also tried using a custom QueryBuilder:

Knex.QueryBuilder.extend('joinWhereNull', function (table1, table2) {
    return this
        .innerJoin(table1, `${table2}.id`, `${table1}.table2_id`)
        .whereNull(`${table1}.date`)
        .whereNull(`${table1}.file`)
})

However, I get a type error saying that joinWhereNull is not a function.

about 4 years ago · Juan Pablo Isaza

2 Respuestas

Responde la pregunta

0

Responde la pregunta

Encuentra empleos remotos

Answer 1 · 2022-07-03T19:29:28.347Z

You can use the QueryBuilder approach as shown here: Extending Query Builder.

However, TypeScript will give type error unless you extend the QueryBuilder interface with joinWhereNull function. The instructions to do that are in the same page: Extending Query Builder.

Quoting here:

Create a knex.d.ts file inside a @types folder (or any other folder).

// knex.d.ts

import { Knex as KnexOriginal } from 'knex';

declare module 'knex' {
  namespace Knex {
    interface QueryBuilder {
      customSelect<TRecord, TResult>(value: number): KnexOriginal.QueryBuilder<TRecord, TResult>;
    }
  }
}

Add the new @types folder to typeRoots in your tsconfig.json.

// tsconfig.json

{
  "compilerOptions": {
    "typeRoots": [
      "node_modules/@types",
      "@types"
    ],
  }
}

Answer 2 · 2022-07-03T19:29:28.479Z

Unfortunately there are multiple QueryBuilder instances at play. So defining the new method on one instance is not working. We can set the new method to QueryBuilder.prototype so it will be available on all the instances.

Following code worked in my test environment:

const knex = require('knex')({ client: 'sqlite3', connection: { filename: './mydb.sqlite' }});

let common = 'common';
let table1 = 'table1';
let table2 = 'table2';
let field1 = 'field1';
let field2 = 'field2';

// using default methods
console.log('one: ',
 knex
  .from(common)
  .select('x', 'y')
  .innerJoin(table1, `${table2}.id`, `${table1}.table2_id`)
  .whereNull(`${table1}.date`)
  .whereNull(`${table1}.file`)
  .where({
   field1: 1,
   field2: 2,
  })
  .toString()
);

// define the new method
//knex.queryBuilder().__proto__['joinWhereNull'] = function () {
Object.getPrototypeOf(knex.queryBuilder())['joinWhereNull'] = function () {
 return this.innerJoin(table1, `${table2}.id`, `${table1}.table2_id`)
  .whereNull(`${table1}.date`)
  .whereNull(`${table1}.file`);
};

// using our method
console.log('two: ',
 knex
  .from(common)
  .select('x', 'y')
  .joinWhereNull()
  .where({
   field1: 3,
   field2: 4,
  })
  .toString()
);

Output:

one:  select `x`, `y` from `common` inner join `table1` on `table2`.`id` = `table1`.`table2_id` where `table1`.`date` is null and `table1`.`file` is null and `field1` = 1 and `field2` = 2
two:  select `x`, `y` from `common` inner join `table1` on `table2`.`id` = `table1`.`table2_id` where `table1`.`date` is null and `table1`.`file` is null and `field1` = 3 and `field2` = 4

I don't have much idea about how the library internally works. But found following comment in the code:
make-knex.js

  // Allow chaining methods from the root object, before
  // any other information is specified.
  //
  // TODO: `QueryBuilder.extend(..)` allows new QueryBuilder
  //       methods to be introduced via external components.
  //       As a side-effect, it also pushes the new method names
  //       into the `QueryInterface` array.
  //
  //       The Problem: due to the way the code is currently
  //       structured, these new methods cannot be retroactively
  //       injected into existing `knex` instances!  As a result,
  //       some `knex` instances will support the methods, and
  //       others will not.
  //
  //       We should revisit this once we figure out the desired
  //       behavior / usage.  For instance: do we really want to
  //       allow external components to directly manipulate `knex`
  //       data structures?  Or, should we come up w/ a different
  //       approach that avoids side-effects / mutation?
  //
  //      (FYI: I noticed this issue because I attempted to integrate
  //       this logic directly into the `KNEX_PROPERTY_DEFINITIONS`
  //       construction.  However, `KNEX_PROPERTY_DEFINITIONS` is
  //       constructed before any `knex` instances are created.
  //       As a result, the method extensions were missing from all
  //       `knex` instances.)

0

467

Knex - Combine multiple dot functions into one

2 Respuestas

0

0

Encuentra empleos remotos

Andres GPT