Advanced Queries

Aggregate Queries

let count: usize = db.select::<User>().count(|u| u.id).await?;

let total: Option<i32> = db.select::<Product>().sum(|p| p.price).await?;

let avg: Option<f64> = db.select::<User>().avg(|u| u.age).await?;

let max: Option<i32> = db.select::<User>().max(|u| u.age).await?;

let min: Option<i32> = db.select::<User>().min(|u| u.age).await?;

With conditions:

let adult_count: usize = db
    .select::<User>()
    .filter(|u| u.age.ge(18))
    .count(|u| u.id)
    .await?;

GROUP BY and HAVING Queries

Basic Grouping

use ormer::Select;

let sql = Select::<User>::new()
    .select_column(|u| u.id.count())
    .group_by(|u| u.age)
    .to_sql();

The builder's no-argument to_sql() is a preview only: it uses the default dialect and performs no capability validation, so its output changes with the enabled features. For exact SQL matching real execution, use the executor's to_sql() (actual dialect with validation), e.g. db.select::<User>().to_sql()?.

Multiple Columns + Grouping

let sql = Select::<User>::new()
    .select_column(|u| (u.department, u.id.count()))
    .group_by(|u| u.department)
    .to_sql();

HAVING Condition Filter

let sql = Select::<User>::new()
    .select_column(|u| (u.department, u.id.count()))
    .group_by(|u| u.department)
    .having(|u| u.id.count().gt(5))
    .to_sql();

Multi-Column Grouping

let sql = Select::<User>::new()
    .select_column(|u| (u.department, u.age, u.id.count(), u.score.avg()))
    .group_by(|u| (u.department, u.age))
    .to_sql();

Complete Query: WHERE + GROUP BY + HAVING + ORDER BY + LIMIT

let sql = Select::<User>::new()
    .filter(|u| u.age.ge(18))
    .select_column(|u| (u.department, u.id.count(), u.score.avg()))
    .group_by(|u| u.department)
    .having(|u| u.id.count().gt(0))
    .order_by(|u| u.department)
    .range(0..10)
    .to_sql();

Supported Aggregate Functions

  • count() - Count, returns usize
  • sum() - Sum, returns original type (numeric types)
  • avg() - Average, returns f64
  • max() - Maximum, returns original type (numeric types)
  • min() - Minimum, returns original type (numeric types)

Unified Expressions

Fields, projections, filters, ordering, grouping, and selected aggregate features share the same expression AST. Expressions can combine functions, CASE, casts, collations, window functions, and backend operators:

let rows: Vec<(i32, String, String)> = db
    .select::<User>()
    .filter(|u| u.email.to_lower().eq("alice@example.com"))
    .map_to(|u| {
        (
            u.id.alias("user_id"),
            u.email,
            ormer::expr!(match u.status {
                "paid" => "done",
                "new" => "open",
                _ => "other",
            })
            .alias("status_label"),
        )
    })
    .order_by(|u| u.email.collate("nocase").asc())
    .collect()
    .await?;

Aggregate expressions support FILTER, inner ordering, and OVER; backends without FILTER render it as CASE WHEN:

let rows: Vec<(i32, i32)> = db
    .select::<Order>()
    .map_to(|o| {
        (
            o.user_id,
            o.total
                .sum()
                .filter(|o| o.paid.eq(true))
                .over(|w| w.partition_by(o.user_id)),
        )
    })
    .collect()
    .await?;

Grouping supports ROLLUP, CUBE, and GROUPING SETS; PostgreSQL / MSSQL / DuckDB use native syntax, MySQL supports only WITH ROLLUP, and SQLite and ClickHouse reject advanced grouping:

let sql = Select::<Sale>::new()
    .select_column(|s| (s.region, s.amount.sum()))
    .rollup(|s| (s.region, s.city))
    .to_sql();

let sql = Select::<Sale>::new()
    .select_column(|s| (s.region, s.amount.sum()))
    .grouping_sets(|s| ((s.region,), ()))
    .to_sql();

You can also combine row values, JSON paths, full text search, DISTINCT ON, and row locks:

