1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
|
# 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).
```clojure
{...
: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
Binary predicate for checking an expression is
is a member of a specified set of values.
The two most common forms are:
* `[:in :col [val1 val2 ...]]` where the `valN` can be arbitrary expressions,
* `[: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 [?, ?, ...]`
The latter produces a sub-select, as expected: `col IN (SELECT ...)`
You can also specify the set of values via a named parameter:
* `[: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` 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](options.md#cache) 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] ...]`
This produces `(col1, col2) 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 `<>`.
## is, is-not
Binary predicates for `NULL` and Boolean values:
```clojure
{...
: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..."]
```
## mod, xor, + - * / % | & ^
Mathematical and bitwise operators. `+` and `*` are
variadic; the rest are strictly binary operators.
## like, not like, ilike, not ilike, regexp
Pattern matching binary operators. `regex` is accepted
as an alias for `regexp`.
`similar-to` and `not-similar-to` are also supported.
## ||
Variadic string concatenation operator.
|
