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

> Learn to filter joined data in FastCRUD with dot-notation and FilterProcessor. This guide explains how to easily query SQLAlchemy relationships for powerful data retrieval.

- Repository: [Benav Labs/fastcrud](https://github.com/benavlabs/fastcrud)
- Tags: how-to-guide
- Published: 2026-02-26

---

**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`](https://github.com/benavlabs/fastcrud/blob/main/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`](https://github.com/benavlabs/fastcrud/blob/main/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`](https://github.com/benavlabs/fastcrud/blob/main/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`](https://github.com/benavlabs/fastcrud/blob/main/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`](https://github.com/benavlabs/fastcrud/blob/main/fastcrud/fast_crud.py). The `get_multi_joined` method then utilizes `JoinProcessor` from [`fastcrud/core/join_processing.py`](https://github.com/benavlabs/fastcrud/blob/main/fastcrud/core/join_processing.py) and `SQLQueryBuilder` from [`fastcrud/core/query/builder.py`](https://github.com/benavlabs/fastcrud/blob/main/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.

```python

# 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.

```python

# 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.

```python

# 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`.

```python
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`](https://github.com/benavlabs/fastcrud/blob/main/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.

```python
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`](https://github.com/benavlabs/fastcrud/blob/main/processor.py)), and delegate via `handle_joined_filters_delegation` in [`execution.py`](https://github.com/benavlabs/fastcrud/blob/main/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`](https://github.com/benavlabs/fastcrud/blob/main/fastcrud/crud/execution.py), which calls `get_multi_joined` in [`fastcrud/fast_crud.py`](https://github.com/benavlabs/fastcrud/blob/main/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`](https://github.com/benavlabs/fastcrud/blob/main/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.