Advanced Queries

ChameleonDB supports advanced query operations for sorting, pagination, field projection, and debugging.

Order By

Sort results by one or more fields:

users, err := db.Users().
    OrderBy("created_at", "desc").
    Execute()

SELECT id, email, name, age, created_at
FROM users
ORDER BY created_at DESC;

Multiple Order Clauses

Chain multiple OrderBy() calls for complex sorting:

users, err := db.Users().
    OrderBy("name", "asc").
    OrderBy("created_at", "desc").
    Execute()

SELECT id, email, name, age, created_at
FROM users
ORDER BY name ASC, created_at DESC;

The order of OrderBy() calls matters. The first call is the primary sort, the second is the secondary sort, etc.

Limit and Offset

Paginate results using Limit() and Offset():

users, err := db.Users().
    OrderBy("created_at", "desc").
    Limit(10).
    Offset(20).
    Execute()

SELECT id, email, name, age, created_at
FROM users
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;

Best practice: Always use OrderBy() with Limit()/Offset() to ensure deterministic pagination.Without ordering, the database may return results in arbitrary order, causing inconsistent pagination.

Pagination Example

Implementing cursor-based pagination:

func GetUsers(page int, pageSize int) ([]User, error) {
    offset := (page - 1) * pageSize
    
    users, err := db.Users().
        OrderBy("created_at", "desc").
        OrderBy("id", "asc").  // Secondary sort for consistency
        Limit(pageSize).
        Offset(offset).
        Execute()
    
    return users, err
}

// Usage:
page1, _ := GetUsers(1, 10)  // First 10 users
page2, _ := GetUsers(2, 10)  // Next 10 users
page3, _ := GetUsers(3, 10)  // Next 10 users

Select (Field Projection)

Select only the fields you need:

users := db.Query("User").
    Select("id", "name", "email").
    Execute(ctx)

SELECT id, name, email
FROM users;

Performance: Field projection reduces memory usage and network transfer. Only select the fields you actually need.

Debug Mode

See generated SQL and query performance:

users := db.Query("User").
    Select("id", "name").
    Filter("email", "like", "ana").
    Debug().
    Execute(ctx)

[SQL] Query User
SELECT id, name FROM users WHERE email LIKE '%ana%'

[TRACE] Query on User: 2.3ms, 3 rows

Debug Output Includes:

Generated SQL - The exact SQL sent to the database
Execution time - How long the query took
Row count - Number of rows returned

Use .Debug() during development to understand query performance and verify SQL generation.

Combining Everything

A realistic query combining multiple features:

users, err := db.Users().
    Filter("age", "gte", 18).
    Filter("orders.total", "gt", 50).
    Include("orders").
    Include("orders.items").
    OrderBy("created_at", "desc").
    Limit(10).
    Execute()

-- 1. Main query
SELECT DISTINCT users.id, users.email, users.name, users.age, users.created_at
FROM users
INNER JOIN orders ON orders.user_id = users.id
WHERE users.age >= 18
  AND orders.total > 50
ORDER BY users.created_at DESC
LIMIT 10;

-- 2. Eager load orders
SELECT id, total, status, created_at, user_id
FROM orders
WHERE user_id IN (...);

-- 3. Eager load order items
SELECT id, quantity, price, order_id
FROM order_items
WHERE order_id IN (...);

What This Query Does:

Finds users who are 18+ years old
Who have at least one order with total > $50
Loads all their orders (not just the ones > $50)
Loads all items for those orders
Sorts by most recent users first
Returns only the first 10 results

Complex Filtering Example

// Find active users aged 25-40 with recent orders
users, err := db.Users().
    Filter("status", "eq", "active").
    Filter("age", "gte", 25).
    Filter("age", "lte", 40).
    Filter("orders.created_at", "gte", lastWeek).
    Include("orders").
    OrderBy("created_at", "desc").
    Limit(50).
    Execute()

Validation Rules

ChameleonDB validates queries before execution:

