How to Configure Sort Parameters in FastCRUD Endpoints: A Complete Guide

FastCRUD allows you to configure sort parameters via HTTP query strings like ?sort=name,-created_at or programmatically through the Python API using sort_columns and sort_orders arguments.

Configuring sort parameters in FastCRUD endpoints enables you to control the ordering of list results without writing custom SQL. The benavlabs/fastcrud library provides a multi-layered sorting pipeline that handles everything from parsing HTTP requests to generating SQLAlchemy ORDER BY clauses. This guide explains how to leverage both the automatic HTTP-based sorting and the programmatic Python API to configure sort behavior in your FastCRUD applications.

Understanding the FastCRUD Sorting Pipeline

FastCRUD implements sorting through three coordinated components that transform a raw HTTP query string into a validated SQL ORDER BY clause. Understanding this pipeline helps you debug sorting issues and customize behavior when needed.

PaginatedRequestQuery: Parsing HTTP Sort Parameters

The PaginatedRequestQuery class, defined in fastcrud/core/__init__.py, acts as a FastAPI dependency that extracts the raw sort value from incoming requests. When a client sends GET /items?sort=name,-created_at, this component captures the string "name,-created_at" and makes it available to the endpoint logic.

EndpointCreator: Transforming Sort Strings

Inside fastcrud/endpoint/endpoint_creator.py, the _read_items method (lines 65-78) processes the raw sort string into two parallel lists. It splits the string on commas, removes the leading hyphen from fields marked for descending order, and builds sort_columns and sort_orders arrays. For example, "name,-created_at" becomes ["name", "created_at"] and ["asc", "desc"].

SortProcessor: Building the SQL ORDER BY Clause

The final transformation occurs in fastcrud/core/query/sorting.py, where SortProcessor.apply_sorting_to_statement (lines 71-95) validates column names against the model and appends asc() or desc() clauses to the SQLAlchemy Select object. If a column does not exist, it raises sqlalchemy.exc.ArgumentError with a clear error message.

How to Configure Sort Parameters via HTTP Requests

The simplest way to configure sorting is through the sort query parameter in your API requests. FastCRUD endpoints automatically parse these parameters when using the default EndpointCreator configuration.

Basic Sorting Syntax

Use comma-separated field names to define sort order. Prefix a field with a hyphen (-) to specify descending order:

curl "http://localhost:8000/items?sort=name,-created_at"

This request sorts results by name in ascending order, then by created_at in descending order for items with identical names.

Multi-Field Sorting

You can chain multiple fields to create complex sort hierarchies:

curl "http://localhost:8000/users?sort=department,last_name,-hire_date"

This sorts by department (ascending), then last name (ascending), then hire date (descending) within each last name group.

How to Configure Sort Parameters via the Python API

When building queries programmatically or creating custom endpoints, you can pass sorting configuration directly to FastCRUD methods using sort_columns and sort_orders parameters.

Using FastCRUD.select()

The select method accepts explicit sort parameters that bypass HTTP parsing:

from fastcrud import FastCRUD
from myapp.models import User
from myapp.schemas import UserReadSchema

user_crud = FastCRUD(User)

# Equivalent to ?sort=age,-name

stmt = await user_crud.select(
    schema_to_select=UserReadSchema,
    sort_columns=["age", "name"],
    sort_orders=["asc", "desc"],
    is_active=True,
)

result = await db.execute(stmt)
users = result.scalars().all()

Using FastCRUD.get_multi()

For direct CRUD operations with sorting:

users = await user_crud.get_multi(
    db,
    sort_columns=["created_at"],
    sort_orders=["desc"],
    limit=10
)

Sorting Validation and Edge Cases

FastCRUD implements strict validation to prevent invalid sort configurations from reaching your database.

Column Validation

The SortProcessor validates every column name against your SQLAlchemy model using get_model_column from fastcrud/introspection.py. If you request a non-existent column:

curl "http://localhost:8000/items?sort=nonexistent_field"

FastCRUD raises sqlalchemy.exc.ArgumentError with a clear message indicating the invalid column.

Order Keyword Validation

Valid sort orders are strictly "asc" or "desc" (case-insensitive). Passing invalid values like "ascending" triggers a ValueError in sorting.py (lines 79-82).

Handling Mismatched Lists

If you manually pass sort_columns and sort_orders with different lengths, FastCRUD raises ValueError immediately (lines 71-75 of sorting.py).

Empty Sort Parameters

When no sort parameter is provided, FastCRUD does not append an ORDER BY clause, allowing the database to return results in its default order (typically insertion order or primary key sequence).

Implementing Custom Default Sorting

FastCRUD does not enforce a global default sort order, giving you full control per request. To implement a permanent default for specific endpoints, wrap the CRUD call:

from fastapi import APIRouter, Depends
from fastcrud.core import PaginatedRequestQuery

router = APIRouter()

@router.get("/users/active")
async def get_active_users(
    db: AsyncSession = Depends(get_db),
    query: PaginatedRequestQuery = Depends(),
):
    # Force sorting by created_at descending if no explicit sort is supplied

    if not query.sort:
        sort_columns = ["created_at"]
        sort_orders = ["desc"]
    else:
        # Parse the query.sort string manually or use EndpointCreator logic

        sort_columns, sort_orders = parse_sort_query(query.sort)
    
    return await user_crud.get_multi(
        db,
        sort_columns=sort_columns,
        sort_orders=sort_orders,
        is_active=True,
    )

Summary

• HTTP Query Syntax: Use ?sort=field1,-field2 where hyphens indicate descending order and commas separate multiple fields.
• Python API: Pass sort_columns and sort_orders lists directly to FastCRUD.select() or FastCRUD.get_multi().
• Validation: FastCRUD validates column names against your model and raises ArgumentError for invalid columns or ValueError for mismatched list lengths.
• Pipeline Location: Sorting logic resides in fastcrud/core/query/sorting.py (SortProcessor), fastcrud/endpoint/endpoint_creator.py (HTTP parsing), and fastcrud/core/__init__.py (PaginatedRequestQuery).

Frequently Asked Questions

How do I sort by multiple columns in FastCRUD?

Separate column names with commas in the sort query parameter. Use a hyphen prefix for descending order. For example, ?sort=department,-salary sorts by department ascending, then by salary descending. When using the Python API, pass parallel lists: sort_columns=["department", "salary"] and sort_orders=["asc", "desc"].

What happens if I provide an invalid column name for sorting?

FastCRUD validates all sort columns against your SQLAlchemy model using the get_model_column utility. If you request a column that does not exist on the model, the SortProcessor.apply_sorting_to_statement method in fastcrud/core/query/sorting.py raises a sqlalchemy.exc.ArgumentError with a descriptive error message before any database query executes.

Can I set a default sort order for all requests?

FastCRUD does not provide a global configuration for default sorting. To enforce a default order, check the sort attribute of PaginatedRequestQuery in your endpoint and supply default sort_columns and sort_orders values when calling FastCRUD.get_multi or FastCRUD.select. This pattern allows different endpoints to have different default behaviors while maintaining explicit control.

Why do my sort_orders and sort_columns need to be the same length?

The SortProcessor in fastcrud/core/query/sorting.py iterates over both lists simultaneously using zip. If the lengths differ, the processor raises a ValueError immediately (lines 71-75) to prevent ambiguous or incomplete sorting instructions. Always ensure that every column in sort_columns has a corresponding order in sort_orders, even if you must repeat "asc" or "desc" for multiple fields.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}