npm package: @nodewave/prisma-ezfilter
Description: A TypeScript package for building dynamic Prisma queries with type-safe filtering, searching, ordering, and range filtering with support for multi-level nested relations
Author: Rizqy EP
Latest version: 0.2.1

# @nodewave/prisma-ezfilter

A TypeScript package for building dynamic Prisma queries with type-safe filtering, searching, ordering, and range filtering with support for **multi-level nested relations**. This package provides a flexible and extensible way to translate standard query parameters into Prisma query options with optional validation and field mapping.

## Features

- 🎯 **Type-safe query building** with full TypeScript support
- 🔍 **Dynamic filtering** with support for exact matches, arrays, and relational data
- 🔗 **Multi-level nested relations** - handle deeply nested fields like `author.profile.address.city`
- 🔎 **Search functionality** with `contains` operator, multi-field search, **case-sensitive/insensitive modes**, and **multiple keywords support**
- 📊 **Range filtering** for dates and numbers with automatic date parsing
- 📝 **Ordering** with customizable sort directions
- 📄 **Pagination** with configurable page sizes
- ✅ **Validation** with customizable specifications
- 🔄 **Field mapping** and transformation support
- 🌐 **Framework-agnostic** query parameter extraction
- 🏗️ **Modular architecture** for easy extension

## Installation

```bash
npm install @nodewave/prisma-ezfilter
```

## Quick Start

### Direct Usage

```typescript
import { BuildQueryFilter, createQueryBuilder } from '@nodewave/prisma-ezfilter';

// Create a query builder
const queryBuilder = new BuildQueryFilter();

// Define your filter with multi-level relations
const filter = {
  filters: {
    status: 'active',
    category: ['tech', 'business'],
    'author.profile.department.region': 'US', // Multi-level relation
    'company.departments.teams.lead.role': 'Senior Developer' // Deep nesting
  },
  searchFilters: {
    title: 'react',
    'author.profile.bio': 'software engineer' // Search in nested relations
  },
  rangedFilters: [
    {
      key: 'author.profile.createdAt', // Range on nested field
      start: '2023-01-01T00:00:00Z',
      end: '2023-12-31T23:59:59Z'
    }
  ],
  orderKey: 'author.profile.department.name', // Order by nested field
  orderRule: 'asc',
  page: 1,
  rows: 20
};

// Build the query
const result = queryBuilder.build(filter);

// Use with Prisma
const posts = await prisma.post.findMany(result.query);
```

### From HTTP Query Parameters

```typescript
import { BuildQueryFilter, extractQueryFromParams } from '@nodewave/prisma-ezfilter';

// Extract from query parameters (framework-agnostic)
const queryParams = {
  filters: '{"status":"active","author.profile.department.region":"US"}',
  searchFilters: '{"title":"react","author.profile.bio":"engineer"}',
  orderKey: 'author.profile.createdAt',
  orderRule: 'desc',
  page: '1',
  rows: '20'
};

const filter = extractQueryFromParams(queryParams);
const queryBuilder = new BuildQueryFilter();
const result = queryBuilder.build(filter);

// Use with Prisma
const posts = await prisma.post.findMany(result.query);
```

## Multi-Level Relations Support

The package now supports **unlimited levels of nested relations**, allowing you to query deeply nested data structures:

### Examples of Supported Nesting Levels

```typescript
// Two levels
'author.profile.name'

// Three levels  
'posts.comments.user.profile.name'

// Four levels
'company.departments.teams.members.profile.role'

// Five levels
'organization.locations.buildings.floors.rooms.equipment.type'

// And so on...
```

### How It Works

The package automatically converts dot-notation fields into nested Prisma query objects:

```typescript
// Input: 'author.profile.department.region'
// Output: 
{
  author: {
    profile: {
      department: {
        region: 'US'
      }
    }
  }
}

// Input: 'company.departments.teams.lead.profile.role'
// Output:
{
  company: {
    departments: {
      teams: {
        lead: {
          profile: {
            role: 'Senior Developer'
          }
        }
      }
    }
  }
}
```

### Supported in All Query Types

Multi-level relations work across all filtering types:

- ✅ **Filters** - Exact matches on nested fields
- ✅ **Search Filters** - Text search in nested fields  
- ✅ **Range Filters** - Date/number ranges on nested fields
- ✅ **Order By** - Sorting by nested fields
- ✅ **Validation** - Relation validation at each level

## API Reference

### Core Types

#### `FilteringQuery`

```typescript
interface FilteringQuery {
  filters?: Record<string, any | any[] | null>;
  searchFilters?: Record<string, any | any[] | null>;
  rangedFilters?: RangedFilter[];
  orderKey?: string;
  orderRule?: 'asc' | 'desc';
  page?: number;
  rows?: number;
}
```

