Advanced Querying

calendar_today December 5, 2025 schedule 19 min read

Learning Objectives: By the end of this lesson, you will be able to:

Build complex queries with subqueries and nested conditions

Use raw SQL when GORM’s query builder isn’t sufficient

Implement transactions for data integrity

Create reusable query scopes for clean code

Utilize query hooks and callbacks

Handle locking for concurrent access

Optimize queries for performance

1. Welcome and Introduction (5 minutes)

Icebreaker Activity

Have you ever faced a situation where a simple query just wouldn’t cut it? Perhaps you needed to find all users who have made more than 5 orders in the last month, ordered products in a specific category, and haven’t logged in for 30 days - all in a single query? Today, we’ll master advanced querying techniques that will help you tackle even the most complex data retrieval scenarios with elegance and efficiency.

Lesson Overview

Duration: 150 minutes
Prerequisites: Lessons 1-4 completed, understanding of associations
Materials Needed: Working GORM setup with populated data

2. Learning Objectives and Session Outline (5 minutes)

What We’ll Cover Today:

Complex Conditions - AND, OR, NOT with nested logic
Subqueries - Queries within queries
Raw SQL - When and how to use raw SQL safely
Transactions - ACID compliance and data integrity
Scopes - Reusable query fragments
Locking - Handling concurrent access
Query optimization - Performance best practices

Success Criteria:

By the end of this session, you should be able to write sophisticated database queries, handle complex business logic with transactions, and optimize query performance.

3. Foundation Knowledge (15-20 minutes)

Core Concepts

Query Building Philosophy

GORM follows a chainable method pattern where each method returns a *gorm.DB instance. This allows building queries incrementally:

query := db.Model(&User{})
if name != "" {
    query = query.Where("name LIKE ?", "%"+name+"%")
}
if age > 0 {
    query = query.Where("age >= ?", age)
}
query.Find(&users)

Real-World Analogy: Think of building queries like assembling a custom burger. You start with the base (Model), add conditions like toppings (Where), specify how you want it cooked (Order), and finally get your meal (Find). Each choice builds upon the previous one.

Why This Matters: Understanding the query builder allows you to create dynamic, flexible queries that adapt to runtime conditions without string concatenation or complex conditional logic.

Transaction Guarantees (ACID)

Atomicity: All operations succeed or all fail
Consistency: Database remains in valid state
Isolation: Concurrent transactions don’t interfere
Durability: Committed changes persist

Key Terminology

Subquery: A query nested inside another query
Scope: Reusable query fragment
Pessimistic Lock: Lock rows to prevent concurrent modification
Optimistic Lock: Use version field to detect conflicts
Savepoint: Checkpoint within transaction for partial rollback

4. Detailed Explanation and Examples (25-30 minutes)

Understanding GORM’s Query Chain

Every GORM method that modifies the query state creates a new *gorm.DB session clone:

// Each method returns a new session
query := db.Where("status = ?", "active")  // New session
query = query.Order("created_at desc")      // Another new session
query = query.Limit(10)                     // Yet another session

This is important because:

Original db is not modified
You can branch queries safely
Sessions inherit configuration from parent

Common Misconceptions

Misconception: “Raw SQL is always faster” Reality: GORM generates optimized SQL. Raw SQL should only be used for unsupported features.
Misconception: “Transactions are only for writes” Reality: Transactions ensure consistent reads and prevent dirty reads.
Misconception: “Scopes are just convenience” Reality: Scopes enable DRY principles and ensure consistent query patterns.

5. Live Coding Demonstration (20-25 minutes)

Example 1: Complex Conditions and Subqueries

package main

import (
    "fmt"
    "time"

    "gorm.io/driver/sqlite"
    "gorm.io/gorm"
)

type User struct {
    ID        uint      `gorm:"primaryKey"`
    Name      string    `gorm:"size:100"`
    Email     string    `gorm:"size:255;uniqueIndex"`
    Age       int
    Role      string    `gorm:"size:20"`
    Status    string    `gorm:"size:20;default:'active'"`
    Score     int       `gorm:"default:0"`
    CreatedAt time.Time
}

type Order struct {
    ID        uint      `gorm:"primaryKey"`
    UserID    uint      `gorm:"index"`
    Amount    float64   `gorm:"type:decimal(10,2)"`
    Status    string    `gorm:"size:20"`
    CreatedAt time.Time
}

