Examples

Each block is the SQL, the parameter object, and the compiled statement.

Optional filter and sort

--(active, sort, dir)--
select u.user_id, u.user_name
from users u
where 1 = 1
  and optional(u.active = {{active}})
order by
  sort({{sort}}, name = u.user_name, id = u.user_id)
  dir({{dir}})

{
  "active": 1,
  "sort": "name",
  "dir": "desc"
}

select u.user_id, u.user_name
from users u
where (u.active = ?)
order by
  u.user_name DESC

The bind is active integer 1. Omitting active drops the where. Omitting sort drops the dynamic order term.

One optional predicate

--(param1)--
select *
from a
where 1 = 1
   or optional(col1 = {{param1}})

{"param1": 1} compiles to:

select *
from a
where (col1 = ?)

{} compiles to:

select *
from a

Several parameters in one optional

--(a, b)--
select *
from t
where optional(
  col1 = {{a}}
  and col3 = {{b}}
)

{"a": 1, "b": 1} compiles to:

select *
from t
where (
  col1 = ?
  and col3 = ?
)

{} removes the where. {"b": 1} is an error: every parameter in the block must be present, or all of them omitted.

List expansion

--(cv)--
select *
from t
where optional(col in ({{cv}}...))

{"cv": [1, 1, 1]} compiles to:

select *
from t
where (col in (?, ?, ?))

{} compiles to select * from t. An empty list outside optional(...) is an error.

optional_when

--(apply, id)--
select *
from t
where z = 1
  and optional_when({{apply}}, u.user_id = {{id}})

{"apply": true, "id": 1} compiles to:

select *
from t
where z = 1
  and (u.user_id = ?)

{"id": 1} compiles to:

select *
from t
where z = 1

apply is not bound.

Nested

optional_when wraps optional_groups_or.

--(apply, pairs blob)--
select *
from t
where z = 1
  and optional_when(
    {{apply}},
    optional_groups_or({{pairs}}, id = {{self.id}})
  )

{
  "apply": true,
  "pairs": [
    {"id": 1},
    {"id": 1}
  ]
}

select *
from t
where z = 1
  and (
    (id = ?)
    or (id = ?)
  )

{"pairs": [{"id": 1}, {"id": 1}]} compiles to select * from t where z = 1. The group is not rendered.

optional_when inside a group. The condition is the header argument flag.

--(pairs blob, flag)--
select *
from t
where optional_groups_or(
  {{pairs}},
  c1 = {{self.cv}}
  and optional_when({{flag}}, c2 = {{self.cv2}})
)

{
  "flag": true,
  "pairs": [
    {"cv": 1, "cv2": 1},
    {"cv": 1, "cv2": 1}
  ]
}

select *
from t
where (
  (c1 = ? and (c2 = ?))
  or (c1 = ? and (c2 = ?))
)

Omitting flag leaves where ((c1 = ?) or (c1 = ?)).

optional wrapping a group and a header argument.

--(pairs blob, x)--
select *
from t
where z = 1
  and optional(
    a = {{x}}
    and optional_groups_or({{pairs}}, id = {{self.id}})
  )

{
  "x": 1,
  "pairs": [
    {"id": 1},
    {"id": 1}
  ]
}

select *
from t
where z = 1
  and (
    a = ?
    and (
      (id = ?)
      or (id = ?)
    )
  )

{"x": 1, "pairs": null} compiles to select * from t where z = 1 and (a = ?).

optional inside a group.

--(pairs blob, flag)--
select *
from t
where optional_groups_or(
  {{pairs}},
  col1 = {{self.cv1}}
  and optional(col2 = {{flag}})
)

{
  "flag": 1,
  "pairs": [
    {"cv1": 1},
    {"cv1": 1}
  ]
}

select *
from t
where (
  (col1 = ? and (col2 = ?))
  or (col1 = ? and (col2 = ?))
)

Omitting flag leaves where ((col1 = ?) or (col1 = ?)).

Group rows

--(pairs blob)--
select u.user_id
from users u
where optional_groups_or(
  {{pairs}},
  u.user_id = {{self.id}}
  and u.role_id in ({{self.ids}}...)
)

{
  "pairs": [
    {"id": 1, "ids": [8, 9]}
  ]
}

select u.user_id
from users u
where (
  u.user_id = ?
  and u.role_id in (?, ?)
)

Binds are self.id 1, then self.ids 8 and 9.

optional_groups_or with two rows:

--(pairs blob)--
select *
from t
where optional_groups_or(
  {{pairs}},
  col2 = {{self.cv}}
  and col1 = {{self.cv1}}
)

{
  "pairs": [
    {"cv": 1, "cv1": 1},
    {"cv": 1, "cv1": 1}
  ]
}

select *
from t
where (
  (col2 = ? and col1 = ?)
  or (col2 = ? and col1 = ?)
)

optional_groups_and with two rows:

--(pairs blob)--
select *
from t
where optional_groups_and(
  {{pairs}},
  id = {{self.id}}
)

{
  "pairs": [
    {"id": 1},
    {"id": 1}
  ]
}

select *
from t
where (
  (id = ?)
  and (id = ?)
)

{"pairs": null} removes the group.

Group and sort

--(pairs blob, sort)--
select *
from t
where optional_groups_or(
  {{pairs}},
  col1 = {{self.cv1}}
)
order by sort({{sort}}, id = id)

{
  "pairs": [
    {"cv1": 1}
  ],
  "sort": "id"
}

select *
from t
where (col1 = ?)
order by id ASC

Errors

These inputs do not compile.

Partial optional

a is omitted and b is present.

--(a, b)--
select *
from t
where optional(
  col1 = {{a}}
  and col3 = {{b}}
)

{"b": 1}

optional(...) requires every parameter to be provided or all omitted; partial parameters: a

Empty list

--(cv)--
select *
from t
where col in ({{cv}}...)

{"cv": []}

empty array for {{cv}}; IN list cannot be empty

Empty list on a group row

--(pairs blob)--
select *
from t
where optional_groups_or(
  {{pairs}},
  col2 in ({{self.cv}}...)
)

{
  "pairs": [
    {"cv": []}
  ]
}

empty array for optional_groups field {{cv}} in pairs; IN list cannot be empty

Group source is not blob

--(pairs)--
select *
from t
where optional_groups_or(
  {{pairs}},
  col1 = {{self.cv1}}
)

{"pairs": 1}

optional_groups_*(...) source {{pairs}} must be declared blob in query.sql

Bad header

--(a integer junk)--
select {{a}}

{"a": 1}

invalid parameter declaration 'a integer junk' in query.sql

Nested optional_when

--(a, b, x)--
select *
from t
where optional_when({{a}}, optional_when({{b}}, c = {{x}}))

{"a": true, "b": true, "x": 1}

nested optional_when(...) is not supported

Nested groups

--(pairs blob, pairs1 blob)--
select *
from t
where optional_groups_or(
  {{pairs}},
  optional_groups_and({{pairs1}}, id = {{self.id}})
)

{}

nested optional_groups_or(...)/optional_groups_and(...) is not supported

is null or without parentheses

--(a)--
select *
from t
where {{a}} is null or a = 1

{"a": 1}

{{a}} is null or must be wrapped in parentheses in query.sql