This page View Markdown Open in ChatGPT Open in Claude

deep : deep populate¶

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.

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'}]

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¶

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

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¶

{
    "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

{
    "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¶

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¶

{
    "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 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

state_id: <ISchemaProperty>{ __type: EType.number, instance: 'oracle', database: 'inventory', collection: 'states', column: 'id' },

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 :

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.

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, which combines with deep.