knex-case's Introduction

knex-case

⚙️ A Knex.js plugin to add case statement support

Installation

$ yarn add knex-case

$ npm i knex-case

Usage

Simple

(CASE WHEN column=1 THEN 1 ELSE 0 END)

require('knex-case');
const Knex = require('knex')({ client: 'mssql' });

const q = Knex.queryBuilder()
  .when('column', '=', 1)
  .thenElse(1, 0)
  .toQuery();

console.log(q); // (CASE WHEN column=1 THEN 1 ELSE 0 END);

Conditional Simple

(CASE WHEN column=1 OR column=2 THEN 1 ELSE 0 END)

require('knex-case');
const Knex = require('knex')({ client: 'mssql' });

const q = Knex.queryBuilder()
  .when('column', '=', 1)
  .orWhen('column', '=', 2)
  .thenElse(1, 0)
  .toQuery();

console.log(q); // (CASE WHEN column=1 OR column=2 THEN 1 ELSE 0 END);

Multiple Clauses

(CASE WHEN column=1 THEN 1 WHEN column=2 THEN 2 ELSE 0 END)

require('knex-case');
const Knex = require('knex')({ client: 'mssql' });

const q = Knex.queryBuilder()
  .when('column', '=', 1)
  .thenElse(1)
  .when('column', '=', 2)
  .thenElse(2)
  .else(0)
  .toQuery();

console.log(q); // (CASE WHEN column=1 THEN 1 WHEN column=2 THEN 2 ELSE 0 END);

Nested Simple

(CASE WHEN column=1 THEN (CASE WHEN column='' THEN 2 ELSE 0 END) ELSE 0 END)

require('knex-case');
const Knex = require('knex')({ client: 'mssql' });

const q = Knex.queryBuilder()
  .when('column', '=', 1)
  .thenElse(
    Knex.queryBuilder()
      .when('column', '=', `''`)
      .thenElse(2, 0)
  )
  .else(0)
  .toQuery();

console.log(q); // (CASE WHEN column=1 THEN (CASE WHEN column='' THEN 2 ELSE 0 END) ELSE 0 END);

Caveats

String Escaping

Currently if you want to output correct SQL that includes string comparisons (ie. something='some-string') you will have to escape the internal value.

Example:

// String literals
Knex.queryBuilder().when('something', '=', `'some-string'`);

// Escape sequences
Knex.queryBuilder().when('something', '=', "'some-string'");

// Alternating quotations
Knex.queryBuilder().when('something', '=', "'some-string'");

knex-case's People

Contributors

Stargazers

Watchers

knex-case's Issues

knex#1482

@ericadamski I came across both your project and knex/knex#1482 (which has sat for almost 2 years) while searching for a way to do case in knex.

Your project looks like to me like a good potential implementation for this missing knex feature -- why not submit your implementation to the knex project?

knex-case not working with builder instance

I am trying to call knex-case functions as below but I keep getting select * only as output. Besides I also dont want that select *... part which is already being included. I only want the case clauses as indicated in the documentation
var builder = knex.queryBuilder();
builder.when('column', '=', 1);
builder.orWhen('column', '=', 7)
builder.thenElse(value)

Formatting a concatenated string

When using a concatenated string is wraps it in single quotes, this should be changed to return the string not wrapped in strings.

ex:

then(I + am + eric) returns 'I + am + eric' and it should return I + am + eric

Recommend Projects

ericadamski / knex-case Goto Github PK