let rows: Vec<User> = db
    .select::<User>()
    .distinct_on(|u| u.org_id)
    .filter(|u| (u.org_id, u.email).eq((org_id, email)))
    .filter(|u| u.profile.json_text("role").eq("admin"))
    .filter(|u| u.bio.matches_text("rust"))
    .for_update()
    .skip_locked()
    .collect()
    .await?;

DISTINCT ON uses native syntax on PostgreSQL / DuckDB and a window-function rewrite elsewhere; ORDER BY must start with the partition keys.

Multi-field full-text search uses a DSL: fields selects the searched columns, query provides the search text, mode picks the match mode (FullTextMode::Natural by default / Boolean / WebSearch), and optionally language sets the language while rank(FullTextRank::Relevance) orders by relevance:

let rows: Vec<Article> = db
    .select::<Article>()
    .fields(|a| (a.title, a.body))
    .query("rust ormer")
    .mode(ormer::FullTextMode::Boolean)
    .rank(ormer::FullTextRank::Relevance)
    .collect()
    .await?;

A serde_json::Value field can declare static JSON paths and array paths:

#[derive(Debug, Model)]
#[table = "users"]
struct User {
    #[primary]
    id: i64,
    #[field(settings.active: bool)]
    #[field(settings.retry_count: i64)]
    #[field(tags[]: String)]
    profile: serde_json::Value,
}

let users: Vec<User> = db
    .select::<User>()
    .filter(|u| u.profile.settings.active.eq(true))
    .filter(|u| u.profile.settings.retry_count.ge(3))
    .filter(|u| u.profile.tags.contains_all(["admin", "write"]))
    .collect()
    .await?;

db.update::<User>()
    .filter(|u| u.id.eq(1))
    .set(|u| u.profile.settings.retry_count.set(3))
    .execute()
    .await?;

JOIN Queries

#[derive(Debug, Model)]
#[table = "users"]
struct User {
    #[primary(auto)]
    id: i32,
    name: String,
}

#[derive(Debug, Model)]
#[table = "roles"]
struct Role {
    #[primary]
    id: i32,
    user_id: i32,
    role_name: String,
}

LEFT JOIN

let user_roles: Vec<(User, Option<Role>)> = db
    .select::<User>()
    .left_join::<Role>(|u, r| u.id.eq(r.user_id))
    .collect()
    .await?;

INNER JOIN

let user_roles: Vec<(User, Role)> = db
    .select::<User>()
    .inner_join::<Role>(|u, r| u.id.eq(r.user_id))
    .collect()
    .await?;

RIGHT JOIN

let user_roles: Vec<(Option<User>, Role)> = db
    .select::<User>()
    .right_join::<Role>(|u, r| u.id.eq(r.user_id))
    .collect()
    .await?;

JOIN with Filter

let admin_users: Vec<(User, Role)> = db
    .select::<User>()
    .inner_join::<Role>(|u, r| u.id.eq(r.user_id))
    .filter(|u| u.name.eq("Alice".to_string()))
    .collect()
    .await?;

JOIN with Right-Table Sorting and Pagination (LATERAL JOIN)

When order_by / order_by_desc or range is used in the JOIN condition, the framework automatically generates LATERAL JOIN SQL to enable sorting and pagination on the right table.

// Right table sorted by role_name desc, take only the first row
let user_roles: Vec<(User, Option<Role>)> = db
    .select::<User>()
    .left_join::<Role>(|u, r| u.id.eq(r.user_id).order_by_desc(r.role_name).range(..1))
    .collect()
    .await?;

// Sort only
let user_roles: Vec<(User, Option<Role>)> = db
    .select::<User>()
    .left_join::<Role>(|u, r| u.id.eq(r.user_id).order_by_desc(r.role_name))
    .collect()
    .await?;

// Pagination only
let user_roles: Vec<(User, Option<Role>)> = db
    .select::<User>()
    .left_join::<Role>(|u, r| u.id.eq(r.user_id).range(..3))
    .collect()
    .await?;

Supported JOIN types: left_join, inner_join, right_join.

Can be combined with filter, order_by / order_by_desc, limit / range, for_update, and other methods on the main query:

let page: Vec<(User, Option<Role>)> = db
    .select::<User>()
    .left_join::<Role>(|u, r| u.id.eq(r.user_id))
    .order_by(|u| u.name)
    .limit(10)
    .collect()
    .await?;

