Conditions

Conditions define the WHERE clause of your queries. ASTQL provides type-safe condition building with support for complex logic.

Basic Conditions

Create a condition with field, operator, and parameter:

condition := instance.C(
    instance.F("email"),     // Field
    astql.EQ,                // Operator
    instance.P("email_val"), // Parameter
)

result, _ := astql.Select(instance.T("users")).
    Where(condition).
    Render()
// SELECT * FROM "users" WHERE "email" = :email_val

Operators

Comparison Operators

Pattern Matching

NULL Checks

// IS NULL
instance.Null(instance.F("deleted_at"))
// "deleted_at" IS NULL

// IS NOT NULL
instance.NotNull(instance.F("verified_at"))
// "verified_at" IS NOT NULL

Array Operators

// IN (uses PostgreSQL ANY)
instance.C(instance.F("id"), astql.IN, instance.P("ids"))
// "id" = ANY(:ids)

// NOT IN (uses PostgreSQL ALL)
instance.C(instance.F("status"), astql.NotIn, instance.P("excluded"))
// "status" != ALL(:excluded)

When executing with sqlx, wrap slices with pq.Array():

params := map[string]any{
    "ids": pq.Array([]int{1, 2, 3}),
}

Combining Conditions

AND

All conditions must be true:

instance.And(
    instance.C(instance.F("active"), astql.EQ, instance.P("is_active")),
    instance.C(instance.F("verified"), astql.EQ, instance.P("is_verified")),
)
// ("active" = :is_active AND "verified" = :is_verified)

OR

At least one condition must be true:

instance.Or(
    instance.C(instance.F("role"), astql.EQ, instance.P("admin")),
    instance.C(instance.F("role"), astql.EQ, instance.P("moderator")),
)
// ("role" = :admin OR "role" = :moderator)

Nested Logic

Combine AND and OR for complex conditions:

instance.And(
    instance.C(instance.F("active"), astql.EQ, instance.P("is_active")),
    instance.Or(
        instance.C(instance.F("role"), astql.EQ, instance.P("admin")),
        instance.And(
            instance.C(instance.F("role"), astql.EQ, instance.P("user")),
            instance.C(instance.F("verified"), astql.EQ, instance.P("is_verified")),
        ),
    ),
)
// ("active" = :is_active AND ("role" = :admin OR ("role" = :user AND "verified" = :is_verified)))

BETWEEN

Use Between and NotBetween for range conditions:

// BETWEEN
astql.Between(
    instance.F("price"),
    instance.P("min_price"),
    instance.P("max_price"),
)
// "price" BETWEEN :min_price AND :max_price

// NOT BETWEEN
astql.NotBetween(
    instance.F("date"),
    instance.P("start"),
    instance.P("end"),
)
// "date" NOT BETWEEN :start AND :end

Field Comparisons

Compare two fields directly:

astql.CF(
    instance.F("updated_at"),
    astql.GT,
    instance.F("created_at"),
)
// "updated_at" > "created_at"

Useful for:

• Comparing timestamps
• Self-referential conditions
• Cross-table comparisons in JOINs

Subqueries

IN Subquery

subquery := astql.Sub(
    astql.Select(instance.T("orders")).
        Fields(instance.F("user_id")).
        Where(instance.C(instance.F("total"), astql.GT, instance.P("min_total"))),
)

result, _ := astql.Select(instance.T("users")).
    Where(astql.CSub(instance.F("id"), astql.IN, subquery)).
    Render()

// SELECT * FROM "users"
// WHERE "id" IN (SELECT "user_id" FROM "orders" WHERE "total" > :sq1_min_total)

NOT IN Subquery

astql.CSub(instance.F("id"), astql.NotIn, subquery)
// "id" NOT IN (SELECT ...)

EXISTS

subquery := astql.Sub(
    astql.Select(instance.T("orders")).
        Fields(instance.F("id")).
        Where(astql.CF(
            instance.WithTable(instance.F("user_id"), "o"),
            astql.EQ,
            instance.WithTable(instance.F("id"), "u"),
        )),
)

result, _ := astql.Select(instance.T("users", "u")).
    Where(astql.CSubExists(astql.EXISTS, subquery)).
    Render()

// SELECT * FROM "users" u
// WHERE EXISTS (SELECT "id" FROM "orders" WHERE o."user_id" = u."id")

NOT EXISTS

astql.CSubExists(astql.NotExists, subquery)
// NOT EXISTS (SELECT ...)

Parameter Namespacing

Subquery parameters are automatically prefixed to prevent collisions:

// Main query: :user_id
// First subquery: :sq1_user_id
// Nested subquery: :sq2_user_id

Maximum subquery depth is 3 levels by default.

Multiple WHERE Clauses

Calling Where multiple times combines conditions with AND:

query := astql.Select(instance.T("users")).
    Where(instance.C(instance.F("active"), astql.EQ, instance.P("is_active"))).
    Where(instance.C(instance.F("role"), astql.EQ, instance.P("role")))

// WHERE ("active" = :is_active AND "role" = :role)

WhereField Shorthand

For simple conditions, use WhereField:

// These are equivalent:
query.Where(instance.C(instance.F("id"), astql.EQ, instance.P("id")))
query.WhereField(instance.F("id"), astql.EQ, instance.P("id"))

Building Conditions Dynamically

func BuildConditions(instance *astql.ASTQL, filters []Filter) astql.ConditionItem {
    if len(filters) == 0 {
        return nil
    }

    // Use instance.ConditionItems() to get a typed slice
    conditions := instance.ConditionItems()
    for _, f := range filters {
        field := instance.F(f.Field)
        param := instance.P(f.Param)
        conditions = append(conditions, instance.C(field, f.Operator, param))
    }

    if len(conditions) == 1 {
        return conditions[0]
    }
    return instance.And(conditions...)
}