Skip to content

Latest commit

 

History

History
137 lines (100 loc) · 3.93 KB

operator-reference.md

File metadata and controls

137 lines (100 loc) · 3.93 KB
Raw

SQL Operators Supported

This section lists the operators that HoneySQL supports out of the box. There is no operator precedence assumed because SQL expressions are represented in prefix form, just like Clojure expressions.

Operators can be specified as keywords or symbols. Use - in the operator where the formatted SQL would have a space (e.g., :not-like is formatted as NOT LIKE).

and, or

Boolean operators. May take any number of expressions as arguments. nil expressions are ignored which can make it easier to programmatically build conditional expressions (since an expression that should be omitted can simply evaluate to nil instead).

{...
 :where [:and [:= :type "match"]
              (when need-status [:in :status [1 5]])]
 ...}
;; if need-status is truthy:
;;=> ["...WHERE (type = ?) AND (status IN (?, ?))..." "match" 1 5]
;; or, if need-status is falsey:
;;=> ["...WHERE (type = ?)..." "match"]
{...
 :where [:or [:= :id 42] [:= :type "match"]]
 ...}
;;=> ["...WHERE (id = ?) OR (type = ?)..." 42 "match"]

in, not-in

Predicates for checking an expression is or is not a member of a specified set of values.

The two most common forms are:

  • [:in :col [val1 val2 ...]] or [:not-in :col [val1 val2 ...]] where the valN can be arbitrary expressions,
  • [:in :col {:select ...}] or [:not-in :col {:select ...}] where the SELECT specifies a single column.

:col could be an arbitrary SQL expression (but is most commonly just a column name).

The former produces an inline vector expression with the values resolved as regular SQL expressions (i.e., with literal values lifted out as parameters): col IN [?, ?, ...] or col NOT IN [?, ?, ...]

The latter produces a sub-select, as expected: col IN (SELECT ...) or col NOT IN (SELECT ...)

You can also specify the set of values via a named parameter:

  • [:in :col :?values] or [:not-in :col :?values] where :params {:values [1 2 ...]} is provided to format in the options.

In this case, the named parameter is expanded directly when :in (or :not-in) is formatted to obtain the sequence of values (which must be sequential, not a Clojure set). That means you cannot use this approach and also specify :cache -- see cache in All the Options for more details.

Another supported form is checking whether a tuple is in a selected set of values that specifies a matching number of columns, producing (col1, col2) IN (SELECT ...), but you need to specify the columns (or expressions) using the :composite special syntax:

  • [:in [:composite :col1 :col2] ...] or [:not-in [:composite :col1 :col2] ...]

This produces (col1, col2) IN ... or (col1, col2) NOT IN ...

Note: This is a change from HoneySQL 1.x which accepted a sequence of column names but required more work for arbitrary expressions.

= <>

Binary comparison operators. These expect exactly two arguments.

not= and != are accepted as aliases for <>.

< > <= >=

Comparison operators. These expect exactly two arguments.

is, is-not

Predicates for NULL and Boolean values:

{...
 :where [:is :id nil]
 ...}
;;=> ["...WHERE col IS NULL..."]
{...
 :where [:is-not :id nil]
 ...}
;;=> ["...WHERE col IS NOT NULL..."]
{...
 :where [:is :col true]
 ...}
;;=> ["...WHERE col IS TRUE..."]
{...
 ;; unlike [:<> :col false], the following will include NULLs:
 :where [:is-not :col false]
 ...}
;;=> ["...WHERE col IS NOT FALSE..."]

xor, + - * / % | & ^

Mathematical and bitwise operators.

like, not like, ilike, not ilike, regexp

Pattern matching operators. regex is accepted as an alias for regexp.

similar-to and not-similar-to are also supported.

with ordinality

The ANSI SQL WITH ORDINALITY expression is supported as an infix operator:

{...
 [:with-ordinality [:jsonb_array_elements :j] [:arr :item :index]]
 ...}
;;=> ["...JSONB_ARRAY_ELEMENTS(j) WITH ORDINALITY ARR(item, index)..."]

||

String concatenation operator.