# 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.