func main() {
    db, _ := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
    db.AutoMigrate(&User{}, &Order{})

    // ===== COMPLEX CONDITIONS =====

    var users []User

    // 1. Multiple conditions with AND (default)
    db.Where("status = ?", "active").
        Where("age >= ?", 18).
        Where("role IN ?", []string{"admin", "moderator"}).
        Find(&users)

    // 2. OR conditions
    db.Where("role = ?", "admin").
        Or("score > ?", 1000).
        Find(&users)

    // 3. Nested conditions with grouping
    // SQL: WHERE status = 'active' AND (role = 'admin' OR score > 500)
    db.Where("status = ?", "active").
        Where(db.Where("role = ?", "admin").Or("score > ?", 500)).
        Find(&users)

    // 4. Complex nested logic
    // SQL: WHERE (status = 'active' AND age >= 18) OR (role = 'admin' AND score > 1000)
    db.Where(
        db.Where("status = ? AND age >= ?", "active", 18),
    ).Or(
        db.Where("role = ? AND score > ?", "admin", 1000),
    ).Find(&users)

    // 5. NOT conditions
    db.Not("status = ?", "banned").Find(&users)
    db.Not(map[string]interface{}{"role": []string{"guest", "banned"}}).Find(&users)

    // 6. IN with slice
    db.Where("id IN ?", []uint{1, 2, 3, 4, 5}).Find(&users)

    // 7. BETWEEN
    db.Where("age BETWEEN ? AND ?", 18, 35).Find(&users)

    // 8. LIKE with wildcards
    db.Where("name LIKE ?", "%john%").Find(&users)
    db.Where("email LIKE ?", "%@gmail.com").Find(&users)

    // 9. NULL checks
    db.Where("deleted_at IS NULL").Find(&users)
    db.Where("manager_id IS NOT NULL").Find(&users)

    // ===== SUBQUERIES =====

    // 10. Subquery in WHERE
    // Find users whose score is above average
    avgSubquery := db.Model(&User{}).Select("AVG(score)")
    db.Where("score > (?)", avgSubquery).Find(&users)

    // 11. Subquery with conditions
    // Find users who have placed orders over $100
    orderSubquery := db.Model(&Order{}).
        Select("user_id").
        Where("amount > ?", 100).
        Group("user_id")
    db.Where("id IN (?)", orderSubquery).Find(&users)

    // 12. Correlated subquery (using raw SQL)
    db.Where("score > (SELECT AVG(score) FROM users WHERE role = users.role)").Find(&users)

    // 13. EXISTS subquery
    db.Where("EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id AND orders.amount > ?)", 100).Find(&users)

    // 14. Subquery in FROM (derived table)
    type Result struct {
        Role       string
        AvgScore   float64
        UserCount  int64
    }
    var results []Result
    subQuery := db.Model(&User{}).
        Select("role, AVG(score) as avg_score, COUNT(*) as user_count").
        Group("role")
    db.Table("(?) as stats", subQuery).
        Where("avg_score > ?", 500).
        Scan(&results)

    // 15. Multiple subqueries
    activeUsers := db.Model(&User{}).Select("id").Where("status = ?", "active")
    highSpenders := db.Model(&Order{}).Select("user_id").Where("amount > ?", 500).Group("user_id")
    
    db.Where("id IN (?) AND id IN (?)", activeUsers, highSpenders).Find(&users)

    fmt.Printf("Found %d users\n", len(users))
}

Example 2: Raw SQL and Named Arguments

package main

import (
    "database/sql"
    "fmt"

    "gorm.io/driver/sqlite"
    "gorm.io/gorm"
)

type User struct {
    ID     uint   `gorm:"primaryKey"`
    Name   string `gorm:"size:100"`
    Email  string `gorm:"size:255"`
    Status string `gorm:"size:20"`
    Score  int
}

type UserStats struct {
    TotalUsers   int64
    ActiveUsers  int64
    AvgScore     float64
    MaxScore     int
    MinScore     int
}

