Querying Data & Filtering

Relevant source files

The following files were used as context for generating this wiki page:

• src/lib.rs
• sea-orm-sync/src/lib.rs
• CLAUDE.md
• README.md
• src/docs.rs
• sea-orm-sync/src/docs.rs

SeaORM provides a robust and expressive API for querying and filtering data from relational databases. It abstracts complex SQL operations into high-level Rust constructs, allowing developers to perform basic CRUD operations, complex joins, and advanced filtering with compile-time type safety. The system supports both asynchronous execution via SQLx and synchronous execution for lightweight environments.

The querying system is built upon SeaQuery, enabling dynamic query building without sacrificing performance. Key features include an Entity Loader that intelligently handles relationships to eliminate the N+1 problem, support for Partial Models to prevent overfetching, and a raw_sql! macro for cases requiring hand-crafted SQL statements.

Sources: src/lib.rs:35-50, sea-orm-sync/src/lib.rs:35-50, src/docs.rs:65-70

Core Querying Mechanics

The primary entry point for querying is the Entity::find() method, which initiates a select query. SeaORM supports various execution modes including finding all records, finding a single record by ID, or streaming results.

Find Operations

The find API offers several methods to retrieve data based on the desired output format and cardinality.

Method	Description	Return Type
all(db)	Retrieves all records matching the query	Vec<Model>
one(db)	Retrieves at most one record matching the query	Option<Model>
find_by_id(id)	Shorthand for filtering by primary key	Select<Entity>
find_by_statement	Executes a query from a raw SQL statement	SelectorRaw

Sources: src/lib.rs:180-200, sea-orm-sync/src/lib.rs:180-200

Logic Flow for Find Execution

The following diagram illustrates how a query request travels from the high-level Entity API down to the database executor.

This diagram shows the transformation of a high-level find call into mapped Rust models.
Sources: src/lib.rs:180-210, src/docs.rs:100-110

Filtering and Expressions

Filtering in SeaORM 2.0 is primarily performed using the .filter() method on a Select object. The framework introduced Strongly-Typed Columns via the COLUMN constant to ensure type safety at compile time.

Filter Syntax

Filters are constructed using column expressions and operators such as .eq(), .contains(), .like(), and .in_subquery().

// 2.0 Preferred Pattern - Strongly-Typed
Entity::find().filter(user::COLUMN.name.contains("Bob"))

// Using Condition for complex logic
Entity::find().filter(
    Condition::any()
        .add(user::COLUMN.id.eq(1))
        .add(user::COLUMN.name.like("%Alice%"))
)

Sources: CLAUDE.md:65-75, src/docs.rs:73-85

Relationship Filtering

SeaORM allows traversing relationships during the filtering process. For example, finding entities based on the properties of their related models.

This diagram shows the process of joining a related table to apply a filter on its columns.
Sources: src/lib.rs:205-215, sea-orm-sync/src/lib.rs:205-215

Advanced Query Features

Entity Loader (Eager Loading)

The Entity Loader is designed to solve the N+1 query problem by intelligently using JOIN for 1-1 relations and a data loader (multiple targeted queries) for 1-N relations.

// user -> profile (1-1 uses JOIN)
// user -> post (1-N uses data loader)
let user = user::Entity::load()
    .filter_by_id(42)
    .with(profile::Entity)
    .with(post::Entity)
    .one(db)
    .await?;

Sources: src/lib.rs:88-100, CLAUDE.md:105-115

Partial Models

Partial models allow developers to select only a subset of columns from a table, which is useful for optimizing performance and reducing memory usage.

#[derive(DerivePartialModel)]
#[sea_orm(entity = "cake::Entity")]
struct CakePartial {
    id: i32,
    name: String,
}

let results = Cake::find()
    .into_partial_model::<CakePartial>()
    .all(db)
    .await?;

Sources: src/lib.rs:225-240, sea-orm-sync/src/lib.rs:225-240

Raw SQL Queries

For complex queries that cannot be easily expressed through the ORM, the raw_sql! macro provides a safe way to write SQL with parameter interpolation.

let ids = [1, 2, 3];
let user: Option<user::Model> = user::Entity::find()
    .from_raw_sql(raw_sql!(
        Postgres,
        r#"SELECT "id", "name" FROM "user" WHERE "id" IN ({..ids})"#
    ))
    .one(db)
    .await?;

Sources: src/lib.rs:155-170, CLAUDE.md:165-170

Comparison of Query Types

Query Type	Best Use Case	Implementation Method
Simple Find	Standard CRUD / Direct lookups	Entity::find()
Eager Loading	Retrieving entity graphs with relations	Entity::load().with(...)
Partial Select	API responses requiring limited fields	.into_partial_model()
Raw SQL	Native DB features or extreme optimization	raw_sql!
Paginated	Large datasets in UI/APIs	.paginate(db, page_size)

Sources: src/lib.rs:215-260, src/docs.rs:125-140

Summary of Query Execution Flow

The sequence of events for a complex query involving eager loading and filtering is represented below:

This diagram outlines how SeaORM splits a request into multiple efficient queries to populate a complex object graph.
Sources: src/lib.rs:105-125, sea-orm-sync/src/lib.rs:105-125

SeaORM 2.0 focuses on developer experience by providing a type-safe, fluent API that balances the convenience of an ORM with the power of raw SQL when needed. The combination of the Entity Loader and Partial Models ensures that data fetching is both ergonomic and efficient.

Sources: CLAUDE.md:1-20, README.md:10-30