# Nested Filters Utility

## Overview

`NestedFilterUtils` is a utility class for handling nested filters in Sequelize queries. It's
designed for scenarios where you need to filter a model based on fields from related tables or
database views.

## Use Case

When filtering a main model (e.g., `residents`) based on fields from a related table/view (e.g.,
`resident_current_locations`), the default query modifier middleware doesn't support nested filter
keys like `residence.room_id`. This utility solves that problem by:

1. **Separating filters** into target (main table) and related (nested) filters
2. **Building WHERE clauses** for the related table/view query
3. **Rebuilding filter objects** for the main table query

## Example Scenario

### Problem

You want to filter residents by their current location:

```
GET /api/v1/residents?filters=residence.room_id==uuid-123
```

Without this utility, the query would fail because:

- The `residents` table doesn't have a `residence.room_id` column
- The query modifier passes the filter directly to the main table query
- SQL error: `missing FROM-clause entry for table "residence"`

### Solution

With `NestedFilterUtils`, the filter is:

1. Extracted as a "related filter" for `resident_current_locations` view
2. Applied to the view query to get matching `resident_id`s
3. Those IDs are used to filter the main `residents` query

## Usage

### Basic Usage

```typescript
import { NestedFilterUtils } from '#utils/nestedFilterUtils';

// Define fields that belong to the related table/view
const residenceFields = ['room_id', 'apartment_id', 'building_id', 'bed_id'];

// Separate filters
const { targetFilters, relatedFilters, whereClause, replacements } =
	NestedFilterUtils.separateFilters(options, residenceFields, 'residence.');

// Query related table/view with extracted filters
const locations = await sequelize.query(
	`SELECT DISTINCT resident_id FROM resident_current_locations WHERE 1=1${whereClause}`,
	{ replacements, type: QueryTypes.SELECT },
);

const filteredIds = locations.map((l) => l.resident_id);

// Apply ID filter to main table query
const mainOptions = { ...options };
if (whereClause.length > 0) {
	mainOptions.filters = NestedFilterUtils.rebuildFilters(targetFilters);
	mainOptions.filters = {
		...(mainOptions.filters || {}),
		id: { [Op.in]: filteredIds },
	};
}

const results = await MainModel.findAll(mainOptions);
```

### Complete Example (ResidentProvider)

```typescript
public async getAllWithResidence(options?: any) {
  const userProvider = UserProvider.getInstance();

  // Separate filters into resident and residence filters
  const residenceFields = ['room_id', 'apartment_id', 'building_id', 'bed_id'];
  const {
    targetFilters: residentFilters,
    relatedFilters: residenceFilters,
    whereClause,
    replacements
  } = NestedFilterUtils.separateFilters(options, residenceFields, 'residence.');

  // Query resident_current_locations view with residence filters
  const locations = await sequelize.query(
    `SELECT DISTINCT resident_id FROM resident_current_locations WHERE 1=1${whereClause}`,
    { replacements, type: QueryTypes.SELECT }
  );

  const filteredResidentIds = locations.map((l: any) => l.resident_id);

  // Handle empty results
  if (filteredResidentIds.length === 0 && whereClause.length > 0) {
    return { count: 0, rows: [], page: options?.page || 1, pageSize: options?.pageSize || 20 };
  }

  // Apply resident_id filter to resident query
  const residentOptions = { ...options };
  if (whereClause.length > 0) {
    residentOptions.filters = NestedFilterUtils.rebuildFilters(residentFilters);
    residentOptions.filters = {
      ...(residentOptions.filters || {}),
      id: { [Op.in]: filteredResidentIds }
    };
  } else {
    residentOptions.filters = NestedFilterUtils.rebuildFilters(residentFilters);
  }

  const residents = await this.getAll(residentOptions);

  // Continue with data enrichment...
}
```

## API Reference

### `separateFilters(options, relatedFields, relatedPrefix)`

Separates filters into target (main table) and related (nested) filters.

**Parameters:**

- `options` (any): Query options containing `filters` object
- `relatedFields` (string[]): Array of field names that belong to related table
- `relatedPrefix` (string): Prefix for nested filter keys (e.g., `'residence.'`)

**Returns:**

```typescript
{
  targetFilters: any[];      // Filters for main table
  relatedFilters: any[];     // Filters for related table/view
  whereClause: string;       // WHERE clause string for related query
  replacements: any;         // Parameter replacements for WHERE clause
}
```

**Example:**

```typescript
const { targetFilters, relatedFilters, whereClause, replacements } =
	NestedFilterUtils.separateFilters(
		{ filters: { [Op.and]: [{ $full_name$: 'John' }, { 'residence.room_id': 'uuid' }] } },
		['room_id', 'building_id'],
		'residence.',
	);

// targetFilters: [{ $full_name$: 'John' }]
// relatedFilters: [{ 'residence.room_id': 'uuid' }]
// whereClause: ' AND room_id = :room_id'
// replacements: { room_id: 'uuid' }
```

### `buildWhereClause(relatedFilters, relatedPrefix)`

Builds WHERE clause and replacements for related filters.

**Parameters:**

- `relatedFilters` (any[]): Array of filter objects for related table
- `relatedPrefix` (string): Prefix to strip from nested keys

**Returns:**

```typescript
{
	whereClause: string; // WHERE clause string (starts with ' AND' if conditions exist)
	replacements: any; // Parameter replacements object
}
```

