# deep Parameter (Deep Populate)

> Replace ids with the rows they point to with the deep parameter of API Maker - from any table, database or instance, several levels deep, with a filter, a sort, a limit and a selection per level, one query per level.

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

`deep` replaces an id with the row it points to. The related table can be in the same database, in another one, or on another instance of another kind : a MongoDB collection populated from a PostgreSQL table works. API Maker reads the related rows with one query per level, whatever the number of parent rows.

```text title="Query param (JSON5)"
GET /api/schema/admin/postgresql/inventory/cities?deep=[{s_key:'state_id',t_instance:'oracle',t_db:'inventory',t_col:'states',t_key:'id'}]
```

```json title="Body of the query APIs"
{ "find": {}, "deep": [ { "s_key": "state_id", "t_instance": "oracle", "t_db": "inventory", "t_col": "states", "t_key": "id" } ] }
```

## The tables of the examples

| cities (PostgreSQL) | | | states (Oracle) | | | countries (MySQL) | |
|---|---|---|---|---|---|---|---|
| id | state_id | city_name | id | country_id | state_name | id | country_name |
| 101 | 201 | AHMEDABAD | 201 | 301 | GUJARAT | 301 | INDIA |
| 105 | 202 | KOCHI | 202 | 301 | KERALA | 302 | USA |
| 110 | 204 | DUNCAN | 204 | 302 | ARIZONA | | |

## One level

```json
{ "find": { "id": 101 }, "deep": [ { "s_key": "state_id", "t_instance": "oracle", "t_db": "inventory", "t_col": "states", "t_key": "id" } ] }
```

```json title="Answer"
{
    "success": true,
    "statusCode": 200,
    "data": [ { "id": 101, "city_name": "AHMEDABAD", "state_id": { "id": 201, "country_id": 301, "state_name": "GUJARAT" } } ]
}
```

## The keys of a deep item

| Key | Meaning |
|---|---|
| `s_key` | The field of the current table which holds the id (the **source key**). Required. |
| `t_col` | The related table or collection (the **target**). Not needed when the schema gives it. |
| `t_key` | The field of the related table to match. Its primary key when absent. |
| `t_db`, `t_instance` | The database and the instance of the related table. The ones of the current table when absent. |
| `isMultiple` | `true` : several related rows are expected, the field becomes an array. `false` (default) : one row, the field becomes an object. |
| `find` | A filter on the related rows : `{ "status": "PAID" }`. |
| `select` | The fields of the related rows to keep. |
| `sort`, `skip`, `limit` | Order and paging of the related rows, per parent row. |
| `deep` | The next level : an array of deep items on the related table. |
| `fetchingTechnique` | For virtual fields : `chunk` (default, 1000 ids per query) or `one_by_one` (one query per parent, needed with `skip` and `limit` on big sets). `fetchingTechniqueSettings.chunkSize` changes the chunk. |

## Several levels

```json
{
    "find": {},
    "limit": 1,
    "deep": [ {
        "s_key": "state_id", "t_instance": "oracle", "t_db": "inventory", "t_col": "states", "t_key": "id",
        "deep": [ { "s_key": "country_id", "t_instance": "mysql", "t_db": "inventory", "t_col": "countries", "t_key": "id" } ]
    } ]
}
```

```json title="Answer"
{
    "success": true,
    "statusCode": 200,
    "data": [ {
        "id": 101,
        "city_name": "AHMEDABAD",
        "state_id": { "id": 201, "state_name": "GUJARAT", "country_id": { "id": 301, "country_name": "INDIA" } }
    } ]
}
```

- As many levels as you need. Each level is one query on its table, for all the parents at once.

## One to many

```json title="Every order of each customer, the latest 5, paid ones only"
{
    "find": {},
    "deep": [ { "s_key": "customer_id", "t_col": "orders", "t_key": "customer_id", "isMultiple": true, "find": { "status": "PAID" }, "sort": "-created_at", "limit": 5, "select": "order_no,total" } ]
}
```

- `isMultiple: true` matches every related row and gives an array.

## Two relations at once

```json
{
    "deep": [
        { "s_key": "state_id", "t_col": "states", "t_key": "id" },
        { "s_key": "created_by", "t_col": "users", "t_key": "id", "select": "name" }
    ]
}
```

## With the schema : just the source key

When the [schema](/v1/docs/schema/schema.html) of the table names the relation on the field (`collection` or `table`, `column`, and `database`, `instance` when they differ), the deep item only needs `s_key`, and the target is never wrong twice :

```typescript title="cities.schema.ts"
state_id: <ISchemaProperty>{ __type: EType.number, instance: 'oracle', database: 'inventory', collection: 'states', column: 'id' },
```

```text
GET /api/schema/admin/postgresql/inventory/cities?deep=[{s_key:'state_id'}]
GET /api/schema/admin/postgresql/inventory/cities?deep=[{s_key:'state_id',deep:[{s_key:'country_id'}]}]
```

- A **virtual field** (`isVirtualField` with `s_columnVirtualLinker` and `t_columnVirtualLinker`) is a one to many relation stored on the other side : `deep=[{s_key:'orders'}]` on a customer fills its orders from the `orders` table, without a column on the customer.
- The generated APIs (`/api/gen`) do not read the schema : give `t_col` and `t_key` in every deep item.

## On writes

`deep` on save single or multiple, master save, update by id, replace by id and remove by id populates the row of the answer :

```text
POST /api/schema/admin/mysql8/inventory/cities/save-single-or-multiple?deep=[{s_key:'state_id'}]
```

## Flat answers

The header `x-am-response-object-type: make_flat` turns the nested objects into one level with `_` between the names : `state_id_country_id_country_name`. See [request headers](/v1/docs/apis-all/header/requestHeader.html#x-am-response-object-type).

## Good to know

- A related row the caller may not read (group permission) is left out, and a `deep` item which finds no table gives a `warning` in the answer, not an error.
- Filtering the parent rows by a field of a related table is a different feature, [find and join](/v1/docs/apis-all/query-params/find.html#find-and-join-fields-of-related-tables), which combines with `deep`.