# Filtering

This page describes how to use the `filter` query parameter to retrieve only the records you need from list endpoints in the Kanbert REST API.

## TL;DR

* Add a `filter` query parameter to any supported list endpoint (e.g., `GET /api/v1/projects`).
* Two formats are supported: **JSON** and **human-readable DSL**.
* Combine multiple conditions, nest boolean groups, and use rich operators (`contains`, `in`, `gte`, etc.).
* Fields and operators are endpoint-specific; unknown fields or invalid combinations return a **400**.
* Supports relation filters (`any`, `all`, `none`, `.count`) and special helper values (e.g., `now`, `me.id`).


## Supported endpoints

Filtering is available on list endpoints (e.g., `GET /api/v1/projects`).
Each endpoint documents:

* Which fields are filterable
* The type of each field
* Supported operators for that field


Important note
Filterable fields vary per endpoint. Always check the endpoint’s documentation or try the examples below.

# Filter Formats

You can pass the `filter` parameter in **JSON** or **string DSL** form. Both formats express the same concepts.

## 1) JSON Format

### Basic array (implicit AND)

```json
[
  {"field": "title", "op": "contains", "value": "Acme"},
  {"field": "created_at", "op": "gte", "value": "2024-01-01"}
]
```

### Explicit boolean grouping

```json
{
  "and": [
    {"field": "client.name", "op": "contains", "value": "Acme"},
    {
      "or": [
        {"field": "shortcode", "op": "eq", "value": "P-42"},
        {"field": "shortcode", "op": "startsWith", "value": "P-"}
      ]
    }
  ]
}
```

### Form-encoded equivalent (HTML forms)

```
filter[0][field]=title&filter[0][op]=contains&filter[0][value]=Acme
```

## 2) DSL String Format

A human-readable alternative to JSON:

```
filter=title contains "Acme" and (created_at >= "2024-01-01" or client.name contains 'Corp')
```

More examples:

```
filter=shortcode = "P-42"
filter=client.name contains 'Acme' or title startsWith 'P-'
filter=status in ['ACTIVE','PENDING'] and budget >= 1000
filter=primary_contact_id is null
```

DSL features:

* Case-insensitive operators (`contains`, `CONTAINS`, `StartsWith`, …)
* Symbol equivalents supported (`=`, `!=`, `<`, `<=`, …)
* Supports groups with parentheses


# Operators

Unless limited by the endpoint, these operators are generally supported:

### Comparison

* `eq`, `ne` / `neq`, `lt`, `lte` / `le`, `gt`, `gte` / `ge`


### String operators

* `contains`, `like`, `startsWith`, `endsWith`


### Sets

* `in`, `nin` / `notin`
Example values: `[1,2,3]` or `['A','B']`


### Null checks

* `is null`
* `is not null`


### Symbol equivalents

* `=`, `!=`, `<`, `<=`, `>`, `>=`


# Field Types

Filtering is **type-aware**. Common types include:

* **string** – free-text; use string or equality operators
* **number** – numeric comparison
* **boolean** – `true` / `false`
* **datetime / date** – ISO-8601 values
* **enum** – restricted string sets
* **id** – entity identifiers
* **relation** – can be used with relation operators (see next section)


Dot-notation may be available for relations (e.g., `client.name`).

# Relation Filters: `any`, `all`, `none`, `.count`

Use relation filters to query based on related records.

### Relation operators

* **any** — at least one related record matches the sub-filter
  * Without sub-filter: “has at least one related record”
* **none** — zero related records match the sub-filter
  * Without sub-filter: “has no related records”
* **all** — every related record must match
* **.count** — compare the number of related items (`=`, `>`, `>=`, …)


## DSL Examples

```text
# Any related member with first_name = 'Neil'
filter=members any (first_name = 'Neil')

# Has at least one member
filter=members any

# No related members with role = 'EXTERNAL'
filter=members none (role = 'EXTERNAL')

# All tasks are completed
filter=tasks all (status = 'DONE')

# Projects with more than 0 tasks
filter=tasks.count > 0

# At least 3 active tasks
filter=tasks.count >= 3 and tasks any (status = 'ACTIVE')
```

## JSON Examples

