Conditional Helpers

Conditional helpers are used within the where query helper. They allow the use of operators like >, <=, in, not in, etc.. Helpers can be arranged pretty much anyway you want to and it will work. I'll try and show all the different ways you can do it.

Checkout the Conditional Playground.

Helper Cascading

Often times, a helper will be used, and its value will be an object. If a hepler is said to cascade, then whatever operation that it represents will persist through sub-objects and arrays until another operator takes over. Take a look at this example:

{
  type: "select",
  table: "users",
  where: {
    $gt: [
      { name: 'Bob', id: 7 },
      { $or: { last_name: 'Bob', first_name: { $equals: 'Bob' } } },
    ]
  }
}

select "users".*
from "users"
where ("users"."name" > $1
       and "users"."id" > $2)
  and ("users"."last_name" > $3
       or "users"."first_name" = $4)

Playground

Since the $gt helper is set to cascade: true in its helper definition, its operation carries throughout all of those object structures except when explicitly told otherwise, like through the use of the $equals helper. In that first_name: { $equals: 'Bob' } object, if the value of $equals was another object, then its operator would cascade in that scope.

Helper: '$or'

Cascades: false

Strictly speaking, $or is not a conditional helper, but it is used in building conditions. Use it to "Or" multiple conditions together

{
  type: 'select'
, table: 'users'
, where: {
    $or: [
      { sex: 'male',   salary: { $gt: 100000, $lt: 150000 } }
    , { sex: 'female', salary: { $gt: 120000, $lt: 150000 } }
    ]
  }
}

select "users".*
from "users"
where ("users"."sex" = $1
       and ("users"."salary" > $2
            and "users"."salary" < $3))
  or ("users"."sex" = $4
      and ("users"."salary" > $5
           and "users"."salary" < $6))

Helper: '$equals'

Cascades: true

Format: col = val

This is the default conditional helper. You do not need specify $equals. It is the default operator.

Example:

{
  type: 'select'
, table: 'users'
, where: { id: 7 }
, where: { id: { $equals: 7 } }
, where: { $equals: { id: 7 } }
}

select "users".* from "users" where "users"."id" = 7

Helper: '$not'

Cascades: false

Format: not col = val

This inverts any expression that is contained by the helper.

Example:

{
  type: 'select'
, table: 'users'
, where: { roleid: { $not : 7 } }
}

select "users".* from "users" where not "users"."roleId" = 7

Example with nested expression

{
  type: 'select'
, table: 'users'
, where: { age : { $not : { $gt: 7 } } }
}

select "users".* from "users" where not "users"."age" > 7

Helper: '$ne'

Cascades: true

Format: col != val

Does not equal helper.

Example:

{
  type: 'select'
, table: 'users'
, where: { id: { $ne: 7 } }
, where: { $ne: { id: 7 } }
}

select "users".* from "users" where "users"."id" != 7

Helper: '$gt'

Cascades: true

Format: col > val

Greater than

Example:

{
  type: 'select'
, table: 'users'
, where: { id: { $gt: 7 } }
, where: { $gt: { id: 7 } }
}

select "users".* from "users" where "users"."id" > 7

Helper: '$gte'

Cascades: true

Format: col >= val

Greater than equal to

Example:

{
  type: 'select'
, table: 'users'
, where: { id: { $gte: 7 } }
, where: { $gte: { id: 7 } }
}

select "users".* from "users" where "users"."id" >= 7

select "users".* from "users" where "users"."id" > 7

Helper: '$lt'

Cascades: true

Format: col < val

Less than

Example:

{
  type: 'select'
, table: 'users'
, where: { id: { $lt: 7 } }
, where: { $lt: { id: 7 } }
}

select "users".* from "users" where "users"."id" < 7

Helper: '$lte'

Cascades: true

Format: col <= val

Less than

Example:

{
  type: 'select'
, table: 'users'
, where: { id: { $lte: 7 } }
, where: { $lte: { id: 7 } }
}

select "users".* from "users" where "users"."id" <= 7

Helper: '$null'

Cascades: true

Format: col is null

Is Null

Example:

{
  type: 'select'
, table: 'users'
, where: { id: { $null: true } }
, where: { $null: { id: true } }
}

select "users".* from "users" where "users"."id" is null

Helper: '$notNull'

Cascades: true

Format: col is not null

Is not null

Example:

{
  type: 'select'
, table: 'users'
, where: { id: { $notNull: true } }
, where: { $notNull: { id: true } }
}

select "users".* from "users" where "users"."id" is not null

Helper: '$like'

Cascades: true

Format: col like value

Value likeness

Example:

{
  type: 'select'
, table: 'users'
, where: { name: { $like: 'Bob' } }
, where: { $like: { name: 'Bob' } }
}

select "users".* from "users" where "users"."name" like 'Bob'

Helper: '$ilike'

Cascades: true

Format: col ilike value

Value likeness case insensitive.

Example:

{
  type: 'select'
, table: 'users'
, where: { name: { $ilike: 'Bob' } }
, where: { $ilike: { name: 'Bob' } }
}

select "users".* from "users" where "users"."name" ilike 'Bob'

Helper: '$in'

Cascades: false

Format: col in set|expression

Value in a set or recordset. Evaluates to false if the set or recordset is empty.

