Relations from the schema
The schema says where customer_id points. API Maker cuts the path at every relation and knows which table and database to query at each step.
Database APIs
Find and Join
Filter a table on the fields of its related tables, even when they live in another database.
With schema APIs, a find can follow relations: orders whose customer lives in Surat is one condition, customer_id.city_id.name = "Surat". API Maker reads the last table of the path first and walks the ids back, one $in query per table, across databases and database types.
See it live
Step through it, slow it down, or open the full canvas.
api-maker/features/find-and-joinLive- Tables
- 0joined
- Databases
- 0types
- Ids passed
- –back
- Orders
- –found
Relations live in the schema. The schema of orders says customer_id points to customers.id in MySQL; customers.city_id points to cities in PostgreSQL.
How it works
Relations live in the schema.
The schema of orders says customer_id points to customers.id in MySQL; customers.city_id points to cities in PostgreSQL.
Filter on a field of another table.
One find on orders: customer_id.city_id.name = "Surat". API Maker cuts the path at every relation.
The last table of the path is read first.
cities is queried with name = "Surat" and only its primary key is kept: [7].
The ids walk back, one $in per table.
customers with city_id $in [7] give [12, 15]; orders with customer_id $in [12, 15] are the answer.
One request, three databases, one answer.
The orders of customers living in Surat, although orders, customers and cities are in MongoDB, MySQL and PostgreSQL.
Mix it with everything else.
The same path works inside $and and $or, next to plain conditions, with sort, limit, getTotalCount and deep.
What you get
Innermost table first
The condition runs on the last table of the path and keeps only its keys. Each table before it is then read with an $in of those keys.
No joins in the database
Because each step is its own query, the tables can be in MongoDB, MySQL and PostgreSQL at the same time.
Inside $and and $or
Dotted paths work next to plain conditions and inside $and and $or, with sort, limit, getTotalCount and deep.
On every reading API
Get all, query, both streams, count, distinct and distinct with query, and the writes that take a find: update many and remove by query.
Access checked on each table
The groups of the API user must allow every table of the path. A user without access to customers cannot filter orders by customers.
An example
A sales report by region
Sales wants the paid orders of the customers of one state, while orders are in MongoDB and customers are in a SQL database. One query with customer_id.state_id.name in find answers it, with no reporting database to build.
POST /api/schema/admin/mongodb/shop/orders/queryx-am-authorization: <API user token>{ "find": { "status": "paid", "customer_id.city_id.name": "Surat" }, "sort": { "createdAt": -1 }, "getTotalCount": true}orders.customer_id points to customers in MySQL, customers.city_id to cities in PostgreSQL, as written in the schemas.
1. PostgreSQL · cities name = "Surat" → ids [7]2. MySQL · customers city_id $in [7] → ids [12, 15]3. MongoDB · orders status = "paid", customer_id $in [12, 15] → the answer
Good to know
- Dotted paths that cross tables need schema APIs: the relations come from the schemas of the tables.
- Each table of the path is one query. A condition that matches many rows sends that many keys in the next $in.
Questions
Is this a SQL JOIN?
No. API Maker runs one query per table and passes the keys from one to the next. That is why it also works between different databases and database types.
Can I write the join myself?
Yes. The query body of schema APIs also accepts find_join items with the target table, its find, and where to put the keys it returns.
Can I combine it with deep?
Yes. find decides which rows come back, deep fills in their related rows.