Filter by a Related Table, Even in Another Database: Find and Join

Intro

0:00 Filter rows by a related table, even when it lives in another database : a dot in find follows the relation. In this video, find and join across one, two and three engines.

Filtering by a related table

0:12 Filtering by a related table usually means a JOIN, and SQL can not join two databases, so code loads whole tables and filters them in memory, or a job copies data from one database to another, and every new filter is one more endpoint. With API Maker, a dot in find follows the relation of the schema, across engines, with one query per table, in reads, counts and updates. In this video : one database, two engines, two hops, three engines, how it runs, and counts and updates.

One database

0:46 On order_items, in MySQL, find on order_id dot total, one thousand five hundred or more. order_id refers to orders, so the dot follows it there. Send. The lines of the large orders, filtered by a column of another table.

Two engines

1:03 On orders, in MySQL : the orders of gold customers, customer_id dot loyalty_tier, and the customers are in PostgreSQL. A deep shows each customer. Send. The three largest orders of gold customers, with their customer : one filter across two engines.

Two hops

1:22 Two hops : customer_id dot city_id dot name. Orders, to customers, to cities. Send. The orders of customers who live in Pune.

Three engines

1:34 Three engines. On shipments, in SQL Server : order_id dot handled_by dot FIRST_NAME. Shipments, to orders in MySQL, to employees in Oracle. Send. The nine shipments of the orders Elena handled.

How it runs

1:50 How it runs : the innermost table first. Oracle finds Elena, one row. MySQL finds the orders with that id in handled_by, and SQL Server the shipments of those orders, with an IN list. One query per table, whatever the number of rows. The same filter from code, in a custom API : Shipments by Employee. g.sys.db.query, with the same dotted key. Send, Index Maker, DB Calls : EMPLOYEES, one row, orders, fifteen, shipments, nine.

2:25 Three queries, in that order.

Counts and updates

2:27 It works beyond reads. Count on orders, with the same filter : send, eighteen orders of gold customers. On the support tickets, Update many : the NORMAL tickets of gold customers become HIGH. Send : six rows updated. Read back, they are HIGH now.

Where it works

2:46 Every API which takes find : get all, query, the streams, count, distinct, update many, remove by query, and the first match of an aggregate. It works with deep : filter on one relation, fill another. And for a rare case, the find_join array of the REST APIs names the join itself.

Rules

3:06 The relation comes from the schema, on the schema APIs : on the generated APIs, a dotted key goes to the database as it is. A virtual field can not be used : filter the table which holds the reference. Each hop is a real call of the API of the related table : the token of the caller is checked there, and its hooks run.

Built on standards

3:28 One way to filter by a relation, on every engine. No copy of data between databases, so nothing goes stale. And the access rules of every table hold on every hop.

Recap

3:41 To sum up. A dot in find follows the relation. One database, two engines, three. The innermost table first, one query per table. In counts and updates too. Next : request headers, to shape every answer. API Maker. Build faster, spend smarter, deliver better.