func main() {
    db, _ := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
    db.AutoMigrate(&User{})

    // ===== RAW SQL QUERIES =====

    var users []User

    // 1. Basic Raw query
    db.Raw("SELECT * FROM users WHERE status = ?", "active").Scan(&users)

    // 2. Raw with named arguments
    db.Raw("SELECT * FROM users WHERE status = @status AND score > @score",
        sql.Named("status", "active"),
        sql.Named("score", 100),
    ).Scan(&users)

    // 3. Raw with map arguments
    db.Raw("SELECT * FROM users WHERE status = @status",
        map[string]interface{}{"status": "active"},
    ).Scan(&users)

    // 4. Complex raw query with joins
    type UserOrder struct {
        UserID     uint
        UserName   string
        TotalOrders int64
        TotalSpent float64
    }
    var userOrders []UserOrder
    db.Raw(`
        SELECT 
            u.id as user_id,
            u.name as user_name,
            COUNT(o.id) as total_orders,
            COALESCE(SUM(o.amount), 0) as total_spent
        FROM users u
        LEFT JOIN orders o ON u.id = o.user_id
        WHERE u.status = ?
        GROUP BY u.id, u.name
        HAVING COUNT(o.id) > ?
        ORDER BY total_spent DESC
        LIMIT ?
    `, "active", 0, 10).Scan(&userOrders)

    // 5. Raw query for aggregates
    var stats UserStats
    db.Raw(`
        SELECT 
            COUNT(*) as total_users,
            SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_users,
            AVG(score) as avg_score,
            MAX(score) as max_score,
            MIN(score) as min_score
        FROM users
    `).Scan(&stats)
    fmt.Printf("Stats: %+v\n", stats)

    // 6. Raw Exec for modifications
    result := db.Exec("UPDATE users SET score = score + ? WHERE status = ?", 10, "active")
    fmt.Printf("Updated %d rows\n", result.RowsAffected)

    // 7. Row-level processing
    rows, _ := db.Raw("SELECT id, name, score FROM users WHERE score > ?", 100).Rows()
    defer rows.Close()
    for rows.Next() {
        var id uint
        var name string
        var score int
        rows.Scan(&id, &name, &score)
        fmt.Printf("User %d: %s (%d points)\n", id, name, score)
    }

    // 8. Using Raw in conditions
    db.Where("score > (?)", db.Raw("SELECT AVG(score) FROM users")).Find(&users)

    // 9. Raw with table expressions (CTE - if database supports)
    db.Raw(`
        WITH high_scorers AS (
            SELECT * FROM users WHERE score > 500
        )
        SELECT * FROM high_scorers WHERE status = ?
    `, "active").Scan(&users)

    // ===== COMBINING RAW WITH GORM =====

    // 10. Use raw in specific parts
    db.Model(&User{}).
        Select("id, name, score, RANK() OVER (ORDER BY score DESC) as rank").
        Where("status = ?", "active").
        Scan(&users)

    // 11. Raw expressions in updates
    db.Model(&User{}).Where("id = ?", 1).
        Update("score", gorm.Expr("score * ? + ?", 2, 100))

    // 12. Raw in order by
    db.Order("CASE WHEN status = 'active' THEN 0 ELSE 1 END, score DESC").Find(&users)
}

Example 3: Transactions

package main

import (
    "errors"
    "fmt"
    "time"

    "gorm.io/driver/sqlite"
    "gorm.io/gorm"
)

type Account struct {
    ID           uint  `gorm:"primaryKey"`
    UserID       uint  `gorm:"uniqueIndex"`
    BalanceCents int64 `gorm:"not null;default:0"` // integer minor units
    Version      int   `gorm:"default:1"`
}

type Transaction struct {
    ID            uint  `gorm:"primaryKey"`
    FromAccountID uint  `gorm:"index"`
    ToAccountID   uint  `gorm:"index"`
    AmountCents   int64 `gorm:"not null"`
    Status        string `gorm:"size:20"`
    CreatedAt     time.Time
}

// TransferMoney demonstrates transaction handling
// Atomic conditional UPDATE works on SQLite and PostgreSQL; SQLite does not
// support PostgreSQL-style SELECT FOR UPDATE row locks.
func TransferMoney(db *gorm.DB, fromID, toID uint, amountCents int64) error {
    if fromID == toID || amountCents <= 0 { return errors.New("invalid transfer") }
    return db.Transaction(func(tx *gorm.DB) error {
        return transferInTx(tx, fromID, toID, amountCents)
    })
}