```json
[
  {"field":"members","op":"any","value":{"field":"first_name","op":"eq","value":"Neil"}},
  {"field":"members","op":"none","value":{"field":"role","op":"eq","value":"EXTERNAL"}},
  {"field":"tasks","op":"all","value":{"field":"status","op":"eq","value":"DONE"}},
  {"field":"tasks.count","op":"gt","value":0},
  {"field":"tasks.count","op":"gte","value":3}
]
```

Notes:

* Sub-filters behave like normal filters but operate on the related model.
* Can be mixed with regular filter conditions and nested boolean groups.


# Special Values and Helpers

You can simplify filter expressions using special built-in helper values.

## `me.id`

Resolves to the authenticated user’s ID.
Valid **only on `id` fields**.

**Example:**

```
filter=id eq me.id
```

## Date and time helpers

These helpers are valid on `date`, `datetime`, or `timestamp` fields:

| Helper | Meaning |
|  --- | --- |
| `now` | Current date-time |
| `sow` / `eow` | Start / end of week |
| `som` / `eom` | Start / end of month |
| `today` | The current day; expands to full-day range |
| `today±Nd` | Relative offset from today (days) |


### Examples

```
filter=start_date lt now
filter=start_date gt sow
filter=start_date lt eom
filter=start_date eq today
filter=start_date gt today-30d
```

`today` expansion example (conceptually):

```
start_date >= 2025-02-07T00:00:00Z AND start_date < 2025-02-08T00:00:00Z
```

# Practical Examples (cURL)

### Filter by title substring + minimum creation date (JSON)

```bash
curl -G \
  --data-urlencode 'filter=[{"field":"title","op":"contains","value":"Acme"},{"field":"created_at","op":"gte","value":"2024-01-01"}]' \
  'https://api.kanbert.com/api/v1/projects'
```

### Client name OR shortcode prefix (JSON with grouping)

```bash
curl -G \
  --data-urlencode 'filter={"and":[{"field":"client.name","op":"contains","value":"Acme"},{"or":[{"field":"shortcode","op":"eq","value":"P-42"},{"field":"shortcode","op":"startsWith","value":"P-"}]}]}' \
  'https://api.kanbert.com/api/v1/projects'
```

### Same using DSL

```bash
curl -G \
  --data-urlencode "filter=client.name contains 'Acme' and (shortcode = 'P-42' or shortcode startsWith 'P-')" \
  'https://api.kanbert.com/api/v1/projects'
```

### Sets + numeric comparison

```bash
curl -G \
  --data-urlencode "filter=status in ['ACTIVE','PENDING'] and budget >= 1000" \
  'https://api.kanbert.com/api/v1/projects'
```

### Null checks

```bash
curl -G \
  --data-urlencode "filter=primary_contact.id is null" \
  'https://api.kanbert.com/api/v1/projects'
```

# Sorting, Pagination, and Includes

Filtering works independently of:

* **Sorting:** continue using the `sort` parameter documented per endpoint.
* **Pagination:** use the endpoint’s default pagination scheme (cursor or page).
* **Includes / Sparse Fieldsets:** you can safely combine them with `filter`.


# Validation and Errors

Invalid filters return a **400 Bad Request**. Common issues:

* Unknown field name
* Operator not allowed for the field type
* Invalid value type
* Malformed JSON or DSL


Example error:

```json
{
  "message": "Invalid filter",
  "errors": [
    {"field": "budget", "issue": "Operator 'contains' is not allowed for type 'number'"}
  ]
}
```

# Tips

* Use **JSON** for complex, nested filters or programmatically generated queries.
* Use **DSL** for debugging and quick experimentation.
* Use **dot-notation** for related fields when supported.
* Use **ISO-8601** timestamps for consistent date filtering.
* For relative dates, prefer **`today`**, `now`, and helper values.


# FAQ

**Q: Can I mix JSON and DSL in one request?**
A: No — choose one format per request.

**Q: Are `and`/`or` required?**
A: JSON arrays use implicit `AND`. To mix `AND`/`OR`, use explicit groups or DSL.

**Q: Can I filter by nested relations?**
A: Yes, when documented. Use dot-notation (e.g., `client.name`).