ORM-23: Add batched loading (load_for) to avoid N+1 queries #23

Open
opened 2026-10-13 12:49:40 +01:00 by NoctisWolfshadow · 0 comments
Owner

Depends on: #19, #20, #21

Context

Looping over users and calling user.posts().all() runs one query per user. This answers the "how does this work with joins?" TODO in query.rs: instead of hydrating joined rows into several models, run one batched follow-up query and group the results in a map. Related data is not stored on the structs.

Tasks

  • HasMany::load_for(&[P]) -> Result<HashMap<i64, Vec<C>>>, HasOne::load_for -> HashMap<i64, C>, BelongsTo::load_for(&[C]) -> HashMap<i64, P> and BelongsToMany::load_for(&[A]) -> HashMap<i64, Vec<B>>.
  • One IN (..) query per chunk of MAX_BOUND_IDS = 500 ids (SQLite limits bound parameters).
  • n-m: select the pivot key as an extra trailing column. Hydrate the child with #14's hydrate, then read the key with row.get(B::COLUMNS.len()).
  • A thread-local statement counter, incremented wherever the ORM prepares a statement and exposed to tests (#[doc(hidden)] or a test-util feature).
  • Parents without children are absent from the map. Document this.

Example

let users = User::query().limit(50).all()?;
let posts_by_user: HashMap<i64, Vec<Post>> = User::POSTS.load_for(&users)?; // 1 query

for user in &users {
    let posts = posts_by_user.get(&user.id).map(Vec::as_slice).unwrap_or(&[]);
    println!("{}: {} posts", user.name, posts.len());
}
-- HasMany: one query for all 50 users
SELECT id, user_id, title, published FROM posts WHERE user_id IN (?, ?, ?, ...);

-- BelongsToMany: the pivot key rides along as the last column
SELECT tags.id, tags.name, post_tags.post_id
FROM tags JOIN post_tags ON tags.id = post_tags.tag_id
WHERE post_tags.post_id IN (?, ?, ?, ...);
#[test]
fn fifty_parents_cost_one_statement() {
    setup_with_many_users(50);
    let users = User::all().unwrap();
    let before = Database::statement_count();
    User::POSTS.load_for(&users).unwrap();
    assert_eq!(Database::statement_count() - before, 1);
}

#[test]
fn twelve_hundred_parents_are_chunked() {
    setup_with_many_users(1200);
    let users = User::all().unwrap();
    let before = Database::statement_count();
    User::POSTS.load_for(&users).unwrap();
    assert_eq!(Database::statement_count() - before, 3); // 500 + 500 + 200
}

Acceptance criteria

  • Results equal what the per-parent queries return.
  • 50 parents cost 1 statement; 1,200 cost 3.
  • Empty input runs no query and returns an empty map.
  • Parents without children are absent from the map.
**Depends on:** #19, #20, #21 ## Context Looping over users and calling `user.posts().all()` runs one query per user. This answers the "how does this work with joins?" TODO in `query.rs`: instead of hydrating joined rows into several models, run **one batched follow-up query** and group the results in a map. Related data is not stored on the structs. ## Tasks - [ ] `HasMany::load_for(&[P]) -> Result<HashMap<i64, Vec<C>>>`, `HasOne::load_for -> HashMap<i64, C>`, `BelongsTo::load_for(&[C]) -> HashMap<i64, P>` and `BelongsToMany::load_for(&[A]) -> HashMap<i64, Vec<B>>`. - [ ] One `IN (..)` query per chunk of `MAX_BOUND_IDS = 500` ids (SQLite limits bound parameters). - [ ] n-m: select the pivot key as an extra trailing column. Hydrate the child with #14's `hydrate`, then read the key with `row.get(B::COLUMNS.len())`. - [ ] A thread-local statement counter, incremented wherever the ORM prepares a statement and exposed to tests (`#[doc(hidden)]` or a `test-util` feature). - [ ] Parents without children are **absent** from the map. Document this. ## Example ```rust let users = User::query().limit(50).all()?; let posts_by_user: HashMap<i64, Vec<Post>> = User::POSTS.load_for(&users)?; // 1 query for user in &users { let posts = posts_by_user.get(&user.id).map(Vec::as_slice).unwrap_or(&[]); println!("{}: {} posts", user.name, posts.len()); } ``` ```sql -- HasMany: one query for all 50 users SELECT id, user_id, title, published FROM posts WHERE user_id IN (?, ?, ?, ...); -- BelongsToMany: the pivot key rides along as the last column SELECT tags.id, tags.name, post_tags.post_id FROM tags JOIN post_tags ON tags.id = post_tags.tag_id WHERE post_tags.post_id IN (?, ?, ?, ...); ``` ```rust #[test] fn fifty_parents_cost_one_statement() { setup_with_many_users(50); let users = User::all().unwrap(); let before = Database::statement_count(); User::POSTS.load_for(&users).unwrap(); assert_eq!(Database::statement_count() - before, 1); } #[test] fn twelve_hundred_parents_are_chunked() { setup_with_many_users(1200); let users = User::all().unwrap(); let before = Database::statement_count(); User::POSTS.load_for(&users).unwrap(); assert_eq!(Database::statement_count() - before, 3); // 500 + 500 + 200 } ``` ## Acceptance criteria - [ ] Results equal what the per-parent queries return. - [ ] 50 parents cost 1 statement; 1,200 cost 3. - [ ] Empty input runs no query and returns an empty map. - [ ] Parents without children are absent from the map.
Sign in to join this conversation.
No description provided.