# find Parameter

> Filter rows with the find parameter of API Maker - the MongoDB operators $eq, $ne, $gt, $gte, $lt, $lte, $in, $nin, $and, $or, $not, $like, $regex and $isNull on every database, and find and join on fields of related tables.

Source: https://docs.apimaker.dev/v1/docs/apis-all/query-params/find.html

`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)**

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

**In the body of the query APIs**

```json
{ "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 } } ] }`.

## Find and join : fields of related tables

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**

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

**Two levels, with $and and $or**

```json
{
    "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](https://docs.apimaker.dev/v1/docs/schema/schema.html) 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` :

```text
?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

```typescript
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](https://docs.apimaker.dev/v1/docs/apis-all/hooks/preHook-api.html) narrows it to their own rows. The [APIs Security Report](https://docs.apimaker.dev/v1/docs/apis-security/api-security-report.html) 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.