How to Configure One-to-Many Relationships in FastCRUD Joins

FastCRUD enables you to fetch parent records with their related child rows by setting relationship_type="one-to-many" in JoinConfig and enabling nest_joins=True to return nested lists.

FastCRUD provides a SQL-level solution for retrieving hierarchical data without the N+1 query problem. To configure one-to-many relationships in FastCRUD joins, you define a JoinConfig with explicit relationship metadata and execute it through the get_joined or get_multi_joined methods. This approach leverages window functions in SQL to group and limit child records efficiently, keeping memory usage low even with large datasets.

Defining JoinConfig for One-to-Many Relationships

The foundation of a one-to-many join is the JoinConfig model defined in fastcrud/core/config/join_configs.py. Unlike one-to-one joins, one-to-many relationships require setting relationship_type="one-to-many" to indicate that a single parent record may map to multiple child records.

from fastcrud import FastCRUD, JoinConfig
from yourapp.models import User, Post

join_config = JoinConfig(
    model=Post,
    join_on=User.id == Post.author_id,
    join_prefix="posts_",           # Results in nested key "posts"

    relationship_type="one-to-many",
    nested_limit=10,                # SQL-level limit per parent

    sort_columns=["created_at"],    # Sort before limiting

    sort_orders=["desc"]
)

Executing the Join with nest_joins

After defining the configuration, pass it to get_joined or get_multi_joined with nest_joins=True. This parameter is mandatory for one-to-many relationships; omitting it raises a ValueError according to the FastCRUD source implementation.

user_crud = FastCRUD(User)

result = await user_crud.get_joined(
    db=db,
    joins_config=[join_config],
    nest_joins=True,    # Required for one-to-many

    id=1
)

Understanding the Result Structure

When nesting is enabled, FastCRUD returns the parent record with child rows aggregated into a list under the key derived from join_prefix. If the prefix is posts_, the output key becomes posts with the trailing underscore removed.

{
    "id": 1,
    "username": "alice",
    "posts": [
        {"id": 101, "title": "First Post", "created_at": "2024-01-15T10:00:00"},
        {"id": 102, "title": "Second Post", "created_at": "2024-01-14T09:00:00"}
    ]
}

Advanced One-to-Many Configuration Options

Limiting and Sorting Child Records

Use nested_limit, sort_columns, and sort_orders to implement SQL-level pagination of child rows. FastCRUD uses a ROW_NUMBER() window function to apply these limits in the database rather than filtering in Python, which optimizes memory usage for large collections.

Filtering Child Collections

The filters parameter accepts a dictionary of conditions applied only to the child model. This allows you to fetch, for example, only published posts for each author without retrieving the full dataset.

Aliasing for Complex Schemas

When joining the same child table multiple times or handling self-referential relationships, use the alias parameter to provide an AliasedClass that distinguishes between the different join contexts.

Safety Defaults and Auto-Detection

By default, FastCRUD excludes one-to-many relationships when auto_detect_relationships=True to prevent unbounded data fetching. You must explicitly opt-in by setting include_one_to_many=True or by providing explicit JoinConfig objects that enumerate the desired relationships, as documented in docs/advanced/joins.md.

Summary

• Configure one-to-many joins using JoinConfig with relationship_type="one-to-many" defined in fastcrud/core/config/join_configs.py.
• Always set nest_joins=True when calling get_joined or get_multi_joined; otherwise, FastCRUD raises a ValueError.
• Use nested_limit and sort_columns to handle large child collections efficiently via SQL window functions.
• Enable include_one_to_many=True when relying on auto_detect_relationships to include one-to-many relationships in automatic detection.

Frequently Asked Questions

What happens if I forget to set nest_joins=True?

FastCRUD raises a ValueError because one-to-many relationships require nesting to structure the child records as a list within the parent object. Without nesting, the flat result format cannot represent multiple child rows per parent.

How do I limit the number of child records returned per parent?

Set the nested_limit parameter in your JoinConfig. FastCRUD implements this using a ROW_NUMBER() window function in SQL, limiting results at the database level before they reach Python.

Can I join multiple one-to-many relationships in a single query?

Yes. Pass multiple JoinConfig instances in the joins_config list, each with distinct join_prefix values. Each relationship will appear as a separate nested list in the resulting dictionary.

Why are one-to-many relationships excluded by default in FastCRUD?

When auto_detect_relationships is enabled, FastCRUD defaults to excluding one-to-many relationships to prevent accidental retrieval of massive datasets. You must explicitly enable them via include_one_to_many=True or explicit JoinConfig definitions.

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