#### `RangedFilter`

```typescript
interface RangedFilter {
  key: string;
  start: string | number | Date;
  end: string | number | Date;
}
```

#### `QuerySpecification`

```typescript
interface QuerySpecification {
  allowedFields?: string[];
  allowedOperators?: PrismaOperator[];
  allowedRelations?: string[];
  maxPageSize?: number;
  defaultPageSize?: number;
  requiredFields?: string[];
  forbiddenFields?: string[];
  defaultSearchMode?: 'insensitive' | 'sensitive'; // NEW: Global search mode setting
}
```

#### `QueryParams` & `QueryExtractor`

```typescript
interface QueryParams {
  [key: string]: string | string[] | undefined;
}

interface QueryExtractor {
  getQueryParams(): QueryParams;
}
```

### BuildQueryFilter Class

#### Constructor

```typescript
new BuildQueryFilter(specification?: QuerySpecification, transformConfig?: TransformConfig)
```

#### Methods

- `build(filter: FilteringQuery): BuildQueryResult` - Builds a query with validation
- `buildWithoutValidation(filter: FilteringQuery): PrismaQueryOptions` - Builds a query without validation
- `validate(filter: FilteringQuery): ValidationResult` - Validates a query without building it
- `setSpecification(specification: QuerySpecification): void` - Updates the specification
- `setTransformConfig(config: TransformConfig): void` - Updates the transform configuration

### Utility Functions

- `createQueryBuilder(specification?, transformConfig?)` - Convenience function to create a query builder
- `buildFilterQuery(filter: FilteringQuery): PrismaQueryOptions` - Builds a query without validation
- `validateQuery(filter: FilteringQuery, specification?): ValidationResult` - Validates a query
- `transformWhereClause(whereConditions, config): any` - Transforms where conditions
- `transformOrderBy(orderBy, config): any` - Transforms order by clause
- `extractQueryFromParams(queryParams: QueryParams): FilteringQuery` - Extracts filter from query parameters
- `createQueryExtractor(params: QueryParams): QueryExtractor` - Creates a query extractor

## Usage Examples

### Basic Usage

```typescript
import { BuildQueryFilter } from '@nodewave/prisma-ezfilter';

const queryBuilder = new BuildQueryFilter();

const filter = {
  filters: {
    status: 'published',
    authorId: 'user123'
  },
  orderKey: 'createdAt',
  orderRule: 'desc',
  page: 1,
  rows: 10
};

const result = queryBuilder.build(filter);
// result.query contains the Prisma query options
// result.validation contains validation results
```

### Multi-Level Relational Filters

```typescript
const filter = {
  filters: {
    status: 'active',
    'author.profile.department.name': 'Engineering',
    'company.departments.teams.lead.profile.role': 'Senior Developer',
    'posts.comments.user.profile.verified': true
  }
};

// Generated Prisma query will include nested objects for each relation level
```

### Array Filters with Multi-Level Relations

```typescript
const filter = {
  filters: {
    status: 'active',
    category: ['tech', 'business', 'design'], // Creates OR condition
    priority: [1, 2, 3], // Numbers work too
    'author.profile.department.region': ['US', 'EU'], // Multi-level arrays
    'company.departments.teams.members.profile.skills': ['React', 'TypeScript'] // Deep nesting
  }
};

// Generated SQL equivalent:
// WHERE status = 'active' 
//   AND (category = 'tech' OR category = 'business' OR category = 'design')
//   AND (priority = 1 OR priority = 2 OR priority = 3)
//   AND (author.profile.department.region = 'US' OR author.profile.department.region = 'EU')
//   AND (company.departments.teams.members.profile.skills = 'React' OR company.departments.teams.members.profile.skills = 'TypeScript')
```

### Case-Sensitive Search

The query builder supports both case-sensitive and case-insensitive search modes. By default, all searches are case-insensitive for better user experience.

#### Global Case-Sensitive Search

```typescript
const specification: QuerySpecification = {
  allowedFields: ['name', 'email', 'title', 'content'],
  allowedRelations: ['author', 'profile'],
  defaultSearchMode: 'sensitive' // Enable case-sensitive search globally
};

const queryBuilder = new BuildQueryFilter(specification);

const filter = {
  searchFilters: {
    name: 'John', // Will only match exact case
    email: 'john@example.com', // Will only match exact case
    'author.profile.bio': 'Software Engineer' // Will only match exact case
  }
};

// Generated Prisma query:
// {
//   where: {
//     AND: [
//       {
//         OR: [
//           { name: { contains: 'John', mode: 'sensitive' } },
//           { email: { contains: 'john@example.com', mode: 'sensitive' } },
//           { author: { profile: { bio: { contains: 'Software Engineer', mode: 'sensitive' } } } }
//         ]
//       }
//     ]
//   }
// }
```

