Delete Query

Documentation for `deleteQuery`

The deleteQuery function is a utility to generate a MySQL DELETE query based on the provided parameters. It supports advanced features such as joins, sorting, and limiting rows, ensuring developers can build dynamic queries with ease.

import {deleteQuery} from "agile-query"

Function Definition

function deleteQuery<Tables extends string[]>({
    table,
    where,
    joins,
    limit,
    sort,
}: DeleteQueryParams<Tables>): string;

Parameters

Parameter	Type	Description	Required
`table`	`string`	The name of the table from which rows will be deleted.	✅
`where`	`string`	The condition to filter rows for deletion (`WHERE` clause).	✅
`joins`	`JoinsType`	Array of `JOIN` clauses to include other tables in the deletion logic.	❌
`limit`	`string` \| `number`	The maximum number of rows to delete.	❌
`sort`	`Record<string, 1 \| -1>` \| `string`	Specifies the sorting order of rows (`ORDER BY` clause). Use `1` for ascending and `-1` for descending.	❌

JoinsType

Property	Type	Description
`type`	`'JOIN'` \| `'INNER JOIN'` \| `'OUTER JOIN'` \| `'CROSS JOIN'` \| `'RIGHT JOIN'` \| `'LEFT JOIN'`	Type of `JOIN` to perform.
`operator`	`string`	The comparison operator for the join condition. Default is `=`.
`[key]`	`string`	Keys represent table and column names for joining conditions. Example: `{ customer: 'customer_id', order: 'customer_id' }`

Return Value

Type: string
Description: A fully-formed MySQL DELETE query string.

Example Usage

const query = deleteQuery<['user', 'order', 'customer']>({
    table: 'orders',                         // Name of the table to delete rows from
    where: 'status = "pending"',             // Condition for deleting rows (WHERE clause)
    sort: {
        order_date: -1                       // Sort by `order_date` in descending order
    },
    limit: 10,                               // Limit the delete operation to 10 rows
    joins: [                                 // JOIN operations to filter rows for deletion
        {
            type: 'INNER JOIN',              // INNER JOIN with another table
            operator: '=',                   // Comparison operator (default is '=')
            customer: 'customer_id',         // Primary table and column
            order: 'customer_id',            // Foreign table and column (foreign key)
        },
    ]
});

console.log(query);

Output

DELETE orders FROM orders INNER JOIN customer ON customer.customer_id = order.customer_id WHERE status = "pending" ORDER BY order_date DESC LIMIT 10;

Detailed Breakdown

table: Specifies the orders table as the main target for the delete operation.
where: Filters rows to delete where status = "pending".
sort: Orders rows by order_date in descending order (ORDER BY order_date DESC).
- Example:
  - { name: 1 } (sort by name in ascending order)
  - { users: { age: -1 } } (sort age in descending order for the users table)
limit: Limits the deletion to the first 10 rows (LIMIT 10).
joins:
- Adds an INNER JOIN between the orders table and the customer table.
- Joins on the condition customer.customer_id = order.customer_id.

when use on relation then use table parameter or user column name.

    [
        { type: 'INNER JOIN', table: 'customers', on: 'orders.customer_id = customers.id' },
        { type: 'LEFT JOIN', table: 'payments', on: 'orders.id = payments.order_id' }
    ]

   joins: [                                 // JOIN operations to filter rows for deletion
       {
           type: 'INNER JOIN',
           operator: '=', // DEFAULT '='
           customer: 'customer_id', // First table name is primary table 
           order: 'customer_id', // Second table name with column name is foreign table and foreign key
       },
   ]

Error Handling

Throws an error if:

table is not provided:

⚠️ The `table` parameter is required.

where is not provided:

⚠️ The `where` parameter is required.

Notes

Use the joins property to ensure proper filtering when multiple tables are involved in the delete operation.
The sort parameter accepts both simple string columns and a detailed object for multi-column sorting.

`JoinsType` (for `joins` parameter)

The joins parameter accepts an array of objects, where each object defines a join between the main table (specified by table) and another table. Each object can have the following structure:

type ('JOIN' | 'INNER JOIN' | 'OUTER JOIN' | 'CROSS JOIN' | 'RIGHT JOIN' | 'LEFT JOIN', optional)
- Description: The type of join. It can be any standard SQL join type:
  - 'INNER JOIN': Selects records that have matching values in both tables.
  - 'LEFT JOIN': Selects all records from the left table, and the matched records from the right table.
  - 'RIGHT JOIN': Selects all records from the right table, and the matched records from the left table.
  - 'CROSS JOIN': Returns the Cartesian product of the two tables.
  - 'OUTER JOIN': A more generic term often used for LEFT OUTER JOIN or RIGHT OUTER JOIN.
- Example: 'INNER JOIN'
table (string, optional)
- Description: The name of the table to join with.
- Required: Yes, when a type is provided.
- Example: 'customers'
on (string, optional)
- Description: The condition for the join, specifying how the two tables should be related.
- Required: Yes, when a type is provided.
- Example: 'orders.customer_id = customers.id'

Example of Full `deleteQuery` Call with Parameter Descriptions

const query = deleteQuery({
    table: 'orders',                         // Name of the table to delete rows from
    where: 'status = "pending"',             // Condition for deleting rows (WHERE clause)
    sort: { order_date: -1 },                // Sort by `order_date` in descending order
    limit: 10,                               // Limit the delete operation to 10 rows
    joins: [                                 // JOIN operations to filter rows for deletion
        { type: 'INNER JOIN', table: 'customers', on: 'orders.customer_id = customers.id' }
    ]
});
console.log(query);
// Output: DELETE orders FROM orders INNER JOIN customers ON orders.customer_id = customers.id WHERE status = "pending" ORDER BY order_date DESC LIMIT 10;

