JAYSON DERULO

1 files changed, 151 insertions, 0 deletions

diff --git a/query/filters.md b/query/filters.md
new file mode 100644
index 0000000..13a929b
--- /dev/null
+++ b/query/filters.md
@@ -0,0 +1,151 @@
+# Filters
+
+[back to query](README.md) /// [home](../README.md)
+
+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.
+
+```json
+{
+  "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
+
+```json
+{ "column": "price", "op": ">", "value": 10 }
+```
+
+### AND (all must match)
+
+```json
+{
+  "and": [
+    { "column": "status", "op": "=", "value": "active" },
+    { "column": "price", "op": ">=", "value": 5 }
+  ]
+}
+```
+
+### OR (any can match)
+
+```json
+{
+  "or": [
+    { "column": "status", "op": "=", "value": "draft" },
+    { "column": "status", "op": "=", "value": "active" }
+  ]
+}
+```
+
+### NOT
+
+```json
+{
+  "not": { "column": "status", "op": "=", "value": "deleted" }
+}
+```
+
+### Nesting
+
+You can nest these however you want:
+
+```json
+{
+  "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.
+
+```json
+{
+  "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:
+
+```json
+{
+  "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.