This page View Markdown Open in ChatGPT Open in Claude

find¶

find filters the rows. It takes an object with the MongoDB operators, and API Maker translates it for MySQL, MariaDB, PostgreSQL, SQL Server and Oracle as well, so one filter language serves every database.

As a query param (JSON5)

GET /api/schema/admin/mysql8/inventory/customers?find={first_name:'Bob'}

In the body of the query APIs

{ "find": { "first_name": "Bob" } }

customer_id	first_name	last_name	last_update	pincode	isActive
1	Bob	lin	2022-11-14 04: 34: 58	382345	1
2	Alice	Page	2022-10-15 02: 10: 40	382346	1
3	Mallory	Brown	2022-09-13 03: 44: 05	382347	1
4	Eve	Mathly	2022-11-12 01: 59: 33	382348	1
5	Eve	Page	2022-11-12 01: 59: 33	382349	1

Operators¶

Operator	Example	Rows returned
a value	`{ "customer_id": 2 }`	equal to the value
`$eq`	`{ "customer_id": { "$eq": 2 } }`	equal
`$ne`	`{ "customer_id": { "$ne": 2 } }`	not equal
`$gt`, `$gte`	`{ "customer_id": { "$gt": 2 } }`	greater than, greater or equal
`$lt`, `$lte`	`{ "customer_id": { "$lte": 2 } }`	less than, less or equal
`$in`	`{ "customer_id": { "$in": [ 1, 2 ] } }`	one of the values
`$nin`	`{ "customer_id": { "$nin": [ 2, 3, 4 ] } }`	none of the values
`$and`	`{ "$and": [ { "customer_id": 1 }, { "pincode": 382345 } ] }`	every condition
`$or`	`{ "$or": [ { "customer_id": 1 }, { "pincode": 382345 } ] }`	any condition
`$not`	`{ "customer_id": { "$not": { "$in": [ 1, 2, 3 ] } } }`	the opposite of the operator
`$like`	`{ "first_name": { "$like": "Bob%" } }`	SQL pattern, `%` any text, `_` one character
`$regex`	`{ "first_name": { "$regex": "^Ma" } }`	regular expression
`$isNull`	`{ "last_update": { "$isNull": true } }`	null, or not null with `false` (SQL databases)

Two keys in one object are both required : { "first_name": "Eve", "pincode": 382349 }.
Comparisons work on numbers, strings and dates in ISO format : { "last_update": { "$gte": "2022-11-01T00:00:00.000Z" } }.
$in and $nin need a non empty array.
SQL databases : one operator per field. { "customer_id": { "$gte": 1, "$lte": 9 } } is refused ; write { "$and": [ { "customer_id": { "$gte": 1 } }, { "customer_id": { "$lte": 9 } } ] }.

A key with dots reaches a field of a related table. API Maker reads the related table with its own query API, keeps the ids which match, and filters the main table with them : a join across tables, databases and even instances.

Customers whose orders are paid

{
    "find": { "orders.status": "PAID" },
    "deep": [ { "s_key": "customer_id", "t_col": "orders", "t_key": "customer_id", "isMultiple": true } ]
}

Two levels, with $and and $or

{
    "find": { "$and": [ { "orders.items.qty": { "$gte": 2 } }, { "$or": [ { "orders.status": "PAID" }, { "orders.status": "SHIPPED" } ] } ] }
}

The relation comes from the deep item of the same call, or from the schema of the table (collection, column, database, instance on the field). With the schema, the deep item is not needed for the filter.
The related rows are read with the permissions of the caller : a field or a table the group can not read can not be filtered on.
A virtual field can not be used in a find and join.

Types¶

Schema APIs convert the values to the types of the schema : { "customer_id": "8" } matches the number 8, a date string becomes a date, an id string an ObjectId.
Generated APIs compare what you send : { "customer_id": "8" } looks for the string.

A column as a query param¶

On get all, get by id and the streams, a column name is a filter without find :

?customer_id=1
?first_name=Bob,Alice          # one of them
?pincode>=382345               # also > < <= !=
?first_name=/^ma/i             # regular expression
?first_name=Bob&find={pincode:382345}   # both apply

From code¶

const rows = await g.sys.db.query({
    instance: 'mysql8', database: 'inventory', collection: 'customers',
    find: { pincode: { $gte: 382345 }, 'orders.status': 'PAID' },
});

Security¶

find is powerful : with it a caller reads every row a table has, unless a pre hook narrows it to their own rows. The APIs Security Report tells which tables need that hook and writes it for you. On SQL instances, keys and values which a query builder would paste into the statement (__: prefix, __ helper) can be bound as plain parameters with the switch Block inline SQL in find of the report.