func transferInTx(tx *gorm.DB, fromID, toID uint, amountCents int64) error {
    debit := tx.Model(&Account{}).Where("id = ? AND balance_cents >= ?", fromID, amountCents).
        Updates(map[string]interface{}{"balance_cents": gorm.Expr("balance_cents - ?", amountCents),
            "version": gorm.Expr("version + 1")})
    if debit.Error != nil { return debit.Error }
    if debit.RowsAffected != 1 { return errors.New("source missing or insufficient balance") }

    credit := tx.Model(&Account{}).Where("id = ?", toID).
        Updates(map[string]interface{}{"balance_cents": gorm.Expr("balance_cents + ?", amountCents),
            "version": gorm.Expr("version + 1")})
    if credit.Error != nil { return credit.Error }
    if credit.RowsAffected != 1 { return gorm.ErrRecordNotFound } // debit rolls back

    return tx.Create(&Transaction{FromAccountID: fromID, ToAccountID: toID,
        AmountCents: amountCents, Status: "completed"}).Error
}

// Method 2: Manual transaction control
func TransferMoneyManual(db *gorm.DB, fromID, toID uint, amountCents int64) error {
    if fromID == toID || amountCents <= 0 { return errors.New("invalid transfer") }
    tx := db.Begin()
    if tx.Error != nil { return tx.Error }
    defer tx.Rollback() // also rolls back on early return/panic; no-op after commit
    if err := transferInTx(tx, fromID, toID, amountCents); err != nil { return err }
    return tx.Commit().Error
}

// Nested transactions with savepoints
func ComplexOperation(db *gorm.DB) error {
    return db.Transaction(func(tx *gorm.DB) error {
        // First operation
        if err := tx.Create(&Account{UserID: 100, BalanceCents: 100000}).Error; err != nil {
            return err
        }
        
        // Nested transaction (savepoint)
        err := tx.Transaction(func(tx2 *gorm.DB) error {
            if err := tx2.Create(&Account{UserID: 101, BalanceCents: 50000}).Error; err != nil {
                return err // Rolls back to savepoint, not entire transaction
            }
            
            // Simulate error in nested transaction
            simulateNestedFailure := true
            if simulateNestedFailure {
                return errors.New("nested operation failed")
            }
            
            return nil
        })
        
        // Can continue even if nested transaction failed
        if err != nil {
            fmt.Printf("Nested transaction failed: %v, continuing...\n", err)
        }
        
        // Third operation
        if err := tx.Create(&Account{UserID: 102, BalanceCents: 75000}).Error; err != nil {
            return err
        }
        
        return nil
    })
}

// Optimistic locking example
func UpdateWithOptimisticLock(db *gorm.DB, accountID uint, newBalanceCents int64) error {
    var account Account
    if err := db.First(&account, accountID).Error; err != nil {
        return err
    }
    
    // Update only if version matches
    result := db.Model(&account).
        Where("version = ?", account.Version).
        Updates(map[string]interface{}{
            "balance_cents": newBalanceCents,
            "version": account.Version + 1,
        })
    
    if result.Error != nil { return result.Error }
    if result.RowsAffected == 0 {
        return errors.New("optimistic lock conflict - record was modified")
    }
    
    return nil
}

func main() {
    db, err := gorm.Open(sqlite.Open(":memory:"), &gorm.Config{})
    if err != nil { panic(err) }
    sqlDB, err := db.DB()
    if err != nil { panic(err) }
    sqlDB.SetMaxOpenConns(1) // :memory: schema belongs to one connection
    defer sqlDB.Close()
    if err := db.AutoMigrate(&Account{}, &Transaction{}); err != nil { panic(err) }
    
    // Create test accounts
    source := Account{UserID: 1, BalanceCents: 100000}
    target := Account{UserID: 2, BalanceCents: 50000}
    if err := db.Create(&source).Error; err != nil { panic(err) }
    if err := db.Create(&target).Error; err != nil { panic(err) }
    
    // Perform transfer
    err = TransferMoney(db, source.ID, target.ID, 10000)
    if err != nil {
        fmt.Printf("Transfer failed: %v\n", err)
    } else {
        fmt.Println("Transfer successful!")
    }
}

Example 4: Scopes and Reusable Queries

package main

import (
    "time"

    "gorm.io/driver/sqlite"
    "gorm.io/gorm"
)

