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 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)

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

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

{
    "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',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 :

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¶

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