Error Handling for Missing Parameters

If the table or where parameters are not provided, the function will throw an error.
- Example:

    deleteQuery({
        table: '',          // Missing table name
        where: 'age > 60'   // Valid condition
    }); // Error: "The `table` parameter is required."

1. Simple DELETE with WHERE Condition

Delete rows from the employees table where the age is greater than 60:

const query = deleteQuery({
    table: 'employees',
    where: 'age > 60',
    limit: 10
});
console.log(query);
// Output: DELETE employees FROM employees WHERE age > 60 LIMIT 10;

2. DELETE with INNER JOIN

Delete rows from the orders table where the status is "pending", and join with the customers table to add conditions based on customer data:

const query = deleteQuery({
    table: 'orders',
    where: 'status = "pending"',
    joins: [
        { type: 'INNER JOIN', table: 'customers', on: 'orders.customer_id = customers.id' }
    ]
});
console.log(query);
// Output: DELETE orders FROM orders INNER JOIN customers ON orders.customer_id = customers.id WHERE status = "pending";

3. DELETE with LEFT JOIN

Delete rows from the products table where the price is lower than 100, while joining with the categories table using a LEFT JOIN:

const query = deleteQuery({
    table: 'products',
    where: 'price < 100',
    joins: [
        { type: 'LEFT JOIN', table: 'categories', on: 'products.category_id = categories.id' }
    ]
});
console.log(query);
// Output: DELETE products FROM products LEFT JOIN categories ON products.category_id = categories.id WHERE price < 100;

4. DELETE with Sorting

Delete rows from the orders table where the status is "shipped", and sort the rows by order_date in descending order:

const query = deleteQuery({
    table: 'orders',
    where: 'status = "shipped"',
    sort: { order_date: -1 }
});
console.log(query);
// Output: DELETE orders FROM orders WHERE status = "shipped" ORDER BY order_date DESC;

const query = deleteQuery<['user', 'order', 'customer']>({
    table: 'orders',                         // Name of the table to delete rows from
    where: 'status = "pending"',             // Condition for deleting rows (WHERE clause)
    sort: {
        order: {
            order_date: -1
        }
    },                // Sort by `order_date` in descending order
    limit: 10,                               // Limit the delete operation to 10 rows
    joins: [                                 // JOIN operations to filter rows for deletion
        {
            type: 'INNER JOIN',
            operator: '=', // DEFAULT '='
            customer: 'customer_id', // First table name is primary table 
            order: 'customer_id', // Second table name with column name is foreign table and foreign key
        },
    ]
});

console.log(query)

//DELETE orders FROM orders INNER JOIN order ON customer.customer_id = order.customer_id WHERE status = "pending" ORDER BY order.order_date DESC LIMIT 10

5. DELETE with LIMIT

Delete only 5 rows from the products table where the stock is 0:

const query = deleteQuery({
    table: 'products',
    where: 'stock = 0',
    limit: 5
});
console.log(query);
// Output: DELETE products FROM products WHERE stock = 0 LIMIT 5;

6. DELETE with Multiple Joins

Delete rows from the orders table where the status is "pending", and join with both customers and products tables:

const query = deleteQuery({
    table: 'orders',
    where: 'status = "pending"',
    joins: [
        { type: 'INNER JOIN', table: 'customers', on: 'orders.customer_id = customers.id' },
        { type: 'LEFT JOIN', table: 'products', on: 'orders.product_id = products.id' }
    ]
});
console.log(query);
// Output: DELETE orders FROM orders INNER JOIN customers ON orders.customer_id = customers.id LEFT JOIN products ON orders.product_id = products.id WHERE status = "pending";

7. DELETE with WHERE, Sorting, and LIMIT

Delete rows from the employees table where the position is "Intern", sort by hire_date ascending, and limit to 10 rows:

const query = deleteQuery({
    table: 'employees',
    where: 'position = "Intern"',
    sort: { hire_date: 1 },
    limit: 10
});
console.log(query);
// Output: DELETE employees FROM employees WHERE position = "Intern" ORDER BY hire_date ASC LIMIT 10;

8. DELETE with WHERE and Complex Join

Delete rows from the employees table where the salary is greater than 50000, and join with departments and projects tables:

const query = deleteQuery({
    table: 'employees',
    where: 'salary > 50000',
    joins: [
        { type: 'INNER JOIN', table: 'departments', on: 'employees.department_id = departments.id' },
        { type: 'LEFT JOIN', table: 'projects', on: 'employees.project_id = projects.id' }
    ]
});
console.log(query);
// Output: DELETE employees FROM employees INNER JOIN departments ON employees.department_id = departments.id LEFT JOIN projects ON employees.project_id = projects.id WHERE salary > 50000;

9. DELETE with RIGHT JOIN

Delete rows from the orders table where the status is "cancelled", and use a RIGHT JOIN with the payments table to identify orders with no payment:

const query = deleteQuery({
    table: 'orders',
    where: 'status = "cancelled"',
    joins: [
        { type: 'RIGHT JOIN', table: 'payments', on: 'orders.id = payments.order_id' }
    ]
});
console.log(query);
// Output: DELETE orders FROM orders RIGHT JOIN payments ON orders.id = payments.order_id WHERE status = "cancelled";

10. DELETE with Multiple Sorting Criteria

Delete rows from the products table where stock is less than 10, and sort by price in ascending order and name in descending order:

const query = deleteQuery({
    table: 'products',
    where: 'stock < 10',
    sort: { price: 1, name: -1 }
});
console.log(query);
// Output: DELETE products FROM products WHERE stock < 10 ORDER BY price ASC, name DESC;