# Filter System Guide The B12 SIS API includes a powerful filter system that supports complex queries with AND/OR grouping, multiple operators, and related field filtering. ## Basic Filtering ### Simple Condition Filter by a single field: ```json { "filter": { "group": "AND", "conditions": [ {"field": "status", "operator": "=", "value": "active"} ] } } ``` ### Multiple Conditions (AND) All conditions must match: ```json { "filter": { "group": "AND", "conditions": [ {"field": "status", "operator": "=", "value": "active"}, {"field": "enrollment_year", "operator": "=", "value": 2024} ] } } ``` ### Multiple Conditions (OR) Any condition can match: ```json { "filter": { "group": "OR", "conditions": [ {"field": "status", "operator": "=", "value": "active"}, {"field": "status", "operator": "=", "value": "applicant"} ] } } ``` ## Nested Filtering Combine AND and OR groups: ```json { "filter": { "group": "AND", "conditions": [ {"field": "enrollment_year", "operator": "=", "value": 2024}, { "group": "OR", "conditions": [ {"field": "status", "operator": "=", "value": "active"}, {"field": "status", "operator": "=", "value": "applicant"} ] } ] } } ``` This translates to: `enrollment_year = 2024 AND (status = 'active' OR status = 'applicant')` ## Operators ### Comparison Operators | Operator | SQL Equivalent | Description | |----------|---------------|-------------| | `=` | `=` | Equals | | `!=` | `<>` | Not equals | | `>` | `>` | Greater than | | `<` | `<` | Less than | | `>=` | `>=` | Greater than or equal | | `<=` | `<=` | Less than or equal | **Examples:** ```json {"field": "score", "operator": ">", "value": 80} {"field": "age", "operator": ">=", "value": 18} {"field": "status", "operator": "!=", "value": "deleted"} ``` ### String Operators | Operator | SQL Equivalent | Description | |----------|---------------|-------------| | `contains` | `LIKE %value%` | Contains substring | | `starts_with` | `LIKE value%` | Starts with | | `ends_with` | `LIKE %value` | Ends with | **Examples:** ```json {"field": "name", "operator": "contains", "value": "john"} {"field": "code", "operator": "starts_with", "value": "STD"} {"field": "email", "operator": "ends_with", "value": "@school.edu"} ``` ### Null Operators | Operator | SQL Equivalent | Description | |----------|---------------|-------------| | `is_null` | `IS NULL` | Is NULL | | `is_not_null` | `IS NOT NULL` | Is not NULL | **Examples:** ```json {"field": "deleted_at", "operator": "is_null", "value": true} {"field": "assigned_to_id", "operator": "is_not_null", "value": true} ``` ### Array Operators | Operator | SQL Equivalent | Description | |----------|---------------|-------------| | `in` | `IN (...)` | Value in array | | `not_in` | `NOT IN (...)` | Value not in array | **Examples:** ```json {"field": "status", "operator": "in", "value": ["active", "pending"]} {"field": "grade_level", "operator": "not_in", "value": ["1", "2", "3"]} ``` ## Field Types ### Text Fields ```json {"field": "name", "operator": "contains", "value": "john"} ``` ### Date Fields Use ISO 8601 format (YYYY-MM-DD): ```json {"field": "birthdate", "operator": ">=", "value": "2010-01-01"} {"field": "enrollment_date", "operator": "<=", "value": "2024-12-31"} ``` ### Date Range ```json { "filter": { "group": "AND", "conditions": [ {"field": "created_at", "operator": ">=", "value": "2024-01-01"}, {"field": "created_at", "operator": "<=", "value": "2024-01-31"} ] } } ``` ### Boolean Fields ```json {"field": "lms_active", "operator": "=", "value": true} {"field": "app_active", "operator": "=", "value": false} ``` ### Enum Fields ```json {"field": "gender", "operator": "=", "value": "Male"} {"field": "status", "operator": "in", "value": ["active", "applicant"]} ``` ### Relate Fields (Foreign Keys) Filter by related record ID: ```json {"field": "class_id", "operator": "=", "value": "class-uuid"} {"field": "program_id", "operator": "is_not_null", "value": true} ``` ### Multi-relate Fields Filter by related record in many-to-many: ```json {"field": "course_ids", "operator": "contains", "value": "course-uuid"} ``` ## Related Field Filtering Filter by fields on related models: ### Direct Relation Filter students by their class name: ```json { "filter": { "group": "AND", "conditions": [ {"field": "current_class.name", "operator": "contains", "value": "10A"} ] } } ``` ### Through Enrollment Filter students by enrolled course: ```json { "filter": { "group": "AND", "conditions": [ {"field": "student_enroll_courses.course_id", "operator": "=", "value": "course-uuid"} ] } } ``` ## Filterable Fields API Get available filter fields for a module: **Endpoint:** `GET /api/filterable-fields/:module` **Response:** ```json { "success": true, "data": [ { "field": "status", "label": "Status", "type": "enum", "options": ["active", "applicant", "alumni", "withdrawal"] }, { "field": "current_class_id", "label": "Current Class", "type": "relate", "module": "Class" }, { "field": "birthdate", "label": "Date of Birth", "type": "date" } ] } ``` ## Saved Filters ### Filter Definition Model Save commonly used filters: ```json { "id": "filter-uuid", "name": "Active Grade 10 Students", "module": "Student", "filter_json": { "group": "AND", "conditions": [ {"field": "status", "operator": "=", "value": "active"}, {"field": "current_class.grade_level.name", "operator": "=", "value": "Grade 10"} ] }, "is_public": true } ``` ### Create Saved Filter **Endpoint:** `POST /api/filter_definitions` ### Load Saved Filter **Endpoint:** `GET /api/filter_definitions/load/:id` ### Get Filters for Module **Endpoint:** `GET /api/filter_definitions/module/:module` ## Complex Examples ### Students in Grade 10 or 11, Active Status ```json { "filter": { "group": "AND", "conditions": [ {"field": "status", "operator": "=", "value": "active"}, { "group": "OR", "conditions": [ {"field": "current_class.grade_level.name", "operator": "=", "value": "Grade 10"}, {"field": "current_class.grade_level.name", "operator": "=", "value": "Grade 11"} ] } ] } } ``` ### Students Enrolled in Specific Courses This Semester ```json { "filter": { "group": "AND", "conditions": [ {"field": "status", "operator": "=", "value": "active"}, { "group": "OR", "conditions": [ {"field": "student_enroll_courses.course_id", "operator": "=", "value": "math-course-uuid"}, {"field": "student_enroll_courses.course_id", "operator": "=", "value": "science-course-uuid"} ] }, {"field": "student_enroll_courses.semester_id", "operator": "=", "value": "current-semester-uuid"} ] } } ``` ### Attendance Records for Absent Students This Week ```json { "filter": { "group": "AND", "conditions": [ {"field": "attendance_date", "operator": ">=", "value": "2024-01-15"}, {"field": "attendance_date", "operator": "<=", "value": "2024-01-19"}, { "group": "OR", "conditions": [ {"field": "status", "operator": "=", "value": "AbsentRequest"}, {"field": "status", "operator": "=", "value": "AbsentNoRequest"} ] } ] } } ``` ## Performance Tips 1. **Index Fields**: Frequently filtered fields should be indexed 2. **Limit Depth**: Keep nested groups to 2-3 levels max 3. **Use Specific Operators**: `=` is faster than `contains` 4. **Date Ranges**: Use `>=` and `<=` instead of multiple `OR` conditions 5. **Pagination**: Always use pagination with large result sets ## Code Examples ### JavaScript Filter Builder ```javascript class FilterBuilder { constructor(group = 'AND') { this.filter = { group, conditions: [] }; } equals(field, value) { this.filter.conditions.push({ field, operator: '=', value }); return this; } contains(field, value) { this.filter.conditions.push({ field, operator: 'contains', value }); return this; } between(field, start, end) { this.filter.conditions.push( { field, operator: '>=', value: start }, { field, operator: '<=', value: end } ); return this; } or(callback) { const orBuilder = new FilterBuilder('OR'); callback(orBuilder); this.filter.conditions.push(orBuilder.build()); return this; } build() { return this.filter; } } // Usage const filter = new FilterBuilder() .equals('status', 'active') .between('enrollment_date', '2024-01-01', '2024-12-31') .or(f => f .equals('gender', 'Male') .equals('gender', 'Female') ) .build(); ```