The note uses GORM as a case study in ORM abstractions.

Preload

db.Preload("Orders").Find(&users)

1. Query parent rows
2. Extract their primary keys
3. Query related rows with WHERE IN (...)
4. Assemble everything in memory

-- It takes 2 queires 
SELECT * FROM users;
SELECT * FROM orders WHERE user_id IN (1, 2, 3, 4);

-- This is the opposite of:
-- SELECT *
-- FROM users
-- JOIN orders ON orders.user_id = users.id;

Client can get the filled domain model object without reconstructing

user.Orders []Order

Explicit Operations

// -- name: ListUsers :many
// SELECT id, name
// FROM users;
users, _ := q.ListUsers(ctx)

ids := make([]int64, 0, len(users))
for _, u := range users {
    ids = append(ids, u.ID)
}

// -- name: ListOrdersByUserIDs :many
// SELECT id, user_id, amount
// FROM orders
// WHERE user_id = ANY($1);
orders, _ := q.ListOrdersByUserIDs(ctx, ids)

ordersByUser := make(map[int64][]Order)
for _, o := range orders {
    ordersByUser[o.UserID] = append(ordersByUser[o.UserID], o)
}

for i := range users {
    users[i].Orders = ordersByUser[users[i].ID]
}

Pagination

-- Pagnate user first
SELECT id, name
FROM users
ORDER BY id
LIMIT 10 OFFSET 0;

-- Then fetch pagnated rows later
SELECT *
FROM orders
WHERE user_id IN (A, B, C);

Optimized Replacement

type ListUsersWithOrdersRow struct {
    UserID   int64
    UserName string
    OrderID  sql.NullInt64
    Amount   sql.NullInt64
}

// -- name: ListUsersWithOrders :many
// SELECT
//     u.id   AS user_id,
//     u.name AS user_name,
//     o.id   AS order_id,
//     o.amount
// FROM users u
// LEFT JOIN orders o ON o.user_id = u.id
// ORDER BY u.id;
rows, _ := q.ListUsersWithOrders(ctx)

users := make([]User, 0)
userIndex := make(map[int64]int)

for _, r := range rows {
    idx, exists := userIndex[r.UserID]
    if !exists {
        users = append(users, User{
            ID:     r.UserID,
            Name:   r.UserName,
            Orders: []Order{},
        })
        idx = len(users) - 1
        userIndex[r.UserID] = idx
    }

    if r.OrderID.Valid {
        users[idx].Orders = append(users[idx].Orders, Order{
            ID:     r.OrderID.Int64,
            Amount: r.Amount.Int64,
        })
    }
}

Pagination

-- Pattern: “page first, join later”

-- name: ListUsersWithOrdersPaged :many
WITH paged_users AS (
    SELECT id, name
    FROM users
    ORDER BY id
    LIMIT $1 OFFSET $2
)
SELECT
    u.id   AS user_id,
    u.name AS user_name,
    o.id   AS order_id,
    o.amount
FROM paged_users u
LEFT JOIN orders o ON o.user_id = u.id
ORDER BY u.id;

Association

There are no hidden SQL tricks inside GORM worth reverse-engineering.