#### Field-Specific Case Sensitivity

```typescript
const specification: QuerySpecification = {
  allowedFields: ['name', 'email', 'title', 'content'],
  allowedRelations: ['author', 'profile'],
  defaultSearchMode: 'insensitive' // Default to case-insensitive
};

const transformConfig: TransformConfig = {
  fieldMappings: {},
  fieldTypeHandlers: {
    name: {
      type: 'string',
      searchOperator: 'contains',
      searchMode: 'sensitive' // Case-sensitive for names
    },
    email: {
      type: 'string',
      searchOperator: 'contains',
      searchMode: 'sensitive' // Case-sensitive for emails
    },
    'author.profile.bio': {
      type: 'string',
      searchOperator: 'contains',
      searchMode: 'insensitive' // Case-insensitive for bio
    },
    title: {
      type: 'string',
      searchOperator: 'contains',
      searchMode: 'insensitive' // Case-insensitive for titles
    }
  }
};

const queryBuilder = new BuildQueryFilter(specification, transformConfig);

const filter = {
  searchFilters: {
    name: 'John', // Case-sensitive
    email: 'john@example.com', // Case-sensitive
    'author.profile.bio': 'software engineer', // Case-insensitive
    title: 'react tutorial' // Case-insensitive
  }
};
```

#### Best Practices for Case Sensitivity

**Use Case-Sensitive Search For:**
- Email addresses
- Usernames
- Exact identifiers
- Code/technical terms
- Database keys

**Use Case-Insensitive Search For:**
- User search queries
- Content descriptions
- General text search
- User-friendly interfaces
- Search suggestions

### Framework Integration

#### Express.js

```typescript
import express from 'express';
import { BuildQueryFilter, extractQueryFromParams } from '@nodewave/prisma-ezfilter';

const app = express();
const queryBuilder = new BuildQueryFilter();

app.get('/posts', async (req, res) => {
  try {
    // Extract from Express query parameters
    const filter = extractQueryFromParams(req.query);
    const result = queryBuilder.build(filter);
    
    if (!result.validation.isValid) {
      return res.status(400).json({
        error: 'Invalid query parameters',
        details: result.validation.errors
      });
    }

    // Use with Prisma
    const posts = await prisma.post.findMany(result.query);
    res.json(posts);
  } catch (error) {
    res.status(500).json({ error: 'Server error' });
  }
});

// Example request with multi-level relations:
// GET /posts?filters={"status":"active","author.profile.department.region":"US"}&searchFilters={"author.profile.bio":"engineer"}&orderKey=author.profile.createdAt&orderRule=desc&page=1&rows=20
```

#### Hono

```typescript
import { Hono } from 'hono';
import { BuildQueryFilter, extractQueryFromParams } from '@nodewave/prisma-ezfilter';

const app = new Hono();
const queryBuilder = new BuildQueryFilter();

app.get('/posts', async (c) => {
  try {
    // Extract from Hono query parameters
    const queryParams = c.req.query();
    const filter = extractQueryFromParams(queryParams);
    const result = queryBuilder.build(filter);
    
    if (!result.validation.isValid) {
      return c.json({
        error: 'Invalid query parameters',
        details: result.validation.errors
      }, 400);
    }

    // Use with Prisma
    const posts = await prisma.post.findMany(result.query);
    return c.json(posts);
  } catch (error) {
    return c.json({ error: 'Server error' }, 500);
  }
});
```

#### Custom Framework Integration

```typescript
import { BuildQueryFilter, extractQueryFromParams, createQueryExtractor } from '@nodewave/prisma-ezfilter';

// Custom query extractor for any framework
class CustomQueryExtractor {
  private params: any;
  
  constructor(params: any) {
    this.params = params;
  }
  
  getQueryParams() {
    return this.params;
  }
}

// Usage
const customExtractor = new CustomQueryExtractor({
  filters: '{"status":"active","author.profile.department.region":"US"}',
  orderKey: 'author.profile.createdAt'
});

const queryParams = customExtractor.getQueryParams();
const filter = extractQueryFromParams(queryParams);
const queryBuilder = new BuildQueryFilter();
const result = queryBuilder.build(filter);
```

### With Validation

