# Query Parameters

> The query params every read API of API Maker understands - find, skip, limit, sort, select, deep and getTotalCount, any column as a filter, upsert and returnDocument for updates - with examples, and the same keys in the body of the query APIs.

Source: https://docs.apimaker.dev/v4/docs/apis-all/query-params/query-params.html

The read APIs of a table ([get all](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-get-all-api.html), [get all by stream](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-get-all-by-stream-api.html), [get by id](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-get-by-id-api.html), [distinct](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-distinct-api.html)) take their options as query params of the URL. The [query](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-query-for-get-data-api.html) APIs take the same keys in their JSON body, and the write APIs accept `select` and `deep` to shape their answer.

| customer_id | first_name | last_name | last_update           | pincode | isActive |
|-------------|------------|-----------|-----------------------|---------|----------|
| 1           | Bob        | lin       | 2022-11-14 04: 34: 58 | 382345  | 1        |
| 2           | Alice      | Page      | 2022-10-15 02: 10: 40 | 382346  | 1        |
| 3           | Mallory    | Brown     | 2022-09-13 03: 44: 05 | 382347  | 1        |
| 4           | Eve        | Mathly    | 2022-11-12 01: 59: 33 | 382348  | 1        |
| 5           | Eve        | Page      | 2022-11-12 01: 59: 33 | 382349  | 1        |

## The params

