How to Configure Nested Join Responses in FastCRUD

Enable nested join responses in FastCRUD by setting nest_joins=True in get_joined() or get_multi_joined() methods, or configure it globally in your CrudRouter to return related data as hierarchical dictionaries instead of flat prefixed columns.

FastCRUD, the SQLAlchemy CRUD generator for FastAPI (benavlabs/fastcrud), supports two response formats for joined queries: flat (where joined columns are prefixed and merged into the parent record) and nested (where related entities are grouped under dedicated keys). Configuring nested join responses allows your API to return intuitive JSON structures that mirror your database relationships, such as a user object containing a list of posts or a single tier record.

Understanding Nested vs Flat Join Responses

When you execute a joined query in FastCRUD, the default behavior (nest_joins=False) flattens all columns into a single dictionary using prefixed keys. For example, a user joined with a tier might return {"id": 1, "name": "Alice", "tier_id": 2, "tier_name": "Premium"}.

With nesting enabled (nest_joins=True), FastCRUD delegates the result transformation to process_joined_data, which groups columns sharing the same join_prefix into sub-dictionaries. The same query returns {"id": 1, "name": "Alice", "tier": {"id": 2, "name": "Premium"}}.

This distinction is critical for one-to-many relationships, which cannot be represented in flat format without data duplication and must use nesting.

Core Mechanics of Nested Join Configuration

Method Signatures and Default Behavior

The nest_joins parameter defaults to False across all CRUD helpers. In fastcrud/crud/fast_crud.py, the overload definitions for get_multi_joined and get_joined (lines 1900‑1940) explicitly include this flag in their signatures. When calling these methods, you opt into nesting by passing nest_joins=True:

user = await user_crud.get_joined(
    db=db,
    nest_joins=True,  # Enable hierarchical response structure

    id=1
)

SQL Generation with Temporary Prefixes

When nest_joins=True, the query builder instructs the internal execution engine to use temporary column prefixes. At lines 1887‑1889 of fastcrud/crud/fast_crud.py, the code passes use_temporary_prefix=nest_joins to the low-level execute_query helper. This ensures that SQL column aliases are generated in a way that process_joined_data can later identify and group related columns correctly.

Safety Validation for One-to-Many Relationships

FastCRUD enforces strict validation to prevent invalid configurations. If you attempt to perform a one-to-many join with nest_joins=False, the library raises a clear error at lines 1900‑1902:

raise ValueError("Cannot use one-to-many relationship with nest_joins=False")

This safety check ensures that relationship cardinality always matches the response format, preventing silent data corruption or loss of child records.

Result Transformation and Aggregation

The nesting logic relies on two key internal functions. First, process_joined_data (called at lines 1911‑1914) groups the flat SQL result set into nested dictionaries based on join_prefix values. For one-to-many relationships with a nested_limit, the fetch_and_merge_one_to_many helper (lines 1914‑1926) fetches child rows separately and merges them back into the parent record as a list.

This post-processing occurs after the SQL execution but before Pydantic validation, ensuring your schemas receive correctly structured data.

Practical Implementation Examples

Auto-Detect Relationships with Nesting

For rapid development, enable auto-detection of foreign key relationships while forcing nested output:

user = await user_crud.get_joined(
    db=db,
    auto_detect_relationships=True,   # Detect all FK relationships automatically

    nest_joins=True,                  # Return nested structure

    id=1,
)

# Returns:

# {

#   "id": 1,

#   "name": "Alice",

#   "tier": {"id": 2, "name": "Premium"},

#   "posts": [{"id": 10, "title": "Hello World"}, ...]

# }

Source: Implementation follows the pattern documented in docs/advanced/joins.md (lines 31‑40).

Explicit JoinConfig for Complex Scenarios

For production applications requiring precise control over relationship types and limits, use explicit JoinConfig objects:

from fastcrud.types import JoinConfig

