Advanced Filtering System

2.1 applyAdvancedFilters

Location: src/components/tables/common/filters.ts

Purpose: Applies advanced filters to a Supabase query, handling both AND and OR conjunctions.

Signature:

function applyAdvancedFilters(
  query: any,
  filters: FilterItem[]
): any

Parameters:

• query - Supabase query builder object
• filters - Array of filter items with column, operator, value, and conjunction

Returns: Modified Supabase query object

Logic Flow:

1. If no filters: Returns query unchanged

2. If OR conjunctions exist:

   • Groups filters by OR boundaries
   • Each group contains filters connected by AND
   • Converts groups to filter strings
   • Applies OR between groups

3. If only AND conjunctions:

   • Applies each filter sequentially with AND logic

Example:

// Filters:
// 1. firstName = "John" (AND)
// 2. lastName = "Doe" (OR)
// 3. age > 25 (AND)

// Results in: (firstName=John AND age>25) OR (lastName=Doe)

Filter String Format:

column.operator.value
and(column1.operator1.value1,column2.operator2.value2)

Notes:

• OR groups are wrapped in and() if multiple filters
• Single filter groups don't need and() wrapper
• Uses query.or() for OR groups, sequential calls for AND

---

2.2 applyFilterToQuery

Location: src/components/tables/common/utils.ts

Purpose: Applies a single filter to a Supabase query based on the operator type.

Signature:

function applyFilterToQuery(
  query: any,
  filter: { column: any; operator: any; value: any }
): any

Parameters:

• query - Supabase query builder
• filter - Object with column, operator, and value

Supported Operators:

Operator	Supabase Method	Description
eq	query.eq()	Equals
neq	query.neq()	Not equals
gt	query.gt()	Greater than
gte	query.gte()	Greater than or equal
lt	query.lt()	Less than
lte	query.lte()	Less than or equal
like	query.ilike()	Case-insensitive like with %value%
search	query.ilike()	Same as like
ilike	query.ilike()	Case-insensitive exact match
is	query.is()	Null check or exact match
in	query.in()	Array membership

Special Handling:

1. is operator:

   • If value is "null" (case-insensitive), uses query.is(column, null)
   • Otherwise uses query.is(column, value)

2. in operator:

   • Accepts comma-separated string or JSON array
   • Parses and trims values
   • Handles parsing errors gracefully

Example:

applyFilterToQuery(query, {
  column: "status",
  operator: "in",
  value: "active,pending,completed"
});
// Results in: query.in("status", ["active", "pending", "completed"])

---

2.3 formatFilterValue

Location: src/components/tables/common/utils.ts

Purpose: Formats filter values based on operator type for use in filter strings.

Signature:

function formatFilterValue(
  operator: string,
  value: string | number
): string | number | null

Parameters:

• operator - Filter operator type
• value - Raw filter value

Returns: Formatted value ready for filter string

Formatting Rules:

1. ilike: Returns value as-is (exact match)
2. like or search: Wraps value in %value% pattern
3. is with "null": Returns null
4. in: Converts comma-separated string to JSON array string
5. Default: Returns value unchanged

Example:

formatFilterValue("like", "john")
// Returns: "%john%"

formatFilterValue("in", "1,2,3")
// Returns: '["1","2","3"]'

---

2.4 mapOperator

Location: src/components/tables/common/utils.ts

Purpose: Maps UI operator names to Supabase query operator names.

Signature:

function mapOperator(operator: any): string

Mapping Table:

UI Operator	Supabase Operator
eq	eq
neq	neq
gt	gt
gte	gte
lt	lt
lte	lte
like	like
ilike	ilike
is	is
in	in
search	ilike
Default	eq

Usage: Used when building filter strings for OR clauses

---

2.5 applyColumnFilters

Location: src/components/tables/common/filters.ts

Purpose: Applies simple column filters (typically from table column search).

Signature:

function applyColumnFilters(
  query: any,
  filters: ColumnFiltersState
): any

Parameters:

• query - Supabase query builder
• filters - TanStack Table column filters state

Logic:

• Applies ilike operator with %value% pattern to each filter
• Used for quick column-level search

Example:

// Filters: [{ id: "firstName", value: "John" }]
// Results in: query.ilike("firstName", "%John%")

---

2.6 applyDateFilters

Location: src/components/tables/common/filters.ts

Purpose: Applies date range filters to a query.

Signature:

function applyDateFilters(
  query: any,
  dateFilter: DateFilterItem
): any

Parameters:

• query - Supabase query builder
• dateFilter - Object with columnId, from, and to dates

Logic:

• Only applies if both from and to are provided
• Converts dates to ISO strings
• Uses gte for start date and lte for end date

Example:

applyDateFilters(query, {
  columnId: "created_at",
  from: "2024-01-01",
  to: "2024-12-31"
});
// Results in:
// query.gte("created_at", "2024-01-01T00:00:00.000Z")
//   .lte("created_at", "2024-12-31T23:59:59.999Z")

---

2.7 applySorting

Location: src/components/tables/common/filters.ts

Purpose: Applies sorting to a Supabase query.

Signature:

function applySorting(
  query: any,
  sortingState: SortingState
): any

Parameters:

• query - Supabase query builder
• sortingState - TanStack Table sorting state array

Logic:

• Iterates through sorting array
• Uses query.order(column, { ascending: !desc })
• Supports multiple column sorting

Example:

// Sorting: [{ id: "created_at", desc: true }]
// Results in: query.order("created_at", { ascending: false })

---

2.8 applyPagination

Location: src/components/tables/common/filters.ts

Purpose: Applies pagination to a Supabase query.

Signature:

function applyPagination(
  query: any,
  paginationState: PaginationState
): any

Parameters:

• query - Supabase query builder
• paginationState - Object with pageIndex and pageSize

Logic:

• Calculates range: [pageIndex * pageSize, (pageIndex + 1) * pageSize - 1]
• Uses query.range(start, end)

Example:

// Pagination: { pageIndex: 2, pageSize: 20 }
// Range: [40, 59]
// Results in: query.range(40, 59)

---

2.9 AdvancedFilters Component

Location: src/components/tables/common/advanceFilter.tsx

Purpose: React component for managing advanced filter UI with dynamic filter creation.

Props:

interface FilterDropdownProps {
  columns: Column[];
  activeFilters: FilterItem[];
  onApplyFilters: (filters: FilterItem[]) => void;
  className?: string;
}

Key Features:

1. Dynamic Filter Management:

   • Add/remove filters dynamically
   • Each filter has unique ID (timestamp-based)

2. Column Type Awareness:

   • Different operators for text, number, option, boolean
   • Auto-resets operator when column changes

3. Filter Validation:

   • Only applies filters with both column and value
   • Preserves filters from other sources

4. Conjunction Support:

   • AND/OR selection per filter
   • Visual indication of filter relationships

State Management:

• Internal state for filter editing
• Syncs with activeFilters prop
• Applies changes via onApplyFilters callback