Virtual Fields: One-to-Many Relations Across Databases

Intro

0:00 Deep fills an id with the row it points to. A virtual field goes the other way : the rows of another table that point back here, like the orders of a customer, in one call, even from another database.

The other side of a relation

0:14 The other side of a relation usually means a second query for every parent, or a GROUP BY, then regrouping the rows in code, a glue service when the children live in another database, and the same lookup written again by every caller. With API Maker, one property in the schema names the children, deep fills it, one query for up to a thousand parents, across any two engines, and every caller uses the same name.

0:41 In this video : the declaration, a customer with their orders, three databases in one answer, products with their stock, shaping and nesting, and the rules to know.

The declaration

0:51 In Instance API, postgres, crm, customers, and its schema. The last properties are virtual fields : orders, reviews and tickets. No column holds them. s_columnVirtualLinker names the column of this table, the id, and t_columnVirtualLinker the column of the other table which holds it, customer_id. Instance, database and table name the other table : the orders are in MySQL.

A customer and their orders

1:22 In API Testing, Query for get data on customers. Customer 35, and in deep, orders, with isMultiple true for a list. Send. Gabriel Souza, with three orders from MySQL, though the row of a customer has no column for them.

Three databases, one answer

1:39 Now three virtual fields at once. The reviews are in MongoDB, the support tickets in the same PostgreSQL database. Send. One answer : orders from MySQL, reviews from MongoDB, tickets from PostgreSQL. Three engines, one call, no code.

Products and their stock

1:57 It works from any engine to any other. Two products in MongoDB, with warehouse_stock, sorted by warehouse. Send. Each product gets one row per warehouse, from SQL Server : the Aurora desk lamp has twenty eight in the first warehouse, fifty four in the second.

Shape and nest

2:15 An item of a virtual field takes the keys of a query. Three gold customers. On their orders, the delivered ones, sorted by total, one each, and inside it, its lines : items is a virtual field of orders. Send. Each customer comes with their largest delivered order and its lines. Jonas has no delivered order, so the field is not there.

The rules

2:39 Two rules. First, the select of the item must keep the linker, customer_id. Without it, nothing can be matched, and no orders come back, without an error. Second, a virtual field is not a column, so it can not filter : find on orders dot status is refused, with a 400 that names the field. To filter by a related table, filter the table which holds the reference : the next video.

3:05 How it runs. API Maker reads the parents, collects their ids, and asks the other table once for every thousand parents, with an IN list, then gives each parent its rows. A limit applies to each list after the read. With fetchingTechnique one_by_one, it asks once per parent instead, and the database applies skip and limit : better when each parent has thousands of children. The size of a chunk is a setting too.

Writes and code

3:35 The same names work for writes : master save takes the orders of a customer under orders, and saves them in MySQL, as video nine shows. From code, g.sys.db takes the same deep, typed in eight languages. And the other table is read through its own query API, with the access of the caller, like every deep.

Built on standards

3:57 A relation is declared once, in the schema, which lives in Git. Every caller, and every AI assistant, uses the same name, so code reads the same everywhere. And the answer has the same shape on every engine.

Recap

4:11 To sum up. A virtual field names the rows that point back. deep with isMultiple fills it, across engines. find, sort, limit, select and nesting on every level. Keep the linker in the select, and filter on the side that holds the reference. Next : find and join, to filter by related tables. API Maker. Build faster, spend smarter, deliver better.