Skip to content

Conditions Builder

The conditions builder allows you to create condition expression with logical operators such AND and OR. The first argument (or left operand) will be by default considered as a pointer. The second argument is an SQL operator which will allows you to perform an actual comparison. The third argument is a value by default, which implies that if you want to use a pointer instead, you will need to wrap it within quotes yourself. If you use the condition function with two arguments only (left operand and right operand), the operator will be = (equals).

You can pass a conditions builder to another one (as well as others builders, like a select builder), It this case it will be considered as a sub expression and therefore will be between parentheses.

    .if('posts.published_at', '>', new Date(2010, 1, 1))
    .and('title', 'foo')
    .or(sh.if('posts.user_id', 'IN','user_id').from('users')))

// > { text:
//    `"posts"."published_at" > '2010-02-01T05:00:00.000Z' AND
//      "title" = 'foo' OR ("posts"."user_id" IN (SELECT "user_id" FROM "users"))`,
//   values: [] }


There are different ways to create a conditions builder. You can use the if factory like in the example above. This case will likely occur when you want to create a sub expression to pass to another condition builder.

The second way will occur more often. It is by calling a method of a builder which returns a proxied conditions builder: select.where or select.on are good examples. Using a proxy let you conveniently call the conditions builder methods as if they were part of the main builder. Whenever a method or a property which does not belong to the conditions builder is called, the proxy is revoked.

const Laurents = sh
    .where('first_name', 'Laurent')
    .noop(); // call noop !!!

    .where('user_id', 'IN', Laurents)

// > { text:
//    `SELECT * FROM "posts" WHERE "user_id" IN
//     SELECT "user_id" FROM "users" WHERE "first_name" = 'Laurent')`,
//   values: [] }

A conditions builders has only two methods for the basic logical operators: .or and .and

If you want to pass around a builder (as argument of a function for example) you should make sure every proxy is revoked first by calling the noop method, otherwise it will definitely lead to some bugs.