Home › API › SIMPLE_SQL_EAGER_LOADER

SIMPLE_SQL_EAGER_LOADER

Eliminates N+1 query problems by batch-loading related data. Instead of one query per parent row, loads all related data in a single additional query per relationship.

View Source

The N+1 Problem

Without eager loading, fetching 100 documents with their folders requires 101 queries: 1 for documents + 100 for each folder. With eager loading: 2 queries total.

Setup

from_table (a_table: READABLE_STRING_8): like Current

Set the main table to query from.

columns (a_columns: READABLE_STRING_8): like Current

Columns to select from main table. Default is *.

where (a_condition: READABLE_STRING_8): like Current

Filter main table rows.

order_by (a_ordering: READABLE_STRING_8): like Current

Order main table results.

limit (a_count: INTEGER): like Current

Limit main table rows.

Include Related Data

include (a_relation, a_table, a_fk, a_pk: READABLE_STRING_8): like Current

Include a direct FK relationship. a_fk on main table points to a_pk on related table.

include_many_to_many (a_relation, a_table, a_join_table, a_main_fk, a_related_fk: READABLE_STRING_8): like Current

Include a many-to-many relationship through a join table.

Execution

execute: SIMPLE_SQL_EAGER_RESULT

Execute the query and return results with loaded relationships.

SIMPLE_SQL_EAGER_RESULT

Container for eager-loaded results. Provides access to main rows and their related data.

View Source

Access

main_rows: SIMPLE_SQL_RESULT

The main table query results.

related (a_relation: READABLE_STRING_8): HASH_TABLE [ARRAYED_LIST [SIMPLE_SQL_ROW], INTEGER_64]

Get all loaded related data for a relation, keyed by main row ID.

related_for (a_relation: READABLE_STRING_8; a_id: INTEGER_64): ARRAYED_LIST [SIMPLE_SQL_ROW]

Get related rows for a specific main row ID.

first_related_for (a_relation: READABLE_STRING_8; a_id: INTEGER_64): detachable SIMPLE_SQL_ROW

Get first related row (for 1:1 or N:1 relationships).

related_count (a_relation: READABLE_STRING_8; a_id: INTEGER_64): INTEGER

Count of related rows for a main row.

Examples

Direct Foreign Key (Documents → Folders)

local
    result: SIMPLE_SQL_EAGER_RESULT
    doc_id: INTEGER_64
do
    -- Load documents with their folders (2 queries total)
    result := db.eager_loader
        .from_table ("documents")
        .include ("folder", "folders", "folder_id", "id")
        .execute

    -- Access the data
    across result.main_rows as doc loop
        doc_id := doc.integer_64_value ("id")
        print ("Document: " + doc.string_value ("name"))

        if attached result.first_related_for ("folder", doc_id) as folder then
            print (" in folder: " + folder.string_value ("name"))
        end
        print ("%N")
    end
end

Many-to-Many (Documents → Tags)

local
    result: SIMPLE_SQL_EAGER_RESULT
    doc_id: INTEGER_64
do
    -- Load documents with tags through document_tags join table
    result := db.eager_loader
        .from_table ("documents")
        .include_many_to_many ("tags", "tags", "document_tags", "document_id", "tag_id")
        .where ("documents.deleted_at IS NULL")
        .execute

    across result.main_rows as doc loop
        doc_id := doc.integer_64_value ("id")
        print ("Document: " + doc.string_value ("name") + " [")

        across result.related_for ("tags", doc_id) as tag loop
            print (tag.string_value ("name") + " ")
        end
        print ("]%N")
    end
end

Multiple Relationships

result := db.eager_loader
    .from_table ("documents")
    .columns ("id, name, folder_id, created_by")
    .include ("folder", "folders", "folder_id", "id")
    .include ("author", "users", "created_by", "id")
    .include_many_to_many ("tags", "tags", "document_tags", "document_id", "tag_id")
    .order_by ("created_at DESC")
    .limit (50)
    .execute

-- 4 queries total: documents, folders, users, tags (via join table)

Performance Impact

Query count: 1 + N relationships (not 1 + N rows). Loading 1000 documents with 3 relationships = 4 queries, not 3001.

Without Eager Loading (N+1 Problem)

-- BAD: N+1 queries
across db.select_from ("documents").execute as doc loop
    -- This executes a query for EACH document!
    folder := db.select_from ("folders")
        .where ("id = ?", doc.integer_value ("folder_id"))
        .execute.first
end

With Eager Loading (Efficient)

-- GOOD: 2 queries total
result := db.eager_loader
    .from_table ("documents")
    .include ("folder", "folders", "folder_id", "id")
    .execute

across result.main_rows as doc loop
    folder := result.first_related_for ("folder", doc.integer_64_value ("id"))
end