type User struct {
    ID        uint      `gorm:"primaryKey"`
    Name      string    `gorm:"size:100"`
    Email     string    `gorm:"size:255"`
    Role      string    `gorm:"size:20"`
    Status    string    `gorm:"size:20"`
    Score     int
    Tier      string    `gorm:"size:20"`
    CreatedAt time.Time
    UpdatedAt time.Time
}

// ===== SCOPE DEFINITIONS =====

// Active returns users with active status
func Active(db *gorm.DB) *gorm.DB {
    return db.Where("status = ?", "active")
}

// ByRole filters by role
func ByRole(role string) func(db *gorm.DB) *gorm.DB {
    return func(db *gorm.DB) *gorm.DB {
        return db.Where("role = ?", role)
    }
}

// HighScorer filters users with score above threshold
func HighScorer(minScore int) func(db *gorm.DB) *gorm.DB {
    return func(db *gorm.DB) *gorm.DB {
        return db.Where("score >= ?", minScore)
    }
}

// RecentlyActive filters users active in last N days
func RecentlyActive(days int) func(db *gorm.DB) *gorm.DB {
    return func(db *gorm.DB) *gorm.DB {
        cutoff := time.Now().AddDate(0, 0, -days)
        return db.Where("updated_at >= ?", cutoff)
    }
}

// PremiumTier filters premium users
func PremiumTier(db *gorm.DB) *gorm.DB {
    return db.Where("tier IN ?", []string{"gold", "platinum"})
}

// Paginate adds pagination
func Paginate(page, pageSize int) func(db *gorm.DB) *gorm.DB {
    return func(db *gorm.DB) *gorm.DB {
        if page <= 0 {
            page = 1
        }
        if pageSize <= 0 {
            pageSize = 10
        }
        offset := (page - 1) * pageSize
        return db.Offset(offset).Limit(pageSize)
    }
}

// OrderByScore orders by score descending
func OrderByScore(db *gorm.DB) *gorm.DB {
    return db.Order("score DESC")
}

// WithFullInfo preloads common associations
func WithFullInfo(db *gorm.DB) *gorm.DB {
    return db.Preload("Profile").Preload("Orders")
}

// ===== DYNAMIC SCOPE BUILDER =====

type UserFilter struct {
    Status    string
    Role      string
    MinScore  int
    MaxScore  int
    Tier      string
    SearchTerm string
    Page      int
    PageSize  int
    SortBy    string
    SortOrder string
}

func ApplyFilters(filter UserFilter) func(db *gorm.DB) *gorm.DB {
    return func(db *gorm.DB) *gorm.DB {
        if filter.Status != "" {
            db = db.Where("status = ?", filter.Status)
        }
        if filter.Role != "" {
            db = db.Where("role = ?", filter.Role)
        }
        if filter.MinScore > 0 {
            db = db.Where("score >= ?", filter.MinScore)
        }
        if filter.MaxScore > 0 {
            db = db.Where("score <= ?", filter.MaxScore)
        }
        if filter.Tier != "" {
            db = db.Where("tier = ?", filter.Tier)
        }
        if filter.SearchTerm != "" {
            search := "%" + filter.SearchTerm + "%"
            db = db.Where("name LIKE ? OR email LIKE ?", search, search)
        }
        
        // Sorting
        if filter.SortBy != "" {
            order := filter.SortBy
            if filter.SortOrder == "desc" {
                order += " DESC"
            }
            db = db.Order(order)
        }
        
        // Pagination
        if filter.Page > 0 && filter.PageSize > 0 {
            offset := (filter.Page - 1) * filter.PageSize
            db = db.Offset(offset).Limit(filter.PageSize)
        }
        
        return db
    }
}

