query/filters.md


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
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151


Filters
back to query /// home
How to filter, join and sort your query results.
Simple Where
The where field is the lazy cunt way. Just key value pairs, all treated as = value with AND between them.
{
  "action": "select",
  "table": "items",
  "where": {
    "status": "active",
    "category_id": 3
  }
}


This becomes WHERE status = 'active' AND category_id = 3.
Structured Filter
The filter field gives you full crazy schizo control. Supports operators, nesting, AND/OR/NOT.
Single condition
{ "column": "price", "op": ">", "value": 10 }


AND (all must match)
{
  "and": [
    { "column": "status", "op": "=", "value": "active" },
    { "column": "price", "op": ">=", "value": 5 }
  ]
}


OR (any can match)
{
  "or": [
    { "column": "status", "op": "=", "value": "draft" },
    { "column": "status", "op": "=", "value": "active" }
  ]
}


NOT
{
  "not": { "column": "status", "op": "=", "value": "deleted" }
}


Nesting
You can nest these however you want:
{
  "and": [
    { "column": "price", "op": ">", "value": 0 },
    {
      "or": [
        { "column": "status", "op": "=", "value": "active" },
        { "column": "is_featured", "op": "=", "value": true }
      ]
    }
  ]
}


Supported Operators


Operator
What it does


=
equals


!=
not equals


>
greater than


>=
greater than or equal


<
less than


<=
less than or equal


like
SQL LIKE pattern matching


not_like
inverse of LIKE


in
value is in a list (pass an array as value)


not_in
value is not in a list


is_null
column is NULL (no value needed)


is_not_null
column is not NULL (no value needed)


between
between two values (pass [min, max] as value)


Maximum Conditions
Theres a per power level limit on how many conditions you can have in one query (max_where_conditions on the group). If you exceed it the server should give you a 400. this is to prevent people from going insane with query complexity.
Joins
Add joins to your query to join other tables. Works with select and count.
{
  "action": "select",
  "table": "items",
  "columns": ["items.name", "categories.name"],
  "joins": [
    {
      "table": "categories",
      "on": "items.category_id = categories.id",
      "type": "LEFT"
    }
  ]
}


Field
Type
Required
Default
Notes


table
string
yes

table to join


on
string
yes

join condition


type
string
no
"INNER"
INNER, LEFT, RIGHT


type may also be full but keep in mind that this is not support on MySQL/MariaDB backed servers so I advice against using it.
You need read permission on every table you join. If you dont have access to a joined table the whole query should get rejected by jsonderulo and he will spank you.
Order By
Sort your results:
{
  "order_by": [
    { "column": "created_at", "direction": "DESC" },
    { "column": "name", "direction": "ASC" }
  ]
}


Field
Type
Required
Default


column
string
yes


direction
string
no
"ASC"


You can sort by multiple columns. They apply in order.