Query Builder

Smart Query Builder

Pie provides rich query methods with chainable calls:

// Basic queries
session.Where("status", "active")
session.Where("age", pie.Gte("age", 18))
session.WhereIn("role", []string{"admin", "user"})
session.WhereBetween("age", 18, 65)
session.WhereNull("deleted_at")
session.WhereNotNull("email")

// Fuzzy queries
session.WhereLike("name", "%John%")
session.WhereStartsWith("email", "admin")
session.WhereEndsWith("domain", ".com")

// Date queries
session.WhereRecentDays("created_at", 7)
session.WhereMonth("created_at", time.Now().Month())
session.WhereYear("created_at", 2024)

// Complex conditions
session.Where("status", "active").
    OrWhere(func(q *pie.Query) {
        q.Where("role", "admin").WhereBetween("age", 30, 50)
    })

Query Methods

Comparison Operators

// Equal
session.Where("status", "active")

// Not equal
session.Where("status", pie.Ne("status", "inactive"))

// Greater than
session.Where("age", pie.Gt("age", 18))

// Greater than or equal
session.Where("age", pie.Gte("age", 18))

// Less than
session.Where("age", pie.Lt("age", 65))

// Less than or equal
session.Where("age", pie.Lte("age", 65))

// Between
session.WhereBetween("age", 18, 65)

// Not between
session.WhereNotBetween("age", 18, 65)

Array Operations

// In array
session.WhereIn("role", []string{"admin", "user"})

// Not in array
session.WhereNotIn("role", []string{"guest"})

// Array contains
session.WhereArrayContains("tags", "golang")

// Array size
session.WhereArraySize("tags", 3)

String Operations

// Like (regex)
session.WhereLike("name", "%John%")

// Starts with
session.WhereStartsWith("email", "admin")

// Ends with
session.WhereEndsWith("domain", ".com")

// Regex
session.WhereRegex("name", "^[A-Z]")

Null Operations

// Is null
session.WhereNull("deleted_at")

// Is not null
session.WhereNotNull("email")

// Is empty
session.WhereEmpty("description")

// Is not empty
session.WhereNotEmpty("description")

Date Operations

// Recent days
session.WhereRecentDays("created_at", 7)

// Recent hours
session.WhereRecentHours("updated_at", 24)

// Specific month
session.WhereMonth("created_at", time.January)

// Specific year
session.WhereYear("created_at", 2024)

// Date range
session.WhereDateBetween("created_at", startDate, endDate)

Complex Queries

Logical Operators

// AND conditions
session.Where("status", "active").
    Where("role", "user").
    Where("age", pie.Gte("age", 18))

// OR conditions
session.Where("status", "active").
    OrWhere("role", "admin")

// Nested conditions
session.Where("status", "active").
    OrWhere(func(q *pie.Query) {
        q.Where("role", "admin").
            Where("verified", true)
    })

Subqueries

// Exists subquery
session.WhereExists(func(q *pie.Query) {
    q.Table("orders").
        Where("user_id", pie.Raw("users._id")).
        Where("status", "completed")
})

// Not exists subquery
session.WhereNotExists(func(q *pie.Query) {
    q.Table("orders").
        Where("user_id", pie.Raw("users._id"))
})

Raw Queries

// Raw MongoDB query
session.WhereRaw(bson.D{
    {"$or", []bson.D{
        {{"age", bson.D{{"$gte", 18}}}},
        {{"role", "admin"}},
    }},
})

// Raw aggregation
session.AggregateRaw(bson.A{
    bson.D{{"$match", bson.D{{"status", "active"}}}},
    bson.D{{"$group", bson.D{
        {"_id", "$role"},
        {"count", bson.D{{"$sum", 1}}},
    }}},
})

Query Execution

Find Operations

// Find all
users, err := session.Find(ctx)

// Find first
user, err := session.FindOne(ctx)

// Find first without error when not found
user, err := session.FirstOne(ctx)

// Find with limit
users, err := session.Limit(10).Find(ctx)

// Find with offset
users, err := session.Offset(20).Find(ctx)

// Find with order
users, err := session.OrderBy("name").Find(ctx)

Count Operations

// Count all
count, err := session.Count(ctx)

// Count with conditions
count, err := session.Where("status", "active").Count(ctx)

// Exists check
exists, err := session.Where("email", "test@example.com").Exists(ctx)

Update Operations

// Update one
result, err := session.
    Where("email", "test@example.com").
    Update(ctx, bson.D{{"$set", bson.D{{"status", "active"}}}})

// Update many
result, err := session.
    Where("role", "guest").
    UpdateMany(ctx, bson.D{{"$set", bson.D{{"role", "user"}}}})

// Upsert
result, err := session.
    Where("email", "test@example.com").
    Upsert(ctx, bson.D{{"$set", bson.D{{"name", "Test User"}}}})

Delete Operations

// Delete one
result, err := session.
    Where("email", "test@example.com").
    Delete(ctx)

// Delete many
result, err := session.
    Where("status", "inactive").
    DeleteMany(ctx)

Performance Tips

Index Usage

// Use indexed fields for better performance
session.Where("email", "test@example.com") // email has index
session.Where("created_at", pie.Gte("created_at", time.Now().AddDate(0, -1, 0))) // created_at has index

Query Optimization

// Use projection to limit returned fields
users, err := session.
    Select("name", "email"). // Only select needed fields
    Where("status", "active").
    Find(ctx)

// Use limit to avoid large result sets
users, err := session.
    Where("status", "active").
    Limit(100). // Limit result size
    Find(ctx)

Caching

// Cache query results
users, err := session.
    Where("status", "active").
    Cache("active_users", 5*time.Minute). // Cache for 5 minutes
    Find(ctx)

Next Steps

• Generics Guide - Learn about generics usage and benefits
• Struct Query - Convert HTTP params to queries
• Pagination - Implement pagination
• Cursor Operations - Use cursors for large datasets
• Aggregation - Advanced aggregation queries