func main() {
    db, _ := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
    db.AutoMigrate(&User{})

    var users []User

    // ===== USING SCOPES =====

    // 1. Single scope
    db.Scopes(Active).Find(&users)

    // 2. Multiple scopes combined
    db.Scopes(Active, ByRole("admin"), OrderByScore).Find(&users)

    // 3. Scopes with parameters
    db.Scopes(
        Active,
        HighScorer(500),
        RecentlyActive(30),
        Paginate(1, 10),
    ).Find(&users)

    // 4. Conditional scope application
    var wantPremium = true
    query := db.Scopes(Active)
    if wantPremium {
        query = query.Scopes(PremiumTier)
    }
    query.Find(&users)

    // 5. Using filter builder
    filter := UserFilter{
        Status:     "active",
        MinScore:   100,
        SearchTerm: "john",
        Page:       1,
        PageSize:   20,
        SortBy:     "score",
        SortOrder:  "desc",
    }
    db.Scopes(ApplyFilters(filter)).Find(&users)

    // 6. Scope with count
    var count int64
    db.Model(&User{}).Scopes(Active, PremiumTier).Count(&count)
    fmt.Printf("Active premium users: %d\n", count)

    // 7. Reusing scopes in different contexts
    // For reporting
    var stats struct {
        TotalScore int64
        AvgScore   float64
        Count      int64
    }
    db.Model(&User{}).
        Scopes(Active, HighScorer(500)).
        Select("SUM(score) as total_score, AVG(score) as avg_score, COUNT(*) as count").
        Scan(&stats)

    fmt.Printf("Found %d users\n", len(users))
}

Example 5: Locking and Concurrency

package main

import (
    "fmt"
    "sync"
    "time"

    "gorm.io/driver/sqlite"
    "gorm.io/gorm"
    "gorm.io/gorm/clause"
)

type Inventory struct {
    ID       uint   `gorm:"primaryKey"`
    SKU      string `gorm:"size:50;uniqueIndex"`
    Quantity int    `gorm:"default:0"`
    Version  int    `gorm:"default:1"` // Optimistic lock version
}

// Pessimistic locking - SELECT FOR UPDATE
// Run this on PostgreSQL/MySQL, NOT the SQLite demo below. Keep row locking
// inside a transaction and validate the requested quantity.
func DecrementStockPessimistic(db *gorm.DB, sku string, quantity int) error {
    if quantity <= 0 { return fmt.Errorf("quantity must be positive") }
    return db.Transaction(func(tx *gorm.DB) error {
        var inventory Inventory
        
        // Lock the row for update
        if err := tx.Clauses(clause.Locking{Strength: "UPDATE"}).
            Where("sku = ?", sku).
            First(&inventory).Error; err != nil {
            return err
        }
        
        if inventory.Quantity < quantity {
            return fmt.Errorf("insufficient stock: have %d, need %d", 
                inventory.Quantity, quantity)
        }
        
        return tx.Model(&inventory).
            Update("quantity", gorm.Expr("quantity - ?", quantity)).Error
    })
}

// Pessimistic locking variants
// PostgreSQL/MySQL only; SQLite does not support FOR UPDATE / SKIP LOCKED.
func LockingExamples(db *gorm.DB) {
    var inventory Inventory
    
    // FOR UPDATE - exclusive lock
    db.Clauses(clause.Locking{Strength: "UPDATE"}).
        First(&inventory, 1)
    
    // FOR SHARE - shared lock (allows concurrent reads)
    db.Clauses(clause.Locking{Strength: "SHARE"}).
        First(&inventory, 1)
    
    // FOR UPDATE NOWAIT - fail immediately if locked
    db.Clauses(clause.Locking{
        Strength: "UPDATE",
        Options:  "NOWAIT",
    }).First(&inventory, 1)
    
    // FOR UPDATE SKIP LOCKED - skip locked rows (useful for job queues)
    var inventories []Inventory
    db.Clauses(clause.Locking{
        Strength: "UPDATE",
        Options:  "SKIP LOCKED",
    }).Limit(10).Find(&inventories)
}

// SQLite-friendly conditional update: does not rely on SELECT FOR UPDATE.
func DecrementStockAtomic(db *gorm.DB, sku string, quantity int) error {
    if quantity <= 0 { return fmt.Errorf("quantity must be positive") }
    result := db.Model(&Inventory{}).Where("sku = ? AND quantity >= ?", sku, quantity).
        Updates(map[string]interface{}{"quantity": gorm.Expr("quantity - ?", quantity),
            "version": gorm.Expr("version + 1")})
    if result.Error != nil { return result.Error }
    if result.RowsAffected != 1 { return fmt.Errorf("missing SKU or insufficient stock") }
    return nil
}