Rule	Error	Solution
Pagination without order	`.Limit(10)` without `.OrderBy()` warns	Add `.OrderBy()`
Unknown field	`.Filter("invalid_field", ...)`	Use valid field name
Unknown entity	`db.Products()` (not in schema)	Define entity in schema
Invalid relation	`.Include("invalid.path")`	Use valid relation path
Type mismatch	`.Filter("age", "eq", "string")`	Use correct type

Query Performance Tips

Use field projection

Only select the fields you need with .Select() to reduce memory and network transfer.

// ❌ Loads all fields
db.Users().Execute()

// ✅ Loads only needed fields
db.Users().Select("id", "name", "email").Execute()

Use eager loading wisely

Only include relations you actually need. Each Include() triggers an additional query.

// ❌ Loads unnecessary data
db.Users().
    Include("orders").
    Include("orders.items").
    Include("profile").
    Include("settings").Execute()

// ✅ Only load what you need
db.Users().Include("orders").Execute()

Always order when paginating

Without ordering, pagination results may be inconsistent.

// ❌ Unpredictable results
db.Users().Limit(10).Offset(20).Execute()

// ✅ Deterministic pagination
db.Users().
    OrderBy("created_at", "desc").
    OrderBy("id", "asc").
    Limit(10).
    Offset(20).
    Execute()

Use Debug mode to optimize

See the generated SQL to identify performance issues.

db.Users().
    Filter("orders.total", "gt", 100).
    Include("orders").
    Debug().  // ← Shows SQL and timing
    Execute()

Limitations (v0.1)

These features are not supported in the current version:

Aggregations (count, sum, avg)
Group by
Subqueries
Transactions
OR logic (all filters are AND)

These features are planned for future versions. Track progress on GitHub.

Complete Example

package main

import (
    "context"
    "fmt"
    "log"
    
    "github.com/chameleon-db/chameleondb/chameleon/pkg/engine"
)

func main() {
    ctx := context.Background()
    eng, err := engine.NewEngine()
    if err != nil {
        log.Fatal(err)
    }
    defer eng.Close()
    
    // Complex query with all features
    result, err := eng.Query("User").
        Select("id", "name", "email", "age").
        Filter("age", "gte", 18).
        Filter("status", "eq", "active").
        Filter("orders.total", "gt", 100).
        Include("orders").
        OrderBy("created_at", "desc").
        Limit(20).
        Debug().
        Execute(ctx)
    
    if err != nil {
        log.Fatal(err)
    }
    
    // Process results
    for _, user := range result.Rows {
        fmt.Printf("User: %s (%d)\n", user["name"], user["age"])
        
        if orders, ok := result.Relations["orders"]; ok {
            fmt.Printf("  Orders: %d\n", len(orders))
            for _, order := range orders {
                fmt.Printf("  - Total: $%.2f\n", order["total"])
            }
        }
    }
}

What’s Next?

Filtering

Review filter operators (eq, gt, like, in)

Relations

Master eager loading with Include()

Mutations

Learn about Insert, Update, and Delete operations

Get Started

Core Concepts

Schema Language

Query System

Mutations

Guides

Security

Order By

Multiple Order Clauses

Limit and Offset

Select (Field Projection)

Debug Mode

Debug Output Includes:

Combining Everything

What This Query Does:

Complex Filtering Example

Validation Rules

Query Performance Tips

Limitations (v0.1)

Complete Example

What’s Next?

Filtering

Relations

Mutations

​Order By

​Multiple Order Clauses

​Limit and Offset

​Pagination Example

​Select (Field Projection)

​Debug Mode

​Debug Output Includes:

​Combining Everything

​What This Query Does:

​Complex Filtering Example

​Validation Rules

​Query Performance Tips

​Limitations (v0.1)

​Complete Example

​What’s Next?

Filtering

Relations

Mutations

Order By

Multiple Order Clauses

Limit and Offset

Pagination Example

Select (Field Projection)

Debug Mode

Debug Output Includes:

Combining Everything

What This Query Does:

Complex Filtering Example

Validation Rules

Query Performance Tips

Limitations (v0.1)

Complete Example

What’s Next?