Examples:

{
  type: 'select'
, table: 'users'
, where: { id: { $in: [ 1, 2, 3 ] } }
}

select "users".* from "users" where "users"."id" in (1, 2, 3)

{
  type: 'select'
, table: 'users'
, where: {
    id: { $in: {
      type: 'select'
    , columns: ['id']
    , table: 'consumers'
    , where: { name: 'Bob' }
    } }
  }
}

select "users".* from "users" where "users"."id" in (
  select "consumers"."id" from "consumers"
  where "consumers"."name" = 'Bob'
)

{
  type: 'select'
, table: 'users'
, where: { id: { $in: [] } }
}

select "users".* from "users" where false

Helper: '$nin'

Cascades: false

Format: col in set|expression

Value not in a set or recordset.

Example:

{
  type: 'select'
, table: 'users'
, where: { id: { $nin: [ 1, 2, 3 ] } }
}

select "users".* from "users" where "users"."id" not in (1, 2, 3)

{
  type: 'select'
, table: 'users'
, where: {
    id: { $nin: {
      type: 'select'
    , columns: ['id']
    , table: 'consumers'
    , where: { name: 'Bob' }
    } }
  }
}

select "users".* from "users" where "users"."id" not in (
  select "consumers"."id" from "consumers"
  where "consumers"."name" = 'Bob'
)

Helper: '$exists'

Cascades: false

Format: exists expression

Expression exists.

Example:

{
  type: 'select'
, table: 'users'
, where: {
    $exists: {
      type: 'select'
    , table: 'other_table'
    , where: { column: 'value' }
    }
  }
}

select "users".* from "users" where exists (
  select "other_table".* from "other_table"
  where "other_table"."column"=$1
)

Helper: '$notExists'

Cascades: false

Format: not exists expression

Expression not exists.

Example:

{
  type: 'select'
, table: 'users'
, where: {
    $notExists: {
      type: 'select'
    , table: 'other_table'
    , where: { column: 'value' }
    }
  }
}

select "users".* from "users" where not exists (
  select "other_table".* from "other_table"
  where "other_table"."column"=$1
)

Helper: '$custom'

Cascades: false

Define your own custom format on the fly.

Example:

{
  type: 'select'
, table: 'users'
, where: {
    id: { $notNull: true }
  , $custom: ['coalesce(something::json, $1) or $2', 'Bob', 'Alice']
  }
, where: {
    value: 'coalesce(something::json, $1) or $2'
  , values: ['Bob', 'Alice']
  }
}

select "users".*
from "users"
where "users"."id" is not null
  and coalesce(something::json, 'Bob') or 'Alice'

Register your own helpers

Check out the source code to see how I implemented conditional helpers. You can find them in helpers/condiontal.js. The API is the standard Helper interface used throughout MoSQL.

mosql.conditionalHelpers.add( name, [options], callback )

Registers a new query helper.

Callbacks arguments are: callback( column, value, values, table, query )

Arguments:

Column - The column associated to the operation (already quoted and assocated to table).
Value - The value being operated on.
Values - The values array. All values not escaped by surrounding '$' signs are pushed to the values array for parameterized queries.
Table - The table associated to the column
Query - This is the whole MoSQL query object passed in by the user.

Example:

var mosql = require('mongo-sql');

mosql.conditionalHelpers.add('$years_ago', function(column, value, values, table){
  return column + " >= now() - interval " + value + " year";
});

mosql.sql({
  type: 'delete'
, table: 'users'
, where: {
    created_at: { $years_ago: 5 }
  }
});

delete from "users" where "users"."created_at" >= now() - interval $1 year

Playground

mosql.conditionalHelpers.has( name )

Returns a boolean denoting whether or not a conditional helper exists.

mosql.conditionalHelpers.get( name )

Returns the conditional helper interface: { fn, options }.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

conditional-helpers.md

conditional-helpers.md

Conditional Helpers

Helper Cascading

Helper: '$or'

Helper: '$equals'

Helper: '$not'

Helper: '$ne'

Helper: '$gt'

Helper: '$gte'

Helper: '$lt'

Helper: '$lte'

Helper: '$null'

Helper: '$notNull'

Helper: '$like'

Helper: '$ilike'

Helper: '$in'

Helper: '$nin'

Helper: '$exists'

Helper: '$notExists'

Helper: '$custom'

Register your own helpers

mosql.conditionalHelpers.add( name, [options], callback )

mosql.conditionalHelpers.has( name )

mosql.conditionalHelpers.get( name )

Files

conditional-helpers.md

Latest commit

History

conditional-helpers.md

File metadata and controls

Conditional Helpers

Helper Cascading

Helper: '$or'

Helper: '$equals'

Helper: '$not'

Helper: '$ne'

Helper: '$gt'

Helper: '$gte'

Helper: '$lt'

Helper: '$lte'

Helper: '$null'

Helper: '$notNull'

Helper: '$like'

Helper: '$ilike'

Helper: '$in'

Helper: '$nin'

Helper: '$exists'

Helper: '$notExists'

Helper: '$custom'

Register your own helpers

mosql.conditionalHelpers.add( name, [options], callback )

mosql.conditionalHelpers.has( name )

mosql.conditionalHelpers.get( name )