Skip to content
This page View Markdown Open in ChatGPT Open in Claude

deep : deep populate

A column such as state_id holds an id. With deep, the answer holds the row of that id instead, in the same call.

In short

  • Name the column : ?deep=state_id. The schema knows where it points.
  • No relation in the schema ? Name the table in an item : t_col and t_key.
  • A deep inside an item goes one level further.
  • API Maker reads related rows in bulk, never with one query per row. They can be on another database.
deep : one query per level, for all the parents at once GET …/cities?deep=[{ s_key: 'state_id', deep: [{ s_key: 'country_id' }] }] query 1 · cities 101 Ahmedabad → 201 102 Surat → 201 103 Pune → 202 PostgreSQL query 2 · states id $in [201, 202] 201 Gujarat → 301 202 Maharashtra → 301 Oracle query 3 · countries id $in [301] 301 India MySQL the answer { city_name,   state_id: { …,   country_id: {…} }} Three tables, three queries : never one query per row. Each level takes its own find, select, sort and limit, and can be on another database or instance. With the relations in the schema, s_key alone is enough.
Deep Populate: Join Data Across Six Databases in One API Call9 chapters and the transcript

The tables of the examples

Table Instance Columns Rows
cities postgresql id, city_name, state_id 101 Ahmedabad → 201, 102 Surat → 201, 103 Pune → 202
states oracle id, state_name, country_id 201 Gujarat → 301, 202 Maharashtra → 301
countries mysql id, country_name 301 India

Each instance has a database named geo. The schemas name the relations :

The relations
// cities.schema.ts : state_id points to states.id, on the instance oracle
state_id: <ISchemaProperty>{ __type: EType.number, instance: 'oracle', table: 'states', column: 'id' },

// states.schema.ts : country_id points to countries.id, on the instance mysql
country_id: <ISchemaProperty>{ __type: EType.number, instance: 'mysql', table: 'countries', column: 'id' },

Name the column

GET /api/schema/admin/postgresql/geo/cities?deep=state_id
Answer
{
    "success": true,
    "statusCode": 200,
    "data": [
        { "id": 101, "city_name": "Ahmedabad", "state_id": { "id": 201, "state_name": "Gujarat", "country_id": 301 } },
        { "id": 102, "city_name": "Surat", "state_id": { "id": 201, "state_name": "Gujarat", "country_id": 301 } },
        { "id": 103, "city_name": "Pune", "state_id": { "id": 202, "state_name": "Maharashtra", "country_id": 301 } }
    ]
}

In the body of the query API, the same is { "deep": ["state_id"] }.

Name the table

Without a relation in the schema, an item says where the id points. s is the source, this table. t is the target, the related table.

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

The answer is the same as above.

Key What it says When you leave it out
s_key The column of this table which holds the id. Required.
t_col The related table. The schema says it.
t_key The column of the related table which holds the same value. The schema says it, else the primary key of the related table.
t_db The database of the related table. The schema says it, else the database of this table.
t_instance The instance of the related table. The schema says it, else the instance of this table.

What an item says wins over the schema.

Go deeper

A deep inside an item fills the rows that item brought. This is the call of the diagram :

GET /api/schema/admin/postgresql/geo/cities?deep=[{s_key:'state_id',deep:[{s_key:'country_id'}]}]
First row of 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.
  • Short form : ?deep=state_id,state_id.country_id. Order matters : state_id.country_id reads the rows state_id brought, so it comes second.

An item takes its own find, select and sort, like a query on its table :

GET /api/schema/admin/postgresql/geo/cities?deep=[{s_key:'state_id',find:{state_name:'Gujarat'},select:'state_name'}]
data
[
    { "id": 101, "city_name": "Ahmedabad", "state_id": { "id": 201, "state_name": "Gujarat" } },
    { "id": 102, "city_name": "Surat", "state_id": { "id": 201, "state_name": "Gujarat" } },
    { "id": 103, "city_name": "Pune", "state_id": 202 }
]
  • select keeps only these columns, plus id, the column the rows are matched by.
  • A state which find leaves out is not put in : Pune keeps state_id: 202.

One to many : isMultiple

isMultiple: true puts all the matching rows, as a list. Here, the cities of each state :

GET /api/schema/admin/oracle/geo/states?deep=[{s_key:'id',t_instance:'postgresql',t_col:'cities',t_key:'state_id',isMultiple:true}]
First row of data
{
    "id": [
        { "id": 101, "city_name": "Ahmedabad", "state_id": 201 },
        { "id": 102, "city_name": "Surat", "state_id": 201 }
    ],
    "state_name": "Gujarat",
    "country_id": 301
}
  • Without isMultiple, only one city comes.
  • sort, skip and limit of the item work on the list of each state : limit:1 keeps one city per state.
  • The list takes the place of the id of the state. To keep the id, use a virtual field.

Virtual fields

A virtual field is in the schema only, not in the table. It stands for the rows of another table which point to this row :

states.schema.ts
cities: <ISchemaProperty>{ __type: EType.number, isVirtualField: true, instance: 'postgresql', table: 'cities', s_columnVirtualLinker: 'id', t_columnVirtualLinker: 'state_id' },

GET /api/schema/admin/oracle/geo/states?deep=[{s_key:'cities',isMultiple:true}]
First row of data
{
    "id": 201,
    "state_name": "Gujarat",
    "country_id": 301,
    "cities": [
        { "id": 101, "city_name": "Ahmedabad", "state_id": 201 },
        { "id": 102, "city_name": "Surat", "state_id": 201 }
    ]
}
  • A state without cities gets no cities field.
  • By default the states share one query, split only as in Speed below. fetchingTechniqueSettings: { chunkSize: 100 } asks for 100 states per query.
  • fetchingTechnique: 'one_by_one' asks one query per state, so the database itself applies limit. Use it with sort to keep a few rows out of many : the 5 latest orders of each customer.

Good to know

  • Where it works. Get all, get by id, query, and the two stream APIs. The write APIs take deep too (save single or multiple, master save, update by id, replace by id, remove by id) : POST …/cities/save-single-or-multiple?deep=state_id answers the saved row with its state. Nothing more is saved.
  • Speed. API Maker asks each related table one query for all the rows. It splits the ids into more queries only when one statement can not hold them : 1,000 on Oracle, 2,000 on SQL Server, 60,000 on MySQL, MariaDB and PostgreSQL. Items on different columns are read at the same time.
  • No row found. An id without a related row stays as it is, even with isMultiple. So does an empty value. A list of ids becomes the list of the rows found.
  • Security. The related rows are read through the query API of their table, as the caller would read them. Columns the caller may not read are left out, and a table they may not read makes the item fail. The pre and post hooks of that API run, so a hook which keeps a person to their own rows does it in a deep too. API Maker 3 skipped these hooks. A deep from code (g.sys.db) runs them only with skipHookRunning: false.
  • Errors. On a read, an item with an unknown column or table answers 400. errors names it, and data still holds the rows. See the error codes.
  • Generated APIs (/api/gen) read no schema : every item needs its t_col, and t_key is _id when left out. There, deep works from a MongoDB collection only. For an SQL table, call the schema APIs.
  • Flat rows for a grid or a CSV file : the header x-am-response-object-type: make_flat turns state_id: { state_name } into state_id_state_name.
  • Filter by a related column, such as the cities of Gujarat : that is find and join, not deep. Both work in the same call.
  • From code, deep takes the same items : examples in every language.