Filters

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.