This page View Markdown Open in ChatGPT Open in Claude

Remove by query¶

Deletes every row that find matches, and answers how many rows went. With returnData: true, it answers the removed rows and their ids too.

In short

POST /api/schema/<user-path>/<instance>/<database>/<table>/query/delete
Body : { "find": {…} }. The answer is { deletedRowsCount }.
A missing or empty find is refused. So no call deletes a whole table by mistake.


Method	POST
URL	`/api/schema/admin/mysql8/inventory/customers/query/delete`
Body	`{ find, returnData?, select?, deep? }`
Query params	none
Answer	`data` : `{ deletedRowsCount }`. With `returnData: true` : `{ deletedRows, deletedRowsCount, ids }`.
Databases	MongoDB, MySQL, MariaDB, PostgreSQL, SQL Server, Oracle, TiDB, Percona
Cached	no. It clears the cached answers of the table.
From code	`g.sys.db.removeByQuery`
API id	`SCHEMA_POST_QUERY_DELETE`
Without a schema	`/api/gen/admin/mysql8/inventory/customers/query/delete` : below

The examples change the table of get all.

The call¶

POST /api/schema/admin/mysql8/inventory/customers/query/delete

Body

{ "find": { "customer_id": { "$in": [ 4, 5 ] } } }

Answer

{
    "success": true,
    "statusCode": 200,
    "data": { "deletedRowsCount": 2 }
}

find takes every operator of find, keys of related tables included.
The rows are not read : the answer only says how many went. Removing a million rows costs the delete, nothing more.

The removed rows : returnData¶

Body

{ "find": { "customer_id": 5 }, "returnData": true }

Answer

{
    "success": true,
    "statusCode": 200,
    "data": {
        "deletedRows": [ { "customer_id": 5, "first_name": "Eve", "last_name": "Page", "last_update": "2022-11-12T01:59:33.000Z", "pincode": 382349, "isActive": 1 } ],
        "deletedRowsCount": 1,
        "ids": [ 5 ]
    }
}

The rows are read before the delete, so deletedRows holds them as they were. select keeps some of their columns. deep adds their related rows.
Every matching row is held in the memory of the server, or of the sandbox for code. So ask for the rows only when you use them.

More filters¶

Two conditions

{ "find": { "$and": [ { "isActive": 0 }, { "last_update": { "$lt": "2022-01-01T00:00:00.000Z" } } ] } }

A related table : the orders of one customer (orders.customer_id refers to customers)

{ "find": { "customer_id.email": "[email protected]" } }

No find, no delete¶

A body without find, or with "find": {}, deletes nothing. The answer is 500 :

{ "success": false, "statusCode": 500, "errors": [ { "message": "Please provide 'find' property having query to remove data.", "field": null, "type": null, "value": { "find": {} } } ] }

To empty a table on purpose, use a filter that every row matches, like { "customer_id": { "$gte": 0 } }.

After the call¶

The cached answers of the table are cleared, and WebSocket subscribers get the news once. Post hooks get the answer in g.res.output. It holds the rows only with returnData: true.

From your code¶

g.sys.db.removeByQuery makes the same call from custom APIs, hooks, events, schedulers and test cases. It returns { deletedRowsCount }. Add returnData: true next to find for the rows.

Without a schema : /api/gen¶

Every table also answers this operation under /api/gen, with or without a schema :


URL	`/api/gen/admin/mysql8/inventory/customers/query/delete`
API id	`GEN_POST_QUERY_DELETE`
From code	`g.sys.db.gen.removeByQueryGen`

find matches the type you send. On MongoDB, a text that looks like an ObjectId is turned into one. On SQL without a schema, API Maker knows no primary key : with returnData: true, ids holds the removed rows themselves.

Headers and errors¶

This operation takes the request headers of every API. It answers the envelope, with the status codes and the messages of every API.

Related : All APIs · Table schema · Pre hooks · Post hooks · API group permission