Compound Queries

Compound queries combine multiple SELECT statements using set operations. Soy supports all standard SQL set operations.

See API Reference for complete method signatures.

Set Operations

UNION

Combine rows from multiple queries, removing duplicates:

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

SELECT * FROM users WHERE status = :active
UNION
SELECT * FROM users WHERE role = :admin

UNION ALL

Keep duplicates:

results, err := users.Query().
    Where("department", "=", "engineering").
    UnionAll(
        users.Query().Where("department", "=", "design"),
    ).
    Exec(ctx, params)

INTERSECT

Rows that appear in both queries:

// Users who are both active AND premium
results, err := users.Query().
    Where("status", "=", "active").
    Intersect(
        users.Query().Where("tier", "=", "premium"),
    ).
    Exec(ctx, params)

INTERSECT ALL

Preserve duplicates in intersection:

results, err := users.Query().
    Where("region", "=", "us").
    IntersectAll(
        users.Query().Where("verified", "=", "true"),
    ).
    Exec(ctx, params)

EXCEPT

Rows in the first query but not the second:

// Active users who are NOT admins
results, err := users.Query().
    Where("status", "=", "active").
    Except(
        users.Query().Where("role", "=", "admin"),
    ).
    Exec(ctx, params)

EXCEPT ALL

Subtract with duplicate handling:

results, err := users.Query().
    Where("region", "=", "us").
    ExceptAll(
        users.Query().Where("opted_out", "=", "true"),
    ).
    Exec(ctx, params)

Chaining Operations

Combine multiple set operations:

results, err := users.Query().
    Where("status", "=", "active").
    Union(
        users.Query().Where("status", "=", "pending"),
    ).
    Except(
        users.Query().Where("banned", "=", "true"),
    ).
    Exec(ctx, params)

SELECT * FROM users WHERE status = :active
UNION
SELECT * FROM users WHERE status = :pending
EXCEPT
SELECT * FROM users WHERE banned = :true

ORDER BY and LIMIT

Apply ordering and pagination to the final result:

results, err := users.Query().
    Where("region", "=", "us").
    Union(
        users.Query().Where("region", "=", "eu"),
    ).
    OrderBy("name", "asc").
    Limit(50).
    Offset(0).
    Exec(ctx, params)

ORDER BY and LIMIT apply to the combined result, not individual queries.

From Specs

Build compound queries from JSON specifications:

spec := soy.CompoundQuerySpec{
    Base: soy.QuerySpec{
        Where: []soy.ConditionSpec{
            {Field: "status", Operator: "=", Param: "active"},
        },
    },
    Operands: []soy.SetOperandSpec{
        {
            Operation: "union",
            Query: soy.QuerySpec{
                Where: []soy.ConditionSpec{
                    {Field: "status", Operator: "=", Param: "pending"},
                },
            },
        },
        {
            Operation: "except",
            Query: soy.QuerySpec{
                Where: []soy.ConditionSpec{
                    {Field: "banned", Operator: "=", Param: "is_banned"},
                },
            },
        },
    },
    OrderBy: []soy.OrderBySpec{
        {Field: "created_at", Direction: "desc"},
    },
    Limit: intPtr(100),
}

compound := users.CompoundFromSpec(spec)
results, err := compound.Exec(ctx, map[string]any{
    "active":    "active",
    "pending":   "pending",
    "is_banned": true,
})

Column Requirements

All queries in a compound must have compatible columns. By default, all queries select all columns.

Select specific fields for all queries:

results, err := users.Query().
    Fields("id", "email", "name").
    Where("status", "=", "active").
    Union(
        users.Query().
            Fields("id", "email", "name").
            Where("status", "=", "pending"),
    ).
    Exec(ctx, params)

Transactions

Execute compound queries within transactions:

tx, err := db.BeginTxx(ctx, nil)
if err != nil {
    return err
}
defer tx.Rollback()

results, err := users.Query().
    Where("status", "=", "active").
    Union(
        users.Query().Where("status", "=", "pending"),
    ).
    ExecTx(ctx, tx, params)
if err != nil {
    return err
}

// Process results...

return tx.Commit()

Inspecting SQL

result, err := users.Query().
    Where("region", "=", "us").
    Union(
        users.Query().Where("region", "=", "eu"),
    ).
    OrderBy("name", "asc").
    Limit(10).
    Render()

fmt.Println(result.SQL)

Complete Example

// Find users eligible for a promotion:
// - Active users with high spending OR
// - Long-term users (>2 years) OR
// - VIP tier users
// EXCEPT users who already received promotion

func GetEligibleUsers(ctx context.Context) ([]*User, error) {
    cutoff := time.Now().AddDate(-2, 0, 0)

    results, err := users.Query().
        Where("status", "=", "status").
        Where("total_spent", ">=", "min_spent").
        Union(
            users.Query().
                Where("created_at", "<", "cutoff"),
        ).
        Union(
            users.Query().
                Where("tier", "=", "tier"),
        ).
        Except(
            users.Query().
                Where("received_promo", "=", "received"),
        ).
        OrderBy("total_spent", "desc").
        Limit(1000).
        Exec(ctx, map[string]any{
            "status":    "active",
            "min_spent": 500.00,
            "cutoff":    cutoff,
            "tier":      "vip",
            "received":  true,
        })

    return results, err
}