Derived Table JOIN

Select and ProjectionSelect (the return type of select_column / map_to) can be promoted to a derived table with as_model::<R>(). Use ViewModel for projection result types without a primary key.

#[derive(Debug, ormer::ViewModel)]
struct UserTotal {
    user_id: i32,
    total: i64,
}

let totals = db
    .select::<Order>()
    .select_column(|o| (o.user_id, o.amount.sum()))
    .group_by(|o| o.user_id)
    .as_model::<UserTotal>();

let rows: Vec<(User, Option<UserTotal>)> = db
    .select::<User>()
    .left_join_derived(totals, |u, t| u.id.eq(t.user_id))
    .collect()
    .await?;

The same derived table can be used as the main query source:

let hot_users: Vec<UserTotal> = db
    .from_derived(totals)
    .filter(|t| t.total.gt(1000_i64))
    .order_by_desc(|t| t.total)
    .range(..10)
    .collect()
    .await?;

Multi-Table Joins

Two Tables (from)

let users: Vec<User> = db
    .select::<User>()
    .from::<Role>()
    .filter(|u, r| u.id.eq(r.user_id))
    .filter(|_, r| r.role_name.eq("admin".to_string()))
    .collect()
    .await?;

Three Tables (from3)

let users: Vec<User> = db
    .select::<User>()
    .from3::<Role, Permission>()
    .filter(|u, r, p| u.id.eq(r.user_id).and(r.id.eq(p.role_id)))
    .collect()
    .await?;

Four Tables (from4)

let users: Vec<User> = db
    .select::<User>()
    .from4::<Role, Permission, Department>()
    .filter(|u, r, p, d| {
        u.id.eq(r.user_id)
            .and(r.id.eq(p.role_id))
            .and(u.department_id.eq(d.id))
    })
    .collect()
    .await?;

Subqueries

IN Subquery

let subquery = db.select::<Role>().map_to(|r| r.user_id);

let users: Vec<User> = db
    .select::<User>()
    .filter(|u| u.id.is_in(subquery))
    .collect()
    .await?;

EXISTS / NOT EXISTS

Use Select::exists() and Select::not_exists() to build subquery expressions:

let users_with_roles: Vec<User> = db
    .select::<User>()
    .filter(|_u| {
        Select::<Role>::new()
            .filter(|r| r.name.eq("admin"))
            .exists()  // or .not_exists()
    })
    .collect()
    .await?;

Can be combined with outer conditions:

.filter(|p| p.age.ge(18).or(
    Select::<Role>::new().filter(|r| r.uid.eq(p.id)).exists()
))

Loading Model Relations

After declaring #[has_many], #[belongs_to], #[has_one], or #[through] on a model, related objects can be loaded on demand:

let user = db.find_by_id::<User>(1).await?.unwrap();
let posts = db
    .find_related(&user, UserWhere::default().posts)
    .await?;

Use preload to load a relation for a batch of parent models without an N+1 query loop:

let mut users = db.select::<User>().collect::<Vec<_>>().await?;
db.preload(&mut users, UserWhere::default().posts).await?;

Use include to load relations as part of the query result. Relation-local ordering, pagination, and nested includes are supported:

let posts: Vec<Post> = db
    .select::<Post>()
    .include(|post| post.user)
    .collect()
    .await?;

let users: Vec<User> = db
    .select::<User>()
    .filter(|user| user.roles.any(|role| role.name.eq("admin")))
    .include(|user| user.roles.order_by(|role| role.name.asc()).range(..20))
    .include(|user| user.user_roles.include(|user_role| user_role.role))
    .collect()
    .await?;

Relation fields are excluded from column mapping. Empty collection relations return an empty Vec, while missing single-object relations return None.

Batch Query Orchestration

batch executes already-built queries in order and returns the same tuple shape. batch_many executes a query collection in order and returns a Vec.

let (users, orders): (Vec<User>, Vec<Order>) = db
    .batch((
        db.select::<User>().include(|u| u.roles),
        db.select::<Order>().include(|o| o.items),
    ))
    .await?;

let orders: Vec<Vec<Order>> = db.batch_many(order_queries).await?;

