# Conditions

> Filtering rows with Where, from simple comparisons to combined and multi-column conditions.

Source: https://skudra.net/queries/conditions

`Where` takes `ant.Cond` values built from a [field pointer](https://skudra.net/models/field-probes.md) and a value. The compiler checks that the value has the column's type.

## Compare a column with a value

```go
users, err := db.Users.Where(ant.Gte(&Users.Age, 18)).All(ctx)
// SELECT ... FROM "users" WHERE "users"."age" >= $1
```

Conditions passed to one `Where` are joined with `AND`. Calling `Where` again adds more:

```go
users, err := db.Users.
	Where(ant.Gte(&Users.Age, 18), ant.Lt(&Users.Age, 65)).
	Where(ant.Ne(&Users.Name, "Bob")).
	All(ctx)
// SELECT ... FROM "users"
// WHERE "users"."age" >= $1 AND "users"."age" < $2 AND "users"."name" <> $3
```

| Condition | SQL |
| --- | --- |
| `ant.Eq(&Users.Age, 18)` | `age = $1` |
| `ant.Ne(&Users.Age, 18)` | `age <> $1` |
| `ant.Gt`, `ant.Gte`, `ant.Lt`, `ant.Lte` | `>`, `>=`, `<`, `<=` |
| `ant.Between(&Users.Age, 18, 65)` | `age BETWEEN $1 AND $2` (both ends included) |
| `ant.In(&Users.Age, 18, 21)` | `age IN ($1, $2)` |
| `ant.NotIn(&Users.Age, 18, 21)` | `age NOT IN ($1, $2)` |
| `ant.Like(&Users.Name, "A%")` | `name LIKE $1` |
| `ant.ILike(&Users.Name, "a%")` | `name ILIKE $1` (case-insensitive) |
| `ant.IsNull(&Users.Bio)`, `ant.IsNotNull(&Users.Bio)` | `bio IS NULL`, `bio IS NOT NULL` |

The orderings (`Gt`, `Between` and the rest) take numbers, strings and `time.Time`. `ILike` is native on PostgreSQL; on the other databases ant writes it as `LIKE` between lowered values.

`ant.Eq(&Users.Age, 0)` matches rows where the age is 0. Zero values are never dropped from the query.

## Match one of several values

`In` is variadic:

```go
names := []string{"Alice", "Bob"}

users, err := db.Users.Where(ant.In(&Users.Name, names...)).All(ctx)
// SELECT ... FROM "users" WHERE "users"."name" IN ($1, $2)
```

`In` with no values matches no rows. `NotIn` with no values matches every row:

```go
users, err := db.Users.Where(ant.In(&Users.Name)).All(ctx)
// SELECT ... FROM "users" WHERE 1 = 0
```

:::warning[Empty lists in bulk writes]

`NotIn` with an empty slice matches every row. A [bulk update or delete](https://skudra.net/writing/update-and-delete.md#the-no-condition-guard) refuses a condition that can't exclude a row, so a `NotIn` built from an empty slice fails there instead of changing the whole table.

:::

On PostgreSQL, a list longer than the parameters one statement can take (65,535) is sent as one array, `= ANY($1)`, when its values are plain and of one type. The other databases have lower limits and no arrays, so for very long lists use [`InSub`](https://skudra.net/queries/subqueries-and-sets.md) or split the list.

## NULL

`IsNull` and `IsNotNull` check for NULL. `Eq` with a nil pointer means the same thing:

```go
var noBio *string

users, err := db.Users.Where(ant.Eq(&Users.Bio, noBio)).All(ctx)
// SELECT ... FROM "users" WHERE "users"."bio" IS NULL
```

A nil among the values of `In` or `NotIn` matches NULL rows, as with `Eq`. Plain SQL doesn't.

```go
bio := "Go developer"

users, err := db.Users.Where(ant.In(&Users.Bio, noBio, &bio)).All(ctx)
// SELECT ... FROM "users" WHERE ("users"."bio" IN ($1) OR "users"."bio" IS NULL)

users, err = db.Users.Where(ant.NotIn(&Users.Bio, noBio, &bio)).All(ctx)
// SELECT ... FROM "users" WHERE ("users"."bio" NOT IN ($1) AND "users"."bio" IS NOT NULL)
```

See [nullable fields](https://skudra.net/models/nullable-and-defaults.md#querying-for-null) for how pointer and `sql.Null` fields map to columns.

## And, Or, Not

```go
users, err := db.Users.
	Where(
		ant.Or(
			ant.Lt(&Users.Age, 18),
			ant.And(ant.Gt(&Users.Age, 65), ant.Like(&Users.Name, "A%")),
		),
		ant.Not(ant.Eq(&Users.Name, "Bob")),
	).
	All(ctx)
// SELECT ... FROM "users"
// WHERE ("users"."age" < $1 OR ("users"."age" > $2 AND "users"."name" LIKE $3))
//   AND NOT ("users"."name" = $4)
```

Parentheses follow the nesting. An empty `ant.And()` matches every row and an empty `ant.Or()` matches none, so both are safe to build from a loop:

```go
var filters []ant.Cond

for _, name := range []string{"Alice", "Bob"} {
	filters = append(filters, ant.Eq(&Users.Name, name))
}

users, err := db.Users.Where(ant.Or(filters...)).All(ctx)
// SELECT ... FROM "users" WHERE ("users"."name" = $1 OR "users"."name" = $2)
```

## Compare two columns

`EqCol` compares one column with another. Both fields must have the same Go type.

```go
untouched, err := db.Users.Where(ant.EqCol(&Users.CreatedAt, &Users.UpdatedAt)).All(ctx)
// SELECT ... FROM "users" WHERE "users"."created_at" = "users"."updated_at"
```

It's also how a [subquery](https://skudra.net/queries/subqueries-and-sets.md) refers to a column of the outer query.

## Search several columns for one value

`AnyOf`, `AllOf` and `NoneOf` compare one value with several columns:

```go
users, err := db.Users.Where(ant.AnyOf("bob", &Users.Name, &Users.Email)).All(ctx)
// SELECT ... FROM "users" WHERE ("users"."name" = $1 OR "users"."email" = $2)
```

| Condition | SQL |
| --- | --- |
| `ant.AnyOf(v, &Users.Name, &Users.Email)` | `(name = $1 OR email = $2)` |
| `ant.AllOf(v, &Users.Name, &Users.Email)` | `(name = $1 AND email = $2)` |
| `ant.NoneOf(v, &Users.Name, &Users.Email)` | `NOT ((name = $1 OR email = $2))` |

For partial matches, combine `Like` conditions with `Or` instead.

## Match pairs of values

`InTuples` matches several columns together, such as composite keys. Separate `In` conditions on each column would also match mixed pairs, like Alice at 17.

```go
columns := []ant.FieldRef{ant.F(&Users.Name), ant.F(&Users.Age)}

users, err := db.Users.
	Where(ant.InTuples(columns, []any{"Alice", 30}, []any{"Bob", 17})).
	All(ctx)
// SELECT ... FROM "users" WHERE ("users"."name", "users"."age") IN (($1, $2), ($3, $4))
```

## Dates

`Past`, `Future` and `Today` compare with the database's clock, not the app server's.

```go
users, err := db.Users.Where(ant.Past(&Users.CreatedAt)).All(ctx)
// SELECT ... FROM "users" WHERE "users"."created_at" < CURRENT_TIMESTAMP

users, err = db.Users.Where(ant.Today(&Users.CreatedAt)).All(ctx)
// SELECT ... FROM "users" WHERE (CAST("users"."created_at" AS date) = CURRENT_DATE)
```

To compare with a time of your own, use the orderings: `ant.Gt(&Users.CreatedAt, time.Now().Add(-24*time.Hour))`. `ant.DatePart(&Users.CreatedAt, "year")` reads part of a date as an [expression](https://skudra.net/queries/expressions.md), on PostgreSQL.

:::note[Run the database in UTC]

`Past` and `Future` use the database's current time. On SQLite and SQL Server, where columns keep no time zone, ant stores times in UTC, so those servers should run in UTC too. See [databases](https://skudra.net/reference/databases.md).

:::

## Reuse conditions

A condition is a value. Name it once and use it in any number of queries:

```go
adults := ant.Gte(&Users.Age, 18)

count, err := db.Users.Where(adults).Count(ctx)
// SELECT COUNT(*) FROM "users" WHERE "users"."age" >= $1

users, err := db.Users.Where(adults).OrderBy(&Users.Name).All(ctx)
// SELECT ... FROM "users" WHERE "users"."age" >= $1 ORDER BY "users"."name"
```

Functions that return conditions share filters across a codebase. For filters every query of a model should get, see [global filters](https://skudra.net/lifecycle/global-filters.md) and [scopes](https://skudra.net/queries/composing.md).

Tested in [tests/features/querying](https://github.com/skudra-go/ant/tree/v0.3.1/tests/features/querying).