| Param | Example | What it does |
|---|---|---|
| [find](https://docs.apimaker.dev/v4/docs/apis-all/query-params/find.html) | `?find={first_name:'Bob',pincode:{$gte:382345}}` | Filter with the MongoDB operators, on every database, including fields of related tables. |
| any column | `?first_name=Bob,Alice&pincode>=382345` | A column name is a filter too, with `>`, `>=`, `<`, `<=`, `!=`, comma lists and regular expressions. |
| [skip](https://docs.apimaker.dev/v4/docs/apis-all/query-params/query-params.html#skip) | `?skip=20` | Leave out the first rows. |
| [limit](https://docs.apimaker.dev/v4/docs/apis-all/query-params/query-params.html#limit) | `?limit=10` | At most this many rows. Without it, every row. |
| [sort](https://docs.apimaker.dev/v4/docs/apis-all/query-params/query-params.html#sort) | `?sort=-last_update,first_name` | Order, `-` for descending, several columns. |
| [select](https://docs.apimaker.dev/v4/docs/apis-all/query-params/select.html) | `?select=first_name,last_name` or `?select=-password` | Only these fields, or everything but these. |
| [deep](https://docs.apimaker.dev/v4/docs/apis-all/query-params/deep.html) | `?deep=[{s_key:'customer_id',t_col:'orders',t_key:'customer_id',isMultiple:true}]` | Replace ids with the related rows, from any table, database or instance, any depth. |
| [getTotalCount](https://docs.apimaker.dev/v4/docs/apis-all/query-params/query-params.html#getTotalCount) | `?getTotalCount=true` | Add `totalCount` : the rows matching the filter before paging. |
| [upsert](https://docs.apimaker.dev/v4/docs/apis-all/query-params/query-params.html#upsert) | `?upsert=true` | Update by id and replace by id : insert when the id does not exist. |
| [returnDocument](https://docs.apimaker.dev/v4/docs/apis-all/query-params/query-params.html#returnDocument) | `?returnDocument=before` | Update by id and replace by id : answer with the row before the change. |
| throwErrorIfRecordNotFound | `?throwErrorIfRecordNotFound=true` | Update, replace and remove by id : `404` instead of `data: null` for an unknown id. |

## skip

Leaves out the first rows. With `limit`, it pages through a table : page 3 of 10 rows is `skip=20&limit=10`.

```text
GET /api/schema/admin/mysql8/inventory/customers?skip=20&limit=10&sort=customer_id
```

- A whole number ; `0` or absent skips nothing.
- Always add a `sort` when you page : without an order, the database may return the rows in any order, and a page may repeat or miss rows.

## limit

At most this many rows. Without it, get all and the query APIs return every row which matches : give a `limit` as soon as a table can grow.

```text
GET /api/schema/admin/mysql8/inventory/customers?limit=2&sort=customer_id
```

**Answer**

```json
{
    "success": true,
    "statusCode": 200,
    "data": [
        { "customer_id": 1, "first_name": "Bob", "last_name": "Lin" },
        { "customer_id": 2, "first_name": "Alice", "last_name": "Page" }
    ]
}
```

- A whole number greater than zero.
- The streams ([get all by stream](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-get-all-by-stream-api.html), query by stream) send big results without a limit and without holding them in memory.

## sort

Orders the rows : name the columns, with `-` in front of a column for descending order.

```text
GET /api/schema/admin/mysql8/inventory/customers?sort=-last_update,first_name
```

**In the body of the query APIs : a string or an object**

```json
{ "find": {}, "sort": "-last_update,first_name" }
{ "find": {}, "sort": { "last_update": -1, "first_name": 1 } }
```

- Spaces around the commas are fine. On MongoDB a nested field sorts too : `sort=address.city`.
- Sorting a big table on a column without an index is slow : [Index Maker](https://docs.apimaker.dev/v4/extensions/index_maker/introduction.html) finds the queries which need one.

## getTotalCount

`getTotalCount=true` adds `totalCount` to the answer : the rows matching the filter, before `skip` and `limit`. One call gives the page and the total.

```text
GET /api/schema/admin/mysql8/inventory/customers?find={isActive:1}&skip=0&limit=2&getTotalCount=true
```

**Answer**

```json
{
    "success": true,
    "statusCode": 200,
    "data": [
        { "customer_id": 1, "first_name": "Bob", "last_name": "Lin" },
        { "customer_id": 2, "first_name": "Alice", "last_name": "Page" }
    ],
    "totalCount": 5
}
```

- It works on get all, get all by stream, query and query by stream ; a stream sends `totalCount` after the rows. The [count](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-count-api.html) API gives the number alone.
- The count is a second query on the database : on a very big table with a complex filter, it costs as much as the query itself.

## upsert

`upsert=true` makes [update by id](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-update-by-id-api.html) and [replace by id](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-replace-by-id-api.html) insert a row when the id does not exist. Without it, an unknown id changes nothing and answers `data: null`.

```text
PUT /api/schema/admin/mysql8/inventory/customers/update-by-id/999?upsert=true
```

**Body**

```json
{ "first_name": "John", "last_name": "Sina", "pincode": 382350, "isActive": 0 }
```

| Row 999 | What happens | Answer |
|---|---|---|
| exists | updated with the body | the row after the change |
| does not exist | inserted with `customer_id: 999` and the body | the new row |

- The inserted row is validated as a save : with a schema, every `required` field must be in the body.
- From code : `g.sys.db.updateById({ ..., upsert: true })`.

## returnDocument

Which version of the row an update by id or a replace by id sends back : `after` the change (the default), or `before` it. It changes the answer only, never what is written.

```text
PUT /api/schema/admin/mysql8/inventory/customers/update-by-id/1?returnDocument=before
```

**Answer : the row before the change of pincode**

```json
{
    "success": true,
    "statusCode": 200,
    "data": { "customer_id": 1, "first_name": "Bob", "last_name": "Lin", "pincode": 382345 }
}
```

- `select` and `deep` apply to the version returned. With `upsert` and an insert, `before` answers `null`.
- From code : `g.sys.db.updateById({ ..., returnDocument: 'before' })`.

## In a deep item

`skip`, `limit` and `sort` inside a [deep](https://docs.apimaker.dev/v4/docs/apis-all/query-params/deep.html) item apply to the related rows of each parent : the 5 latest orders of every customer, for example.

## Writing them

- Values are **JSON5** : `find={first_name:'Bob'}` needs no quotes around keys and accepts single quotes. Strict JSON works too.
- URL encode the value when your client does not : `find=%7Bfirst_name%3A'Bob'%7D`. The API testing page and `g.sys.db` do it for you.
- Numbers, booleans and ISO dates in a column param are cast to their type before the query (`?pincode=382345` compares a number).
- The same keys go in the body of the query APIs, as JSON :

```json
{ "find": { "first_name": "Bob" }, "sort": "-customer_id", "skip": 0, "limit": 10, "select": "first_name,last_name", "deep": [ { "s_key": "customer_id", "t_col": "orders", "t_key": "customer_id" } ], "getTotalCount": true }
```

## From code

`g.sys.db.getAll({ instance, database, collection, queryParams: { find: {…}, sort: '-customer_id', limit: 10 } })` : `queryParams` takes the same keys, as objects rather than strings. See the [code examples](https://docs.apimaker.dev/v4/examples/sys/db/getAll.html).

## Schema or generated

The schema APIs convert the values of `find` to the types of the schema (`"8"` matches the number 8) and know the relations for `deep`. The generated APIs compare what you send as it is. See [All APIs at a glance](https://docs.apimaker.dev/v4/docs/apis-all/overview.html).