SQL is not merged, and no transaction is opened automatically. Use tx.batch(...) inside a transaction closure when a transaction is required.

Set Operations

UNION / UNION ALL

// UNION
let sql = Select::<User>::new()
    .filter(|u| u.age.gt(30))
    .union(Select::<User>::new().filter(|u| u.age.lt(18)))
    .to_sql();

// UNION ALL
let sql = Select::<User>::new()
    .filter(|u| u.age.gt(30))
    .union_all(Select::<User>::new().filter(|u| u.age.lt(18)))
    .to_sql();

INTERSECT / EXCEPT

let sql = Select::<User>::new()
    .filter(|u| u.age.gt(18))
    .intersect(Select::<User>::new().filter(|u| u.age.lt(65)))
    .to_sql();

let sql = Select::<User>::new()
    .filter(|u| u.age.gt(18))
    .except(Select::<User>::new().filter(|u| u.name.eq("admin")))
    .to_sql();

Each operand supports its own order_by and range; both operands are rendered parenthesized ((SELECT ...) UNION (SELECT ...)), so ORDER BY/LIMIT inside an operand stays within its parentheses and applies to that operand:

let sql = Select::<User>::new()
    .filter(|u| u.age.gt(30))
    .order_by(|u| u.name)
    .range(..10)
    .union(
        Select::<User>::new()
            .filter(|u| u.age.lt(18))
            .order_by_desc(|u| u.age)
            .range(..5),
    )
    .to_sql();

Set operations execute via select_union:

let union = Select::<User>::new()
    .filter(|u| u.age.gt(30))
    .union(Select::<User>::new().filter(|u| u.age.lt(18)));
let users: Vec<User> = db.select_union(union).collect().await?;

from / from3 / from4 related queries provide count(), rendering SELECT COUNT(*) FROM (<the atomic query>) with the identical predicate. It ignores range() / order_by() and fits the total_count of list pagination:

let total = db
    .select::<User>()
    .from::<Role>()
    .filter(|u, r| u.id.eq(r.uid))
    .filter(|_, r| r.name.eq("admin".to_string()))
    .count()
    .await?; // i64

Combining the same predicate with range() yields "page data + total count" whose predicates can never drift apart.

Complete Example

use ormer::{Database, DbType, Model};

#[derive(Debug, Model)]
#[table = "users"]
struct User {
    #[primary(auto)]
    id: i32,
    name: String,
    age: i32,
}

#[derive(Debug, Model)]
#[table = "roles"]
struct Role {
    #[primary(auto)]
    id: i32,
    user_id: i32,
    role_name: String,
}

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let db = Database::connect(DbType::Sqlite, "file:test.db").await?;
    db.create_table::<User>().execute().await?;
    db.create_table::<Role>().execute().await?;
    
    db.insert(&vec![
        User { id: 1, name: "Alice".to_string(), age: 25 },
        User { id: 2, name: "Bob".to_string(), age: 30 },
    ]).await?;
    
    db.insert(&vec![
        Role { id: 1, user_id: 1, role_name: "admin".to_string() },
        Role { id: 2, user_id: 2, role_name: "user".to_string() },
    ]).await?;
    
    // Aggregate
    let count: usize = db.select::<User>().count(|u| u.id).await?;
    let avg_age: Option<f64> = db.select::<User>().avg(|u| u.age).await?;
    
    // LEFT JOIN
    let user_roles: Vec<(User, Option<Role>)> = db
        .select::<User>()
        .left_join::<Role>(|u, r| u.id.eq(r.user_id))
        .collect()
        .await?;
    
    // Multi-table
    let admin_users: Vec<User> = db
        .select::<User>()
        .from::<Role>()
        .filter(|u, r| u.id.eq(r.user_id))
        .filter(|_, r| r.role_name.eq("admin".to_string()))
        .collect()
        .await?;
    
    // Subquery
    let users_with_roles: Vec<User> = db
        .select::<User>()
        .filter(|u| u.id.is_in(
            db.select::<Role>().map_to(|r| r.user_id)
        ))
        .collect()
        .await?;
    
    db.drop_table::<Role>().execute().await?;
    db.drop_table::<User>().execute().await?;
    Ok(())
}
Last Updated:
Contributors: fawdlstty