How to Filter Joined Data in FastCRUD Queries: Complete Guide for SQLAlchemy Relationships

FastCRUD filters joined data by parsing dot‑notation keyword arguments (e.g., articles.title__ilike) in FilterProcessor, separating them from base model filters via separate_joined_filters, and delegating query execution to get_multi_joined which constructs the SQLAlchemy JOIN automatically.

FastCRUD, maintained in the benavlabs/fastcrud repository, provides a declarative way to query SQLAlchemy relationships without writing raw SQL. By leveraging the FilterProcessor engine in fastcrud/core/filtering/processor.py, you can apply filters to related tables using simple Python kwargs while the library handles complex JOIN construction and result nesting.

How FastCRUD Processes Joined Filters

The filtering engine operates through three distinct stages defined in fastcrud/core/filtering/processor.py.

Stage 1: Parsing Filter Arguments

The FilterProcessor.parse_filters method converts user‑supplied kwargs into SQLAlchemy ColumnElement objects. It detects joined‑field filters by identifying dot notation (e.g., relationship.field) and validates the format using validate_joined_filter_format from fastcrud/core/filtering/validators.py. This stage handles operators like __gt, __lt, __in, and logical modifiers such as _or and _not.

Stage 2: Separating Joined Filters

Inside FilterProcessor.separate_joined_filters (lines ≈ 378‑390), the engine walks the kwargs dictionary and extracts any key containing a dot. It returns two dictionaries: one for regular base‑model filters and another mapping relationships to their specific filter conditions. This separation ensures that base table predicates and JOIN conditions are handled correctly.

Stage 3: Delegation and Execution

When joined filters are present, FastCRUD.get_multi detects them and calls handle_joined_filters_delegation in fastcrud/crud/execution.py. This method resolves the relationship attribute on the base model, discovers the related class, and forwards the request to FastCRUD.get_multi_joined in fastcrud/fast_crud.py. The get_multi_joined method then utilizes JoinProcessor from fastcrud/core/join_processing.py and SQLQueryBuilder from fastcrud/core/query/builder.py to construct the final SELECT statement with proper JOINs, pagination, and sorting.

Basic Joined Filter Syntax

Filter related records by referencing the relationship name followed by a dot and the field name. FastCRUD automatically constructs the necessary INNER or OUTER JOIN.

# Filter Authors by Article title (One-to-Many relationship)

authors = await AuthorCRUD.get_multi(
    db,
    **{
        "articles.title__ilike": "%fastcrud%",  # relationship.field__operator

        "name": "John Doe",                      # Base model filter

    },
    offset=0,
    limit=20,
)

In this example, articles is the relationship attribute on the Author model. FastCRUD parses articles.title__ilike into Article.title.ilike("%fastcrud%") and joins the articles table automatically.

Using Comparison Operators on Joined Fields

Apply standard SQLAlchemy operators to related columns using double‑underscore suffixes after the field name.

# Find Orders with LineItems having quantity > 5

orders = await OrderCRUD.get_multi(
    db,
    **{
        "order_items.quantity__gt": 5,  # quantity greater than 5

        "status": "shipped",
    }
)

Supported operators include __eq, __ne, __gt, __ge, __lt, __le, __in, __not_in, __like, __ilike, and __contains.

OR Conditions Across Relationships

Use the _or modifier to create disjunctive conditions spanning joined tables.

# Customers with addresses in New York City OR New York State

customers = await CustomerCRUD.get_multi(
    db,
    _or={
        "addresses.city": "New York",
        "addresses.state": "NY"
    }
)

The _or dictionary creates an SQL OR clause that spans fields on the related addresses table.

Explicit Multi‑Join Queries with get_multi_joined

For queries requiring multiple relationships or custom join conditions, bypass automatic delegation and call get_multi_joined directly with JoinConfig.

from fastcrud import JoinConfig

# Projects filtered by Participant name AND Client company name

projects = await ProjectCRUD.get_multi_joined(
    db,
    offset=0,
    limit=10,
    join_model=Participant,
    join_filters={"name": "Alice"},
    joins_config=[
        JoinConfig(
            model=Client,
            join_on=Project.client_id == Client.id,
            join_type="inner",
            filters={"company_name__ilike": "%Acme%"},
        )
    ],
)

This approach uses fastcrud/crud/execution.py to process multiple join configurations simultaneously.

Returning Pydantic Models from Joined Queries

Control the response format by specifying a Pydantic schema and enabling model return.

orders = await OrderCRUD.get_multi(
    db,
    **{
        "articles.price__gt": 100,
    },
    schema_to_select=OrderReadSchema,  # Schema with nested ArticleRead

    return_as_model=True,
)

When return_as_model=True, FastCRUD formats each row using format_multi_response, preserving nested structures created by the JOIN operations.

Summary

• Dot notation (relationship.field) triggers automatic JOIN handling in FilterProcessor.parse_filters.
• Three-stage pipeline: Parse filters, separate them in separate_joined_filters (lines ≈ 378‑390 of processor.py), and delegate via handle_joined_filters_delegation in execution.py.
• Operators like __gt, __ilike, and modifiers like _or work identically on joined and base fields.
• Explicit control: Use get_multi_joined with JoinConfig for complex multi‑relationship queries.
• Type safety: Pass schema_to_select and return_as_model=True to receive validated Pydantic objects instead of dictionaries.

Frequently Asked Questions

How does FastCRUD construct the SQL JOIN when filtering related tables?

FastCRUD resolves the relationship attribute specified before the dot (e.g., articles) on the base SQLAlchemy model. It then delegates to handle_joined_filters_delegation in fastcrud/crud/execution.py, which calls get_multi_joined in fastcrud/fast_crud.py. This method uses JoinProcessor and SQLQueryBuilder to generate the SQLAlchemy join() clause, apply the parsed filters as WHERE conditions on the related table, and execute the query with proper aliasing to avoid collisions.

Can I filter on multiple different relationships in a single get_multi call?

Yes. You can include filters for multiple relationships in the same kwargs dictionary (e.g., articles.title="Foo" and comments.status="active"). FastCRUD will process all joined filters through separate_joined_filters and handle them accordingly. For complex join types (e.g., specifying INNER vs OUTER joins) or joining the same relationship twice with different aliases, use the explicit get_multi_joined method with a list of JoinConfig objects.

What validation exists for joined filter syntax?

FastCRUD validates joined filter keys using validate_joined_filter_format in fastcrud/core/filtering/validators.py. This ensures that dot‑notation keys conform to the relationship.field or relationship.field__operator pattern. Invalid syntax, such as empty relationship names or malformed operators, raises a validation error before query construction begins.

Is there a performance difference between automatic joined filtering and using get_multi_joined directly?

Both paths ultimately execute through get_multi_joined; the automatic path simply adds a thin delegation layer via handle_joined_filters_delegation. The performance overhead is negligible, but explicit get_multi_joined calls allow you to specify join_type (e.g., "left" vs "inner") and custom join_on conditions, which can significantly impact query execution plans for large datasets.

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