# Query Modifier Guide

## Overview

Query Modifier is a middleware system that automatically transforms incoming query parameters to make them easier to work with in your controllers. This system includes two main components:

1. **API Query Modifier**: Transforms array and boolean query parameters
2. **Query Modifier**: Handles pagination, sorting, and filtering for database queries

## API Query Modifier

### Purpose

The API Query Modifier automatically transforms query parameters based on naming conventions:

- Parameters ending with `[]` are converted to arrays
- Parameters starting with `is_` are converted to booleans

### Usage Examples

#### Array Parameters

```javascript
// Input: ?status[]=active&status[]=inactive&status[]=pending
// Output: { status: ['active', 'inactive', 'pending'] }

// Input: ?ids[]=1&ids[]=2&ids[]=null
// Output: { ids: [1, 2, null] }
```

#### Boolean Parameters

```javascript
// Input: ?is_active=true&is_verified=1&is_deleted=false&is_locked=0
// Output: { is_active: true, is_verified: true, is_deleted: false, is_locked: false }
```

### Implementation

```typescript
import { apiQueryModifier } from "./middlewares/mod/api-query-modifier";

// In server.ts
app.use(apiQueryModifier());
```

## Query Modifier

### Purpose

The Query Modifier processes complex query parameters for database operations, including:

- Pagination (page, pageSize)
- Sorting (sortField, sortOrder)
- Filtering (filters with Sequelize conditions)

### Query Parameters

#### Pagination

```javascript
// ?page=2&pageSize=20
{
    page: 2,
    pageSize: 20
}
```

#### Sorting

```javascript
// ?sortField=created_at&sortOrder=DESC
{
    sortField: 'created_at',
    sortOrder: 'DESC'
}
```

#### Filtering

```javascript
// ?filters=field1:value1,field2:value2,field3|>10
{
    filters: {
        [Op.and]: [
            { field1: { [Op.eq]: 'value1' } },
            { field2: { [Op.eq]: 'value2' } },
            { field3: { [Op.gt]: 10 } }
        ]
    }
}
```

### Filter Syntax

#### Basic Filters

- `field:value` - Equal to
- `field|>value` - Greater than
- `field|<value` - Less than
- `field|>=value` - Greater than or equal
- `field|<=value` - Less than or equal
- `field|!=value` - Not equal
- `field|%value%` - Like (contains)
- `field|%value` - Like (ends with)
- `field|value%` - Like (starts with)

#### OR Conditions

- `field1:value1|field2:value2` - OR condition

### Usage in Controllers

```typescript
import queryModifier from "./middlewares/query-modifier";

export const getUsers = [
	queryModifier, // Apply query modifier
	async (req: Req, res: Response) => {
		const { payload } = req; // Contains processed query parameters

		const result = await UserProvider.findAll({
			page: payload.page,
			pageSize: payload.pageSize,
			sortField: payload.sortField,
			sortOrder: payload.sortOrder,
			filters: payload.filters,
		});

		return res.sendOk({
			data: {
				rows: result.users,
				count: result.total,
				page: result.page,
				pageSize: result.limit,
			},
		});
	},
];
```

## Middleware Order

In `server.ts`, middlewares should be applied in this order:

```typescript
app.use(responseTemplate, apiQueryModifier(), queryModifier);
```

1. **responseTemplate**: Adds response methods (sendOk, error, etc.)
2. **apiQueryModifier**: Transforms query parameter types
3. **queryModifier**: Processes pagination/filtering parameters

## Benefits

1. **Automatic Type Conversion**: No manual parsing of arrays and booleans
2. **Consistent Filtering**: Standardized filter syntax across all endpoints
3. **Pagination Support**: Built-in pagination handling
4. **Database Agnostic**: Works with any database through Sequelize
5. **Client Friendly**: Simple query parameters for complex operations

## Examples

### Complete Controller Example

```typescript
import { Request, Response } from "express";
import { Req } from "#interfaces/IApi";
import queryModifier from "#middlewares/query-modifier";

export const getUsers = [
	queryModifier,
	async (req: Req, res: Response) => {
		try {
			const { payload } = req;

			// payload contains:
			// - page, pageSize (numbers)
			// - sortField, sortOrder (strings)
			// - filters (Sequelize where conditions)
			// - dateField (array for date filtering)

			const users = await User.findAll({
				where: payload.filters,
				limit: payload.pageSize,
				offset: (payload.page - 1) * payload.pageSize,
				order: payload.sortField ? [[payload.sortField, payload.sortOrder]] : undefined,
			});

			const total = await User.count({ where: payload.filters });

			return res.sendOk({
				data: {
					rows: users,
					count: total,
					page: payload.page,
					pageSize: payload.pageSize,
				},
			});
		} catch (error) {
			res.error(error);
		}
	},
];
```

### Client Usage Examples

```javascript
// Get users with pagination
GET /api/users?page=1&pageSize=10

// Get users with sorting
GET /api/users?sortField=name&sortOrder=ASC

// Get users with filtering
GET /api/users?filters=status:active,role:admin

// Get users with array parameters
GET /api/users?roles[]=admin&roles[]=user&is_active=true

// Complex filtering
GET /api/users?filters=created_at|>2024-01-01,status:active|status:pending
```

## Configuration

### Default Values

- `page`: 1
- `pageSize`: 10
- `sortOrder`: null (no sorting)

### Limits

- `pageSize` max: 100 (configurable in middleware)

## Error Handling

The middleware automatically handles:

- Invalid page/pageSize values (defaults to valid values)
- Malformed filter strings (skips invalid filters)
- Type conversion errors (graceful fallbacks)

## Related Files

- `src/middlewares/mod/api-query-modifier.ts` - API query parameter transformation
- `src/middlewares/query-modifier.ts` - Database query processing
- `src/services/database/build-condition.ts` - Filter condition building
- `src/interfaces/IApi.ts` - Type definitions