```typescript
const specification: QuerySpecification = {
  allowedFields: ['title', 'status', 'authorId', 'createdAt'],
  allowedRelations: ['author', 'profile', 'department', 'company', 'teams', 'members'], // Include all relation levels
  maxPageSize: 100,
  forbiddenFields: ['password', 'secret']
};

const queryBuilder = new BuildQueryFilter(specification);

const filter = {
  filters: {
    status: 'published',
    'author.profile.department.region': 'US', // Will be validated
    'company.departments.teams.lead.role': 'Developer' // Will be validated
  }
};

const result = queryBuilder.build(filter);
console.log(result.validation.warnings); // Check for warnings
```

### With Field Mapping

```typescript
const transformConfig: TransformConfig = {
  fieldMappings: {
    'userName': 'user.profile.name',
    'userDepartment': 'user.profile.department.name'
  },
  fieldNameMappings: {
    'userName': 'name',
    'userDepartment': 'departmentName'
  },
  fieldTypeHandlers: {
    'createdAt': {
      type: 'date',
      searchOperator: 'gte'
    }
  }
};

const queryBuilder = new BuildQueryFilter(undefined, transformConfig);

const filter = {
  filters: {
    userName: 'john', // Will be mapped to user.profile.name
    userDepartment: 'engineering' // Will be mapped to user.profile.department.name
  }
};

const result = queryBuilder.build(filter);
```

### Complex Multi-Level Queries

```typescript
const filter = {
  filters: {
    status: 'published',
    category: ['tech', 'business'],
    'author.profile.verified': true,
    'company.departments.teams.lead.profile.role': 'Senior Developer'
  },
  searchFilters: {
    title: 'react',
    'author.profile.bio': 'software engineer',
    'company.departments.teams.members.profile.skills': 'TypeScript'
  },
  rangedFilters: [
    {
      key: 'author.profile.createdAt',
      start: '2023-01-01T00:00:00Z',
      end: '2023-12-31T23:59:59Z'
    },
    {
      key: 'company.departments.teams.createdAt',
      start: '2023-06-01T00:00:00Z',
      end: '2023-06-30T23:59:59Z'
    }
  ],
  orderKey: 'company.departments.teams.members.profile.createdAt',
  orderRule: 'desc',
  page: 2,
  rows: 25
};
```

## Query Parameter Format

The package supports standard query parameters that can be sent via HTTP requests:

### Supported Parameters

- `filters` - JSON string containing exact match filters
- `searchFilters` - JSON string containing search filters (uses `contains` operator)
- `rangedFilters` - JSON string containing range filters
- `orderKey` - Field name for ordering
- `orderRule` - Order direction (`asc` or `desc`)
- `page` - Page number for pagination
- `rows` - Number of rows per page

### Example HTTP Request with Multi-Level Relations

```bash
GET /posts?filters={"status":"active","author.profile.department.region":"US"}&searchFilters={"author.profile.bio":"engineer"}&orderKey=author.profile.createdAt&orderRule=desc&page=1&rows=20
```

### Array Support with Multi-Level Relations

Arrays in filters automatically create OR conditions, even with deeply nested relations:

```javascript
// Single value with multi-level relation
filters: '{"author.profile.department.region":"US"}'
// SQL: WHERE author.profile.department.region = 'US'

// Array values with multi-level relations
filters: '{"author.profile.department.region":["US","EU","APAC"]}'
// SQL: WHERE (author.profile.department.region = 'US' OR author.profile.department.region = 'EU' OR author.profile.department.region = 'APAC')'

// Mixed types with multi-level relations
filters: '{"status":"active","author.profile.department.region":["US","EU"],"company.departments.teams.lead.role":["Developer","Senior Developer"]}'
// SQL: WHERE status = 'active' AND (author.profile.department.region = 'US' OR author.profile.department.region = 'EU') AND (company.departments.teams.lead.role = 'Developer' OR company.departments.teams.lead.role = 'Senior Developer')'
```

## Advanced Features

### Custom Type Handlers

```typescript
const transformConfig: TransformConfig = {
  fieldTypeHandlers: {
    'price': {
      type: 'number',
      searchOperator: 'gte'
    },
    'isActive': {
      type: 'boolean'
    },
    'customField': {
      type: 'custom',
      customHandler: (value) => {
        // Custom transformation logic
        return { equals: value.toUpperCase() };
      }
    }
  }
};
```

### Custom Relation Handlers

```typescript
const transformConfig: TransformConfig = {
  relationHandlers: {
    'user': {
      type: 'one-to-many',
      relationQuery: 'some',
      customHandler: (value, field) => {
        return {
          some: {
            [field]: value
          }
        };
      }
    }
  }
};
```

## Error Handling

