← Cookbook

Chapter 3 — ORM

13 live recipes against the Author / Post fixture from Chapter 2. Run with DATABASE_URL=... cargo test --test cookbook_chapter03_orm -- --test-threads=1.

§3.31 Post::objects().filter("published", Op::Eq, true).fetch_on(&pool) → filter_eq_fetch_returns_matching_rows
§3.34 Op::Gt / Op::Lt / Op::ILike / Op::In / Op::Between / Op::IsNull — six tests covering the full Op surface.
§3.35 .order_by(&[("col", desc)]) (true = DESC, false = ASC) → order_by_view_count_desc
§3.36 .limit(N).offset(M) for pagination → limit_offset_paginates
§3.37 .aggregate().annotate("alias", AggregateExpr::Count|Sum|Avg|...)
- fetch_aggregate(&q, &pool) → Vec<HashMap<String, SqlValue>> → aggregate_count_and_sum
§3.42 model.save(&pool) does INSERT (PK Unset) or UPDATE (PK Set) → save_inserts_then_updates_in_place
§3.46 raw sqlx::query_scalar / query_as for SQL the QuerySet doesn't cover → raw_sql_escape_via_sqlx
§3.47 manual pool.begin() ... tx.rollback() — atomic rollback on UNIQUE violation → manual_transaction_rolls_back_on_error
§3.48 PG JSONB @> containment operator on the metadata column → json_operator_on_jsonb_column

Framework bug fixed during this slice: SUM(BIGINT) returns PostgreSQL NUMERIC and AVG(BIGINT) returns NUMERIC — the aggregate row decoder only tries i64/i32/f64/bool/String, so the result silently came back as SqlValue::Null. Added Dialect::cast_aggregate_to_int / cast_aggregate_to_float (PG emits ::bigint / ::double precision; MySQL emits CAST(.. AS SIGNED) / CAST(.. AS DOUBLE)). Aggregate writer wraps SUM/AVG via the new methods.

Sub-sections 3.32 (get/get_on/fetch_on), 3.33 (OR-nested), 3.38 (annotate), 3.39 (prefetch FK), 3.40 (prefetch_soft), 3.41 (prefetch_generic), 3.43 (bulk_insert), 3.44 (bulk UPDATE), 3.45 (WhereExpr::Not), 3.49 (_pool executor variants) queued for Slice 3b.

3.50 QuerySet inspection + introspection (v0.43)

Recent Eloquent-shape builder helpers covering the common "inspect or branch a queryset" patterns:

// .when(cond, |qs| ...) / .unless(cond, |qs| ...) / .tap(|&qs| ...)
let qs = Post::objects()
    .when(only_active, |q| q.filter("active", true))
    .unless(is_admin, |q| q.filter("public", true))
    .tap(|q| tracing::debug!(?q, "before fetch"));

// .reorder(&[(col, asc)]) — replace ORDER BY instead of appending
let qs = Post::objects()
    .with_default_order()
    .reorder(&[("views", true)]);

// QuerySet: Clone — divergent branches from a shared base
let base    = Post::objects().filter("status__ne", "archived");
let drafts  = base.clone().filter("status", "draft");
let pub_now = base.filter("status", "published");

// .pluck::<U>(col, &pool) — single-column projection on a filtered qs
let titles: Vec<String> = Post::objects()
    .filter("published", true)
    .pluck::<String>("title", &pool).await?;

// .is_empty(&pool) — inverse of exists_pool
if Post::objects().filter("category_id", 7).is_empty(&pool).await? {
    return Ok(Response::empty());
}

// .to_sql(&pool) / .to_compiled(&pool) — render SQL without executing
let sql = Post::objects().filter("published", true).to_sql(&pool)?;
// -> "SELECT … FROM \"post\" WHERE \"published\" = $1"

Verified by: tests/queryset_when_unless_tap.rs, tests/queryset_reorder_sqlite_live.rs, tests/queryset_clone_sqlite_live.rs, tests/queryset_pluck_sqlite_live.rs, tests/queryset_is_empty_sqlite_live.rs, tests/queryset_to_sql_sqlite_live.rs.

3.51 Eloquent shortcut batch — find_or_new / find_many_or_fail / insert_or_ignore / aggregates / locking (v0.43)

// Find-or-default in one call. Returns (row, exists: bool) so
// edit-or-create form handlers know which path was taken.
let (post, exists) = Post::find_or_new(form.id, &pool, || Post {
    id: Auto::default(),
    title: form.title.clone(),
}).await?;

// Find by multiple PKs, error if any missing. Dedups duplicate
// PKs before counting.
let posts: Vec<Post> = Post::find_many_or_fail([1, 2, 3], &pool).await?;

// INSERT or silently skip on unique-constraint violation.
// PG/SQLite: `ON CONFLICT DO NOTHING`. MySQL: `INSERT IGNORE`.
let inserted: bool = post.insert_or_ignore(&pool).await?;

// Aggregate scalars on a filtered queryset (table-wide versions
// are `Model::sum` / `Model::avg` / etc.):
let total: Option<i64> = Post::objects()
    .filter("published", true)
    .sum::<i64>("views", &pool).await?;
let avg: Option<f64>  = Post::objects().avg::<f64>("views", &pool).await?;

// Row locking — Eloquent muscle-memory alias:
let row = Post::objects()
    .filter("id", 42)
    .lock_for_update()                 // == select_for_update()
    .first(&pool).await?;

Verified by: tests/model_find_or_new_sqlite_live.rs, tests/model_find_many_or_fail_sqlite_live.rs, tests/model_insert_or_ignore_sqlite_live.rs, tests/queryset_aggregates_sqlite_live.rs, tests/queryset_lock_for_update_emission.rs.

3.52 Pagination + find-or-insert + single-value reach (v0.43)

// Model::paginate(page, per_page, &pool) -> (rows, total) —
// Eloquent paginate over the whole table.
let (posts, total) = Post::paginate(2, 10, &pool).await?;

// QuerySet::paginate — filtered counterpart. `total` reflects
// matching rows.
let (drafts, total_drafts) = Post::objects()
    .filter("status", "draft")
    .paginate(1, 10, &pool).await?;

// Model::find_or_insert(pk, &pool, fallback) — Eloquent
// findOrCreate. Persists the fallback if not found. Returns
// (row, exists: bool).
let (post, found) = Post::find_or_insert(
    pk,
    &pool,
    || Post { id: Auto::default(), title: "new".into() },
).await?;

// QuerySet::value<U>(col, &pool) — Eloquent Builder::value().
// Single column from the first row of a filtered queryset.
let email: Option<String> = User::objects()
    .filter("id", 1_i64)
    .value::<String>("email", &pool).await?;

Verified by: tests/model_paginate_sqlite_live.rs, tests/queryset_paginate_sqlite_live.rs, tests/model_find_or_insert_sqlite_live.rs, tests/queryset_value_sqlite_live.rs.

← Cookbook