joins = [
    JoinConfig(
        model=Post,
        join_on=User.id == Post.author_id,
        join_prefix="posts_",              # Becomes the key "posts" in output

        relationship_type="one-to-many",
        nested_limit=5,                   # LIMIT 5 per parent at SQL level

    ),
    JoinConfig(
        model=Tier,
        join_on=User.tier_id == Tier.id,
        join_prefix="tier_",
        relationship_type="one-to-one",
    ),
]

users = await user_crud.get_multi_joined(
    db=db,
    schema_to_select=UserSchema,
    nest_joins=True,
    joins_config=joins,
    offset=0,
    limit=20,
)

The join_prefix determines the output key (the trailing underscore is stripped), while relationship_type dictates whether FastCRUD treats the join as a single object or a list.

Source: docs/advanced/joins.md (lines 42‑56).

Global Router Configuration

To apply nested responses across all endpoints generated by FastCRUD, configure the CrudRouter or EndpointCreator with the nest_joins flag. In fastcrud/endpoint/crud_router.py (line 50), the default is set to True, but you can explicitly declare it:

from fastcrud.endpoint import CrudRouter

router = CrudRouter(
    crud=user_crud,
    schema=UserSchema,
    create_schema=UserCreate,
    update_schema=UserUpdate,
    nest_joins=True,          # All GET endpoints return nested structures

)

app.include_router(router, prefix="/users")

Under the hood, EndpointCreator forwards this value to CRUD calls at line 639 of fastcrud/endpoint/endpoint_creator.py, ensuring consistency between manual CRUD operations and auto-generated endpoints.

When to Use Nested Join Responses

• One-to-many relationships: Required (nest_joins=True) because child rows must be grouped into a list to avoid duplication.
• API payloads mirroring object graphs: Nesting produces JSON that directly represents your relational model (e.g., user → posts, project → participants), reducing client-side processing.
• Read-heavy use cases: While nesting adds post-processing overhead via process_joined_data and fetch_and_merge_one_to_many, the cleaner API contract benefits read operations. For high-throughput write paths, consider flat results with nest_joins=False.

Summary

• FastCRUD supports nested (nest_joins=True) and flat (nest_joins=False) response formats for joined queries.
• One-to-many relationships strictly require nesting and will raise a ValueError if attempted with flat formatting.
• Enable nesting at the method level via get_joined()/get_multi_joined(), or globally via CrudRouter configuration.
• Use JoinConfig for explicit control over relationship types, prefixes, and nested limits.
• The transformation logic resides in fastcrud/crud/fast_crud.py, utilizing process_joined_data and fetch_and_merge_one_to_many for result aggregation.

Frequently Asked Questions

Does FastCRUD require nested joins for one-to-many relationships?

Yes. According to the validation logic in fastcrud/crud/fast_crud.py (lines 1900‑1902), attempting a one-to-many join with nest_joins=False raises a ValueError stating "Cannot use one-to-many relationship with nest_joins=False". This ensures that child records are properly grouped into lists under their parent key.

What is the performance impact of enabling nest_joins?

Enabling nest_joins=True triggers additional post-processing in Python via process_joined_data and, for limited one-to-many queries, fetch_and_merge_one_to_many. While this adds computational overhead compared to flat results, the impact is typically negligible for moderate result sets. For high-throughput applications processing thousands of records, benchmark both approaches to determine the optimal configuration for your specific workload.

Can I mix nested and flat joins in the same query?

No. The nest_joins flag applies to the entire query result. However, you can control individual join presentation through the join_prefix parameter in JoinConfig. When nesting is enabled, all joined relationships will be nested under their respective prefixes; when disabled, all appear as flat prefixed columns. To achieve mixed formats, execute separate queries or manually transform the results after retrieval.

How does JoinConfig's join_prefix affect the output structure?

The join_prefix value in JoinConfig determines the key name under which nested data appears in the final response. FastCRUD strips the trailing underscore from the prefix (e.g., "posts_" becomes "posts"). This prefix serves as the grouping mechanism for process_joined_data when aggregating flat SQL results into nested dictionaries, as implemented in fastcrud/crud/fast_crud.py (lines 1911‑1914).

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