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

> Learn to implement count operations with FastCRUD using its async method. Explore joins, distinct counts, and sub-queries with detailed examples for efficient data retrieval.

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

---

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

```python

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

```python
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:

```python
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`](https://github.com/benavlabs/fastcrud/blob/main/fastcrud/core/config/join_configs.py) (lines 79-102) inside `get_multi_joined`:

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