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

Source: https://docs.apimaker.dev/v4/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 each related table once for all the parent rows : one query per deep item, whatever their number (for a virtual field, one per 1000 parents).

**Query param (JSON5)**

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

**Body of the query APIs**

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

> Diagram : deep : one query per level, for all the parents at once
>
> deep on a get all of cities reads the cities, then the states of all those cities in one query with $in, then the countries of all those states in one more query, even when the tables are on other instances, and nests each row in its parent.

## 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" } ] }
```

**Answer**

```json
{
    "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" } ]
    } ]
}
```

**Answer**

```json
{
    "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 deep item is one query on its table, for all the parents at once.

## One to many

**Every order of each customer, the latest 5, paid ones only**

```json
{
    "find": {},
    "deep": [ { "s_key": "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.
- The `id` of each customer is replaced by its orders. To keep it and add the orders beside it, declare a virtual field (below).

## 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](https://docs.apimaker.dev/v4/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 :

**cities.schema.ts**

```typescript
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',isMultiple:true}]` on a customer fills its orders from the `orders` table, without a column on the customer. Without `isMultiple` it holds one row. A `select` of the item must keep the column `t_columnVirtualLinker` names, or no row can be matched.
- The generated APIs (`/api/gen`) do not read the schema : give `t_col` and `t_key` in every deep item. There, `deep` works from a MongoDB collection ; from a SQL table, call the schema APIs (`/api/schema`), the generated ones answer `Unable to find collection`.

## 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](https://docs.apimaker.dev/v4/docs/apis-all/header/requestHeader.html#x-am-response-object-type).

## Good to know

- The related rows are read through the query API of their table : the columns the group of the caller may not read are removed, and a table it may not call makes the item fail (`errors`).
- For a request of a person or an application, they are read as that caller reads them : the pre and post hooks of the query API of their table run, as for a call of that API. A hook which keeps a person to their own rows keeps them there in a `deep` too, and a table whose settings say `NO_ACCESS` is not read. API Maker version 3 read them without these hooks.
- A `deep` asked by code (`g.sys.db`, in a custom API, a hook, a scheduler ...) follows the code : the hooks of the related table run only when the call passes `skipHookRunning: false`, as for the table it calls.
- A `deep` item which finds no table, or names a column the table does not have, answers 400 : `errors` names it, and `data` still holds the rows. On writes, those errors are dropped.
- Filtering the parent rows by a field of a related table is a different feature, [find and join](https://docs.apimaker.dev/v4/docs/apis-all/query-params/find.html#find-and-join-fields-of-related-tables), which combines with `deep`.