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:

{
  "filter": {
    "group": "AND",
    "conditions": [
      {"field": "status", "operator": "=", "value": "active"}
    ]
  }
}

Multiple Conditions (AND)

All conditions must match:

{
  "filter": {
    "group": "AND",
    "conditions": [
      {"field": "status", "operator": "=", "value": "active"},
      {"field": "enrollment_year", "operator": "=", "value": 2024}
    ]
  }
}

Multiple Conditions (OR)

Any condition can match:

{
  "filter": {
    "group": "OR",
    "conditions": [
      {"field": "status", "operator": "=", "value": "active"},
      {"field": "status", "operator": "=", "value": "applicant"}
    ]
  }
}

Nested Filtering

Combine AND and OR groups:

{
  "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:

{"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:

{"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:

{"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:

{"field": "status", "operator": "in", "value": ["active", "pending"]}
{"field": "grade_level", "operator": "not_in", "value": ["1", "2", "3"]}

Field Types

Text Fields

{"field": "name", "operator": "contains", "value": "john"}

Date Fields

Use ISO 8601 format (YYYY-MM-DD):

{"field": "birthdate", "operator": ">=", "value": "2010-01-01"}
{"field": "enrollment_date", "operator": "<=", "value": "2024-12-31"}

Date Range

{
  "filter": {
    "group": "AND",
    "conditions": [
      {"field": "created_at", "operator": ">=", "value": "2024-01-01"},
      {"field": "created_at", "operator": "<=", "value": "2024-01-31"}
    ]
  }
}

Boolean Fields

{"field": "lms_active", "operator": "=", "value": true}
{"field": "app_active", "operator": "=", "value": false}

Enum Fields

{"field": "gender", "operator": "=", "value": "Male"}
{"field": "status", "operator": "in", "value": ["active", "applicant"]}

Relate Fields (Foreign Keys)

Filter by related record ID:

{"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:

{"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:

{
  "filter": {
    "group": "AND",
    "conditions": [
      {"field": "current_class.name", "operator": "contains", "value": "10A"}
    ]
  }
}

Through Enrollment

Filter students by enrolled course:

{
  "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:

{
  "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:

{
  "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

{
  "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

{
  "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

{
  "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

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();