// Optimistic locking - version-based
func DecrementStockOptimistic(db *gorm.DB, sku string, quantity int) error {
    if quantity <= 0 { return fmt.Errorf("quantity must be positive") }
    maxRetries := 3
    
    for i := 0; i < maxRetries; i++ {
        var inventory Inventory
        if err := db.Where("sku = ?", sku).First(&inventory).Error; err != nil {
            return err
        }
        
        if inventory.Quantity < quantity {
            return fmt.Errorf("insufficient stock")
        }
        
        // Update with version check
        result := db.Model(&Inventory{}).
            Where("id = ? AND version = ?", inventory.ID, inventory.Version).
            Updates(map[string]interface{}{
                "quantity": inventory.Quantity - quantity,
                "version":  inventory.Version + 1,
            })
        
        if result.Error != nil { return result.Error }
        if result.RowsAffected > 0 {
            return nil // Success
        }
        
        // Version mismatch - retry
        time.Sleep(time.Millisecond * 100 * time.Duration(i+1))
    }
    
    return fmt.Errorf("optimistic lock failed after %d retries", maxRetries)
}

// Explicit optimistic locking; a `gorm:"version"` tag alone does NOT enable it.
// The separate gorm.io/plugin/optimisticlock package provides an alternative.
type Product struct {
    ID       uint   `gorm:"primaryKey"`
    Name     string
    Stock    int
    Version  int `gorm:"default:1"`
}

func UpdateProductStock(db *gorm.DB, id uint, newStock int) error {
    var product Product
    if err := db.First(&product, id).Error; err != nil {
        return err
    }
    
    result := db.Model(&Product{}).Where("id = ? AND version = ?", id, product.Version).
        Updates(map[string]interface{}{"stock": newStock, "version": product.Version + 1})
    if result.Error != nil { return result.Error }
    if result.RowsAffected != 1 { return fmt.Errorf("optimistic lock conflict") }
    return nil // Save could insert a missing row; do not use it as an update-only API.
}

// Concurrent operations demonstration
func ConcurrentDemo(db *gorm.DB) {
    // Setup
    if err := db.Create(&Inventory{SKU: "TEST-001", Quantity: 100}).Error; err != nil { panic(err) }
    
    var wg sync.WaitGroup
    errors := make(chan error, 10)
    
    // Simulate 10 concurrent purchases
    for i := 0; i < 10; i++ {
        wg.Add(1)
        go func(i int) {
            defer wg.Done()
            err := DecrementStockAtomic(db, "TEST-001", 5)
            if err != nil {
                errors <- fmt.Errorf("purchase %d failed: %v", i, err)
            }
        }(i)
    }
    
    wg.Wait()
    close(errors)
    
    // Check errors
    for err := range errors {
        fmt.Println(err)
    }
    
    // Verify final quantity
    var inventory Inventory
    if err := db.Where("sku = ?", "TEST-001").First(&inventory).Error; err != nil { panic(err) }
    fmt.Printf("Final quantity: %d\n", inventory.Quantity)
}

func main() {
    db, err := gorm.Open(sqlite.Open(":memory:"), &gorm.Config{})
    if err != nil { panic(err) }
    sqlDB, err := db.DB()
    if err != nil { panic(err) }
    sqlDB.SetMaxOpenConns(1) // :memory: uses one connection; SQLite serializes writes
    defer sqlDB.Close()
    if err := db.AutoMigrate(&Inventory{}, &Product{}); err != nil { panic(err) }
    
    ConcurrentDemo(db)
}

6. Hands-On Practice (30-40 minutes)

Exercise 1: Advanced Reporting Query (15 minutes)

Objective: Build a complex analytics query

Instructions:

Create a query that finds top 10 customers by total order value
Include only customers who have ordered in the last 90 days
Show customer name, total orders, total amount, average order value
Use scopes for reusable filters

Solution:

package main

import (
    "fmt"
    "time"

    "gorm.io/driver/sqlite"
    "gorm.io/gorm"
)

type Customer struct {
    ID        uint      `gorm:"primaryKey"`
    Name      string    `gorm:"size:100"`
    Email     string    `gorm:"size:255"`
    CreatedAt time.Time
}

type Order struct {
    ID         uint      `gorm:"primaryKey"`
    CustomerID uint      `gorm:"index"`
    Amount     float64   `gorm:"type:decimal(10,2)"`
    Status     string    `gorm:"size:20"`
    CreatedAt  time.Time
}

