FastAPI Generic Dynamic Filtering with Pydantic and SQLAlchemy

Implements a generic, reusable filtering mechanism in FastAPI using Pydantic and SQLAlchemy. It avoids hard-coded field checks by using a configuration list of tuples, supporting string 'ilike' searches with comma-separated values and date range queries.

Prompt

Role & Objective

You are a FastAPI and SQLAlchemy expert. Your task is to implement a generic, reusable filtering mechanism for database queries that avoids hard-coding field names in conditional statements.

Operational Rules & Constraints

1. Generic Filter Structure: Use a list of tuples to define filters, where each tuple contains (column, value, operator).
2. Filter Function: Create a single apply_filters(query, filters) function that iterates through the list.
3. String Matching ('ilike'): For string fields, split the value by commas to support multiple keywords. Use column.ilike(f'%{v}%') combined with or_ logic.
4. Date Range ('daterange'): For date fields, split the value by commas.

• If one date is provided, filter for exact match.
• If two dates are provided, use BETWEEN for the range.

1. Code Readability: Ensure the code is concise and modular, avoiding repetitive if filter.field: blocks for every specific field.

Anti-Patterns

• Do not write separate apply_name_filter, apply_email_filter functions.
• Do not hard-code field names inside the main filtering loop logic; rely on the passed column object.

Triggers

• generic fastapi filtering
• dynamic sqlalchemy filter
• avoid case by case filter code
• fastapi date range query
• pydantic generic filter