Queries

Soy provides two SELECT builders: Select() for single records and Query() for multiple. Both share the same filtering and ordering capabilities.

See API Reference for complete method signatures.

Single Record Queries

Select() returns a single record or an error if not found:

user, err := users.Select().
    Where("id", "=", "user_id").
    Exec(ctx, map[string]any{"user_id": 123})

if err != nil {
    // Includes "no rows found" when record doesn't exist
    return err
}
// user is guaranteed non-nil here

Multi-Record Queries

Query() returns a slice:

activeUsers, err := users.Query().
    Where("status", "=", "status").
    Exec(ctx, map[string]any{"status": "active"})

// Returns []*User, empty slice if no matches

Field Selection

Select specific columns:

// Only these fields populated
users.Select().Fields("id", "email", "name")

Unselected fields have zero values in the returned struct.

WHERE Conditions

Simple Conditions

users.Query().Where("field", "operator", "param_name")

The parameter name maps to the params map at execution:

users.Query().
    Where("age", ">=", "min_age").
    Where("status", "=", "status").
    Exec(ctx, map[string]any{
        "min_age": 18,
        "status":  "active",
    })

Multiple Where() calls produce AND:

WHERE age >= :min_age AND status = :status

AND Groups

Explicit AND grouping:

users.Query().WhereAnd(
    soy.C("age", ">=", "min"),
    soy.C("age", "<=", "max"),
    soy.C("status", "=", "status"),
)

WHERE (age >= :min AND age <= :max AND status = :status)

OR Groups

users.Query().WhereOr(
    soy.C("role", "=", "admin"),
    soy.C("role", "=", "moderator"),
    soy.C("role", "=", "editor"),
)

WHERE (role = :admin OR role = :moderator OR role = :editor)

Combined Conditions

users.Query().
    Where("active", "=", "is_active").
    WhereOr(
        soy.C("role", "=", "admin"),
        soy.C("role", "=", "mod"),
    )

WHERE active = :is_active AND (role = :admin OR role = :mod)

NULL Checks

// IS NULL
users.Query().WhereNull("deleted_at")

// IS NOT NULL
users.Query().WhereNotNull("verified_at")

IN Operator

users.Query().Where("status", "IN", "statuses")
// Execute with: {"statuses": []string{"active", "pending"}}

Pattern Matching

// LIKE (case-sensitive)
users.Query().Where("name", "LIKE", "pattern")
// {"pattern": "%alice%"}

// ILIKE (case-insensitive, PostgreSQL)
users.Query().Where("email", "ILIKE", "domain")
// {"domain": "%@example.com"}

Array Operations

// Contains
users.Query().Where("tags", "@>", "required_tags")
// {"required_tags": []string{"vip", "verified"}}

// Contained by
users.Query().Where("permissions", "<@", "allowed")

// Overlap
users.Query().Where("interests", "&&", "topics")

ORDER BY

Basic Ordering

users.Query().OrderBy("created_at", "desc")
users.Query().OrderBy("name", "asc")

Multiple Columns

users.Query().
    OrderBy("status", "asc").
    OrderBy("created_at", "desc")

ORDER BY status ASC, created_at DESC

NULL Handling

// NULLs appear first
users.Query().OrderByNulls("score", "desc", "first")

// NULLs appear last
users.Query().OrderByNulls("score", "asc", "last")

Expression Ordering

For computed values or pgvector:

// Order by vector distance
users.Query().OrderByExpr("embedding", "<->", "query_vector", "asc")

Pagination

users.Query().
    OrderBy("created_at", "desc").
    Limit(20).
    Offset(40)  // Page 3, 20 per page

DISTINCT

Simple Distinct

users.Query().Distinct()

DISTINCT ON (PostgreSQL)

Get first row per distinct column combination:

// First order per customer
orders.Query().
    DistinctOn("customer_id").
    OrderBy("customer_id", "asc").
    OrderBy("created_at", "desc")

SELECT DISTINCT ON (customer_id) * FROM orders
ORDER BY customer_id ASC, created_at DESC

GROUP BY and HAVING

Basic Grouping

users.Query().
    Fields("status", "COUNT(*) as count").
    GroupBy("status")

HAVING with Conditions

users.Query().
    Fields("status", "COUNT(*) as count").
    GroupBy("status").
    Having("count", ">=", "min_count")

Aggregate HAVING

users.Query().
    Fields("department_id").
    GroupBy("department_id").
    HavingAgg("COUNT", "*", ">", "threshold")

HAVING COUNT(*) > :threshold

Row Locking

For transactional consistency:

// Exclusive lock - other transactions wait
user, err := users.Select().
    Where("id", "=", "id").
    ForUpdate().
    ExecTx(ctx, tx, params)

// Shared lock - allows other shared locks
users.Select().ForShare()

// Less restrictive locks
users.Select().ForNoKeyUpdate()
users.Select().ForKeyShare()

Lock modes:

Lifecycle Callbacks

Register an OnScan callback to transform or validate every record as it is scanned. See the Lifecycle Guide for full details.

users.OnScan(func(ctx context.Context, result *User) error {
    result.Email = strings.ToLower(result.Email)
    return nil
})

// Fires per row in both Select and Query paths
user, _ := users.Select().Where("id", "=", "id").Exec(ctx, params)
// user.Email is lowercased

allUsers, _ := users.Query().Exec(ctx, nil)
// every user.Email is lowercased

Complete Example

// Complex query with multiple features
results, err := users.Query().
    Fields("id", "name", "email", "score").
    Where("status", "=", "status").
    WhereNotNull("email").
    WhereOr(
        soy.C("role", "=", "admin"),
        soy.C("score", ">=", "threshold"),
    ).
    OrderByNulls("score", "desc", "last").
    OrderBy("name", "asc").
    Limit(50).
    Offset(0).
    Exec(ctx, map[string]any{
        "status":    "active",
        "admin":     "admin",
        "threshold": 100,
    })

SELECT id, name, email, score
FROM users
WHERE status = :status
  AND email IS NOT NULL
  AND (role = :admin OR score >= :threshold)
ORDER BY score DESC NULLS LAST, name ASC
LIMIT 50 OFFSET 0