**Supported Operators:**

- `Op.eq` (or direct value): `field = :field`
- `Op.in`: `field IN (:fieldArray)`
- `Op.like`: `field LIKE :field`
- `Op.iLike`: `field ILIKE :field`

### `rebuildFilters(filters)`

Rebuilds a Sequelize filter object from an array of filter objects.

**Parameters:**

- `filters` (any[]): Array of filter objects

**Returns:**

- `any`: Sequelize filter object with `Op.and` wrapper if multiple filters

**Example:**

```typescript
const single = NestedFilterUtils.rebuildFilters([{ $name$: 'John' }]);
// Returns: { $name$: 'John' }

const multiple = NestedFilterUtils.rebuildFilters([
	{ $name$: 'John' },
	{ $age$: { [Op.gte]: 18 } },
]);
// Returns: { [Op.and]: [{ $name$: 'John' }, { $age$: { [Op.gte]: 18 } }] }
```

## Supported Filter Formats

The utility supports both direct field names and nested field names:

```typescript
// Direct field name (no prefix)
filters = room_id == uuid - 123;

// Nested field name (with prefix)
filters = residence.room_id == uuid - 123;

// Both work the same way
const residenceFields = ['room_id'];
NestedFilterUtils.separateFilters(options, residenceFields, 'residence.');
```

## Best Practices

### 1. Define Related Fields Clearly

Always explicitly list which fields belong to the related table:

```typescript
const residenceFields = ['room_id', 'apartment_id', 'building_id', 'bed_id'];
```

### 2. Use Consistent Prefixes

Choose a clear prefix for nested filters and document it in your API:

```typescript
// Use 'residence.' for all residence-related filters
NestedFilterUtils.separateFilters(options, residenceFields, 'residence.');

// API usage:
// GET /api/v1/residents?filters=residence.room_id==uuid-123
// GET /api/v1/residents?filters=residence.building_id==uuid-456
```

### 3. Handle Empty Results

Always check if the related query returns no results:

```typescript
if (filteredIds.length === 0 && whereClause.length > 0) {
	return { count: 0, rows: [], page: options?.page || 1, pageSize: options?.pageSize || 20 };
}
```

### 4. Preserve Pagination Options

Always copy pagination options from the original request:

```typescript
const residentOptions = { ...options };
// Modify filters, but keep page, pageSize, sortField, sortOrder
```

### 5. Combine with Main Table Filters

The utility preserves main table filters, allowing complex queries:

```typescript
// Filter by both resident name and residence location
GET /api/v1/residents?filters=full_name@=John,residence.room_id==uuid-123

// Both filters are applied correctly
```

## Common Use Cases

### Filtering by Location

```typescript
const locationFields = ['room_id', 'apartment_id', 'building_id', 'bed_id'];
const { targetFilters, relatedFilters, whereClause, replacements } =
	NestedFilterUtils.separateFilters(options, locationFields, 'residence.');

// Query: SELECT DISTINCT resident_id FROM resident_current_locations WHERE room_id = :room_id
```

### Filtering by Related Entity

```typescript
const userFields = ['user_id', 'user_email', 'user_role'];
const { targetFilters, relatedFilters, whereClause, replacements } =
	NestedFilterUtils.separateFilters(options, userFields, 'user.');

// Query: SELECT DISTINCT entity_id FROM entity_users WHERE user_id = :user_id
```

### Filtering by Status in Related Table

```typescript
const statusFields = ['status', 'priority', 'severity'];
const { targetFilters, relatedFilters, whereClause, replacements } =
	NestedFilterUtils.separateFilters(options, statusFields, 'ticket.');

// Query: SELECT DISTINCT entity_id FROM tickets WHERE status = :status
```

## Reusability for Other Models

This utility can be reused for any model that needs to filter by related table fields:

### Example: IncidentProvider

```typescript
public async getAllWithLocation(options?: any) {
  const locationFields = ['room_id', 'apartment_id', 'building_id'];
  const {
    targetFilters: incidentFilters,
    relatedFilters: locationFilters,
    whereClause,
    replacements
  } = NestedFilterUtils.separateFilters(options, locationFields, 'location.');

  // Query incident_locations view
  const locations = await sequelize.query(
    `SELECT DISTINCT incident_id FROM incident_locations WHERE 1=1${whereClause}`,
    { replacements, type: QueryTypes.SELECT }
  );

  const filteredIncidentIds = locations.map(l => l.incident_id);

  // Apply to incident query
  const incidentOptions = { ...options };
  if (whereClause.length > 0) {
    incidentOptions.filters = NestedFilterUtils.rebuildFilters(incidentFilters);
    incidentOptions.filters = {
      ...(incidentOptions.filters || {}),
      id: { [Op.in]: filteredIncidentIds }
    };
  } else {
    incidentOptions.filters = NestedFilterUtils.rebuildFilters(incidentFilters);
  }

  return this.getAll(incidentOptions);
}
```

## Limitations

1. **Single-level nesting**: Only supports one level of nesting (e.g., `residence.room_id`, not
   `residence.building.name`)
2. **SQL injection protection**: Always use parameterized queries with replacements
3. **Performance**: Two separate queries are executed (related table + main table)

## Future Enhancements

- Support for multi-level nested filters
- Support for OR conditions between related filters
- Caching of related query results
- Automatic JOIN generation for simple cases