type CustomerStats struct {
    CustomerID   uint
    CustomerName string
    TotalOrders  int64
    TotalAmount  float64
    AvgAmount    float64
}

// Scopes
func RecentOrders(days int) func(db *gorm.DB) *gorm.DB {
    return func(db *gorm.DB) *gorm.DB {
        cutoff := time.Now().AddDate(0, 0, -days)
        return db.Where("orders.created_at >= ?", cutoff)
    }
}

func CompletedOrders(db *gorm.DB) *gorm.DB {
    return db.Where("orders.status = ?", "completed")
}

func TopCustomers(limit int) func(db *gorm.DB) *gorm.DB {
    return func(db *gorm.DB) *gorm.DB {
        return db.Order("total_amount DESC").Limit(limit)
    }
}

func GetTopCustomerStats(db *gorm.DB) ([]CustomerStats, error) {
    var stats []CustomerStats
    
    err := db.Table("customers").
        Select(`
            customers.id as customer_id,
            customers.name as customer_name,
            COUNT(orders.id) as total_orders,
            COALESCE(SUM(orders.amount), 0) as total_amount,
            COALESCE(AVG(orders.amount), 0) as avg_amount
        `).
        Joins("LEFT JOIN orders ON orders.customer_id = customers.id").
        Scopes(RecentOrders(90), CompletedOrders).
        Group("customers.id, customers.name").
        Having("COUNT(orders.id) > 0").
        Scopes(TopCustomers(10)).
        Scan(&stats).Error
    
    return stats, err
}

func main() {
    db, _ := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
    db.AutoMigrate(&Customer{}, &Order{})
    
    stats, err := GetTopCustomerStats(db)
    if err != nil {
        fmt.Printf("Error: %v\n", err)
        return
    }
    
    for _, s := range stats {
        fmt.Printf("%s: %d orders, $%.2f total, $%.2f avg\n",
            s.CustomerName, s.TotalOrders, s.TotalAmount, s.AvgAmount)
    }
}

Exercise 2: Transaction Implementation (10 minutes)

Objective: Implement order placement with inventory management

Exercise 3: Dynamic Query Builder (15 minutes)

Objective: Build a flexible search API with multiple filter options

7. Common Pitfalls and Troubleshooting (10 minutes)

Frequent Mistakes

Problem: SQL injection vulnerability Cause: String concatenation in queries Solution: Always use parameterized queries with ? placeholders
Problem: Transaction deadlocks Cause: Inconsistent lock ordering Solution: Always acquire locks in consistent order
Problem: Scope not applied Cause: Not assigning result back to query variable Solution: query = query.Scopes(...) or chain directly

Debugging Tips

Enable db.Debug() to see generated SQL
Use transactions for debugging rollback scenarios
Test scopes individually before combining
Check result.RowsAffected after updates

8. Best Practices and Professional Tips (5 minutes)

Industry Standards

Always use transactions for multi-table operations
Create scopes for repeated query patterns
Use pessimistic locking for high-contention scenarios
Prefer raw SQL only when GORM can’t express the query
Index columns used in WHERE, JOIN, and ORDER BY

Next Steps in Your Learning Journey

Study database-specific optimizations
Learn query profiling and EXPLAIN analysis
Explore caching strategies (Redis integration)
Practice with complex real-world scenarios

9. Summary and Review (5 minutes)

Key Takeaways

Complex queries can be built incrementally with chaining
Transactions ensure data integrity in multi-step operations
Scopes enable DRY, testable query patterns
Locking prevents data corruption in concurrent scenarios

Quick Review Questions

When should you use raw SQL vs GORM’s query builder?
What’s the difference between pessimistic and optimistic locking?
How do you create a reusable scope with parameters?
Why use clause.Locking instead of raw “SELECT FOR UPDATE”?

For Instructors

Teaching Tips

Show actual SQL output for each query technique
Demonstrate deadlock scenarios and solutions
Use realistic business scenarios for examples
Have students debug intentionally broken queries

Assessment Ideas

Write complex queries from business requirements
Identify and fix SQL injection vulnerabilities
Implement transaction for given scenario
Optimize slow queries

Adaptation Notes

For beginners: Focus on basic conditions and transactions
For advanced: Cover locking strategies and optimization
Use database explain plans for performance discussions