```typescript
const result = queryBuilder.build(filter);

if (!result.validation.isValid) {
  console.error('Validation errors:', result.validation.errors);
  return;
}

if (result.validation.warnings.length > 0) {
  console.warn('Validation warnings:', result.validation.warnings);
}

// Use the query
const data = await prisma.model.findMany(result.query);
```

## Testing

The package includes comprehensive end-to-end tests that cover:

- ✅ Query parameter extraction from HTTP requests
- ✅ Array filter processing (OR conditions)
- ✅ **Multi-level relational query handling** (NEW!)
- ✅ Search filter functionality in nested relations
- ✅ Range filter processing on nested fields
- ✅ Ordering by deeply nested fields
- ✅ Validation with specifications for multi-level relations
- ✅ Error handling for malformed data
- ✅ Framework-agnostic query extraction

### Multi-Level Relations Test Coverage

The new test suite specifically covers:

- ✅ Two-level relations (`author.profile.name`)
- ✅ Three-level relations (`posts.comments.user.profile.name`)
- ✅ Four-level relations (`company.departments.teams.members.profile.role`)
- ✅ Array values with multi-level relations
- ✅ Search filters in nested relations
- ✅ Range filters on nested fields
- ✅ Ordering by nested fields
- ✅ Complex queries combining multiple nesting levels
- ✅ Validation of multi-level relations

Run tests with:

```bash
npm test
```

## Complete Type Definitions

### Core Interfaces

#### `FilteringQuery`
```typescript
interface FilteringQuery {
  filters?: Record<string, any | any[] | null>;
  searchFilters?: Record<string, any | any[] | null>;
  rangedFilters?: RangedFilter[];
  orderKey?: string;
  orderRule?: OrderDirection;
  page?: number;
  rows?: number;
}
```

#### `RangedFilter`
```typescript
interface RangedFilter {
  key: string;
  start: string | number | Date;
  end: string | number | Date;
}
```

#### `QuerySpecification`
```typescript
interface QuerySpecification {
  allowedFields?: string[];
  allowedOperators?: PrismaOperator[];
  allowedRelations?: string[];
  maxPageSize?: number;
  defaultPageSize?: number;
  requiredFields?: string[];
  forbiddenFields?: string[];
  defaultSearchMode?: 'insensitive' | 'sensitive'; // Global search mode setting
}
```

#### `TransformConfig`
```typescript
interface TransformConfig {
  fieldMappings: Record<string, string>;
  fieldNameMappings?: Record<string, string>;
  fieldTypeHandlers?: Record<string, FieldTypeHandler>;
  relationHandlers?: Record<string, RelationHandler>;
}
```

#### `FieldTypeHandler`
```typescript
interface FieldTypeHandler {
  type: 'string' | 'number' | 'boolean' | 'date' | 'custom';
  searchOperator?: PrismaOperator;
  searchMode?: 'insensitive' | 'sensitive'; // Field-specific search mode
  customHandler?: (value: any) => any;
}
```

#### `RelationHandler`
```typescript
interface RelationHandler {
  type: 'one-to-many' | 'many-to-many' | 'one-to-one';
  relationQuery: 'some' | 'every' | 'none';
  nestedField?: string;
  customHandler?: (value: any, actualField: string) => any;
}
```

#### `PrismaOperator`
```typescript
type PrismaOperator =
  | 'equals'
  | 'not'
  | 'in'
  | 'notIn'
  | 'lt'
  | 'lte'
  | 'gt'
  | 'gte'
  | 'contains'
  | 'startsWith'
  | 'endsWith'
  | 'mode'
  | 'search';
```

#### `BuildQueryResult`
```typescript
interface BuildQueryResult {
  query: PrismaQueryOptions;
  validation: ValidationResult;
}
```

#### `ValidationResult`
```typescript
interface ValidationResult {
  isValid: boolean;
  errors: string[];
  warnings: string[];
}
```

## Migration from v0.1.0

If you're upgrading from the previous version:

1. **Package name changed**: `prisma-ezfilter` → `@nodewave/prisma-ezfilter`
2. **New feature**: Multi-level relations are now supported (previously only single-level)
3. **New feature**: Case-sensitive/insensitive search modes are now supported
4. **Enhanced validation**: Better support for validating nested relations
5. **Improved types**: Better TypeScript support for nested objects

### Breaking Changes

- None - all existing functionality remains the same
- Multi-level relations are additive and don't affect existing single-level queries
- Case-sensitive search is backward compatible - existing searches remain case-insensitive by default

## Contributing

1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Add tests for new functionality
5. Submit a pull request

## License

MIT License - see LICENSE file for details.