How to Implement Count Operations with FastCRUD: Complete Guide with Examples

FastCRUD provides an asynchronous count method that returns the number of rows matching supplied filters, with support for complex joins, distinct counting, and related-object sub-queries via JoinConfig and CountConfig classes.

FastCRUD is a Python library for rapidly building CRUD endpoints with SQLAlchemy and FastAPI. When you need to implement count operations with FastCRUD, the library offers type-safe aggregation through its core FastCRUD class defined in fastcrud/crud/fast_crud.py. This guide covers the three operational modes—simple filtering, join-enabled counting, and per-row sub-queries—using actual source implementations and runnable examples.

Core Count Implementation

The count method in fastcrud/crud/fast_crud.py (lines 1098-1120) handles all counting logic through a structured four-step pipeline:

1. Filter Parsing: self._filter_processor.parse_filters(**kwargs) converts field lookups like age__gt=30 into SQLAlchemy filter expressions.
2. Join Construction: When joins_config is provided, _query_builder.prepare_joins validates primary keys and applies relationship joins.
3. Distinct Handling: If distinct_on_primary=True, the query applies SELECT DISTINCT on the primary key to eliminate duplicates from one-to-many relationships.
4. Execution: await db.scalar(count_query) returns an integer; if no result is found, the method raises ValueError("Could not find the count.").

Simple Filter Counting

For basic aggregation without relationships, invoke count with filter arguments:

# Assume user_crud = FastCRUD(User) and db is an async SQLAlchemy session

total = await user_crud.count(db, age__gt=30, is_active=True)
print(f"Active users over 30: {total}")

The internal FilterProcessor converts kwargs into WHERE age > 30 AND is_active = true clauses, executing a single SELECT COUNT(*) query.

Counting with Joins Using JoinConfig

When you must count records based on conditions in related tables, pass a list of JoinConfig objects defined in fastcrud/core/config/join_configs.py (lines 14-30).

Many-to-Many Relationship Counting

This example counts projects that have at least one participant named "Jane Doe" by joining through an association table:

from fastcrud import FastCRUD, JoinConfig
from models import Project, ProjectsParticipantsAssociation, Participant

project_crud = FastCRUD(Project)

joins_config = [
    JoinConfig(
        model=ProjectsParticipantsAssociation,
        join_on=Project.id == ProjectsParticipantsAssociation.project_id,
        join_type="inner",
    ),
    JoinConfig(
        model=Participant,
        join_on=ProjectsParticipantsAssociation.participant_id == Participant.id,
        join_type="inner",
        filters={"name": "Jane Doe"},
    ),
]

count = await project_crud.count(db, joins_config=joins_config)
print(f"Projects with Jane Doe: {count}")

Distinct Counting for One-to-Many Joins

One-to-many joins can inflate row counts. Use distinct_on_primary=True to count each primary record only once:

count = await project_crud.count(
    db,
    joins_config=joins_config,
    distinct_on_primary=True
)

This modifies the SQL to select distinct primary keys before applying the aggregate function, preventing duplicate counts caused by multiple related records.

Embedding Related Object Counts with CountConfig

To retrieve per-row counts of related objects within a multi-record query, use CountConfig from fastcrud/core/config/join_configs.py (lines 79-102) inside get_multi_joined:

from fastcrud import FastCRUD, CountConfig

search_crud = FastCRUD(Search)

video_count_cfg = CountConfig(
    model=Video,
    join_on=(Video.id == VideoSearchAssociation.video_id) &
            (VideoSearchAssociation.search_id == Search.id),
    alias="videos_count",
)

results = await search_crud.get_multi_joined(
    db,
    counts_config=[video_count_cfg],
    return_total_count=True,
)

for row in results["data"]:
    print(f"Search {row['id']} has {row['videos_count']} videos")

CountConfig generates scalar sub-queries that attach as computed columns to each result row, distinct from the standalone count method which returns a single aggregate integer.

Summary

• The count method in fastcrud/crud/fast_crud.py provides asynchronous, filtered counting with automatic SQL generation via FilterProcessor.
• Simple counts use field lookups processed into SQLAlchemy expressions without requiring join configuration.
• Join-enabled counts utilize JoinConfig objects to traverse relationships while filtering on joined table columns.
• Distinct counting via distinct_on_primary=True ensures accurate tallies when one-to-many joins duplicate primary keys.
• Per-row aggregates employ CountConfig with get_multi_joined to embed sub-query counts alongside primary record data.
• All count operations execute through await db.scalar(count_query) and raise ValueError when the database returns no scalar result.

Frequently Asked Questions

How do I count distinct records when using joins in FastCRUD?

Pass distinct_on_primary=True to the count method. This parameter instructs FastCRUD to wrap the primary key selection in a SELECT DISTINCT clause before counting, ensuring each parent record is counted only once regardless of how many child records exist in one-to-many relationships.

What is the difference between JoinConfig and CountConfig in FastCRUD?

JoinConfig modifies the main counting query to join tables and filter based on related data, returning a single integer count of matching primary records. CountConfig creates scalar sub-queries that execute alongside get_multi_joined, attaching related object counts as extra columns to each row in the result set without affecting the main record selection.

Where does FastCRUD implement the count method and how does it handle errors?

The implementation resides in fastcrud/crud/fast_crud.py between lines 1098-1120. After building and executing the query via await db.scalar(count_query), the method validates the result; if the scalar value is missing, it raises ValueError("Could not find the count.") to indicate a database or query failure.

Can I filter on joined tables when counting with FastCRUD?

Yes. Include filters directly within the JoinConfig objects passed to the joins_config parameter. The internal _query_builder.prepare_joins method applies these filters to the joined tables while constructing the count query, allowing you to constrain counts based on conditions in related models.

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