Search
CRUD covers loading entities by primary key. This section covers finding entities by their column values with the type-safe query builder orm.DBQuery, sorting, pagination, and the generated SearchOne, SearchMany, SearchManyWithTotal and Count methods.
All search methods are generated on the entity's Provider and query MySQL. How the rows are read depends on the entity:
- An entity with
orm:"redisCache"selects only theIDcolumn and then hydrates the entities throughGetByID/GetByIDs, so results pass through the context cache and the Redis row cache exactly like primary-key loads. - An entity without
redisCachehas no row cache to go through, so the search query selects all columns itself: one round trip instead of two. The context cache is still honoured (see SearchMany).
Tips
Search runs in MySQL only. Redis Search was removed: there are no *InRedis methods, and Validate() rejects the redisSearch, searchable and sortable tags. For full-text search see Elasticsearch.
Typed Field Descriptors
After code generation every Provider has a Fields struct with one typed descriptor per column. Descriptors build orm.Condition values for Filter() and implement orm.Field for SortByASC()/SortByDESC().
// Entity definition
type UserEntity struct {
ID uint64 `orm:"redisCache"`
Name string `orm:"required"`
Email string
Age uint32
Score *float64
Status string `orm:"enum=active,inactive;required"`
Country orm.Reference[CountryEntity]
CreatedAt time.Time
}
func (e UserEntity) UniqueIndexes() [][]string { return [][]string{{"Email"}} }
func (e UserEntity) CachedUniqueIndexes() [][]string { return [][]string{{"Email"}} }
// Generated descriptors (package entities):
// entities.UserEntityProvider.Fields.ID orm.UintField
// entities.UserEntityProvider.Fields.Name orm.StringField
// entities.UserEntityProvider.Fields.Email orm.NullableStringField (string without `required`)
// entities.UserEntityProvider.Fields.Age orm.UintField
// entities.UserEntityProvider.Fields.Score orm.NullableFloatField
// entities.UserEntityProvider.Fields.Status orm.EnumField
// entities.UserEntityProvider.Fields.Country orm.NullableUintField (Reference without `required`)
// entities.UserEntityProvider.Fields.CreatedAt orm.TimeField
Go type to descriptor mapping
| Go field | Tag | Descriptor |
|---|---|---|
ID uint64 | UintField (always) | |
uint8 .. uint64 | UintField | |
int8 .. int64 | IntField | |
float32, float64 | FloatField | |
bool | BoolField | |
time.Time (date or orm:"time") | TimeField | |
string | required | StringField |
string | (not required) | NullableStringField |
string | enum=... or enumName=... + required | EnumField |
string | enum=... or enumName=..., not required | NullableEnumField |
orm.Reference[T] | required | ReferenceField |
orm.Reference[T] | (not required) | NullableUintField |
*uint8 .. *uint64 | NullableUintField | |
*int8 .. *int64 | NullableIntField | |
*float32, *float64 | NullableFloatField | |
*bool | NullableBoolField | |
*time.Time | NullableTimeField | |
*string | NullableStringField |
Not present in Fields: the FakeDelete column, []uint8 (blob) columns, set= columns, orm.References[T] columns and JSON struct pointer columns.
Tips
The orm.NullableReferenceField type exists in the library but the generator never emits it: an optional reference is exposed as NullableUintField, which has the same Eq/In/IsNull/IsNotNull methods.
Condition methods per descriptor
Every method returns a orm.Condition. Column names are always back-quoted in the generated SQL.
| Descriptor | Methods | SQL |
|---|---|---|
UintField | Eq(v uint64), Gte(v uint64), Lte(v uint64), Gt(v uint64), Lt(v uint64), In(values ...uint64) | `col` = ?, >=, <=, >, <, `col` IN (?,?,...) |
IntField | Eq, Gte, Lte, Gt, Lt (int64), In(values ...int64) | same |
FloatField | Eq, Gte, Lte, Gt, Lt (float64), In(values ...float64) | same |
TimeField | Eq, Gte, Lte, Gt, Lt (time.Time) | same, no In |
StringField | Is(v string), Like(v string), In(values ...string), IsEmpty() | `col` = ?, `col` LIKE ?, IN, `col` = '' |
BoolField | Is(v bool) | `col` = ? |
EnumField | Is(v any), In(values ...any) | `col` = ?, IN |
ReferenceField | Eq(v uint64), In(values ...uint64) | `col` = ?, IN |
NullableUintField | UintField methods + IsNull(), IsNotNull() | `col` IS NULL, `col` IS NOT NULL |
NullableIntField | IntField methods + IsNull(), IsNotNull() | |
NullableFloatField | FloatField methods + IsNull(), IsNotNull() | |
NullableTimeField | TimeField methods + IsNull(), IsNotNull() | |
NullableStringField | Is, Like, In, IsEmpty() + IsNull(), IsNotNull() | |
NullableBoolField | Is(v bool) + IsNull(), IsNotNull() | |
NullableEnumField | Is(v any), In(values ...any) + IsNull(), IsNotNull() |
EnumField.Is/In take any, so you can pass the generated enum constants directly:
import "myapp/entities/enums"
entities.UserEntityProvider.Fields.Status.Is(enums.UserStatusList.Active)
entities.UserEntityProvider.Fields.Status.In(enums.UserStatusList.Active, enums.UserStatusList.Inactive)
Negating a condition
Every Condition has Not() Condition, which wraps the predicate in NOT (...). Calling Not() on an already negated condition returns the original one.
entities.UserEntityProvider.Fields.Status.Is(enums.UserStatusList.Inactive).Not()
// NOT (`Status` = ?)
entities.UserEntityProvider.Fields.Name.Like("%test%").Not()
// NOT (`Name` LIKE ?)
Building Queries with NewQuery
orm.NewQuery() returns an empty *orm.DBQuery. All builder methods return the query, so they chain:
import "github.com/DropletLive/orm"
query := orm.NewQuery().
Filter(
entities.UserEntityProvider.Fields.Age.Gte(18),
entities.UserEntityProvider.Fields.Status.Is(enums.UserStatusList.Active),
).
SortByDESC(entities.UserEntityProvider.Fields.CreatedAt).
Pager(orm.NewPager(1, 20))
// SELECT `ID` FROM `UserEntity` WHERE `Age` >= ? AND `Status` = ? ORDER BY `CreatedAt` DESC LIMIT 0,20
// (an entity without redisCache selects all columns instead of `ID`)
| Method | Behaviour |
|---|---|
Filter(conditions ...Condition) *DBQuery | Appends typed conditions. Every call adds to the list; all conditions are joined with AND. |
FilterWhere(w Where) *DBQuery | Sets the single raw Where slot. A second call replaces the first one. |
SortByASC(f Field) *DBQuery / SortByDESC(f Field) *DBQuery | Append a sort clause; several calls produce ORDER BY a ASC, b DESC. |
Pager(p *Pager) *DBQuery | Sets the LIMIT clause. Without a pager SearchMany has no LIMIT. |
GetConditions() []Condition | Returns the typed conditions (used by generated SearchOne for cached unique index detection). |
BuildWhereClause() (string, []any) | Typed conditions followed by the raw where, joined with AND; "", nil when empty. |
BuildOrderClause() string | ORDER BY ... or "". |
BuildLimitClause() string | LIMIT <offset>,<size> or "". |
WriteSelect(b *strings.Builder, prefix string, excludeFakeDeleted, withOrder, withLimit bool) []any | Appends prefix, then WHERE ... (typed conditions, the raw where and, when excludeFakeDeleted is set and the raw where does not allow fake-deleted rows, `FakeDelete` = 0), ORDER BY ... when withOrder and LIMIT ... when withLimit; returns the bound parameters in SQL order. This is what the generated search methods call. |
PageSize() int | The pager's page size, 0 without a pager. |
IsWithFakeDeletes() bool | true only when the raw where was created with WithFakeDeletes(). |
FilterWhere: raw SQL fallback
For predicates that the typed descriptors cannot express, pass a Where built with orm.NewWhere(). Typed conditions and the raw where are combined with AND:
query := orm.NewQuery().
Filter(entities.UserEntityProvider.Fields.Status.Is(enums.UserStatusList.Active)).
FilterWhere(orm.NewWhere("(`Name` LIKE ? OR `Email` LIKE ?)", "%john%", "%john%"))
// WHERE `Status` = ? AND (`Name` LIKE ? OR `Email` LIKE ?)
Warning
FilterWhere holds one Where. If you need several raw fragments, build one Where and extend it with Append() instead of calling FilterWhere twice.
The Where object
func orm.NewWhere(query string, parameters ...any) *orm.BaseWhere creates a raw SQL fragment with ? placeholders. *BaseWhere implements the orm.Where interface (String(), GetParameters(), IsWithFakeDeletes()).
where := orm.NewWhere("`Email` = ? AND `Age` >= ?", "alice@example.com", 18)
where.String() // "`Email` = ? AND `Age` >= ?"
where.GetParameters() // []any{"alice@example.com", 18}
// A slice or array parameter expands the first "IN ?" into "IN (?,?,...)":
where = orm.NewWhere("`Age` IN ?", []int{18, 20, 30})
where.String() // "`Age` IN (?,?,?)"
where.GetParameters() // []any{18, 20, 30}
// Extend an existing where (a leading space is added when missing):
where.Append("AND `Status` = ?", "active")
where.String() // "`Age` IN (?,?,?) AND `Status` = ?"
// Replace parameters after construction:
where.SetParameter(1, 21) // 1-based index
where.SetParameters(21, 22, 23, "active")
| Method | Description |
|---|---|
String() string | The SQL fragment |
GetParameters() []any | The bound parameters |
Append(query string, parameters ...any) | Appends another fragment and its parameters |
SetParameter(index int, param any) *BaseWhere | Replaces one parameter (1-based) |
SetParameters(params ...any) *BaseWhere | Replaces all parameters |
WithFakeDeletes() *BaseWhere | Marks the query to include fake-deleted rows (see below) |
IsWithFakeDeletes() bool | Whether WithFakeDeletes() was called |
Pager
orm.NewPager(currentPage, pageSize int) *orm.Pager describes one page. Pages are 1-based; String() renders LIMIT <(page-1)*size>,<size>.
pager := orm.NewPager(1, 100)
pager.GetPageSize() // 100
pager.GetCurrentPage() // 1
pager.String() // "LIMIT 0,100"
pager.IncrementPage()
pager.GetCurrentPage() // 2
pager.String() // "LIMIT 100,100"
// The fields are exported as well:
pager.CurrentPage = 5
pager.PageSize = 25
The pager performs no validation: page 0 or a negative page produces a negative offset, so always start from page 1.
SearchMany
func (p userEntityProvider) SearchMany(ctx orm.Context, query *orm.DBQuery) ([]*entities.UserEntity, error)
Without a Pager there is no LIMIT, so an empty query loads the whole table. The SQL depends on the entity:
| Entity | Queries |
|---|---|
with redisCache | SELECT `ID` FROM <table> [WHERE ...] [ORDER BY ...] [LIMIT ...], then GetByIDs for the ids |
without redisCache | SELECT `ID`,<all columns> FROM <table> [WHERE ...] [ORDER BY ...] [LIMIT ...] - one query, no GetByIDs |
users, err := entities.UserEntityProvider.SearchMany(ctx,
orm.NewQuery().
Filter(entities.UserEntityProvider.Fields.Age.Gte(18)).
SortByASC(entities.UserEntityProvider.Fields.Name).
Pager(orm.NewPager(1, 100)),
)
if err != nil {
return err
}
for _, user := range users {
fmt.Println(user.GetName())
}
The returned slice keeps the SQL order; it is nil when no row matches.
- With
redisCache, entities are hydrated throughGetByIDs: each one is served from the context cache when already loaded, then from the Redis row cache, and otherwise loaded with a singleSELECT ... WHERE ID IN (...). - Without
redisCache, every row comes from the search query. A row whose entity is already in the context cache yields that cached instance (the freshly read values are discarded, as withGetByIDs); otherwise a new entity is built from the row.
In both cases every loaded entity is placed in the context cache, so two searches in one Context that return the same row give you the same pointer.
Tips
The parameters of a search are interpolated on the client, so each query is one round trip to MySQL. See Data Pools.
SearchManyWithTotal
func (p userEntityProvider) SearchManyWithTotal(ctx orm.Context, query *orm.DBQuery) ([]*entities.UserEntity, int, error)
Runs SELECT COUNT(*) with the query's WHERE first. When the count is 0 it returns nil, 0, nil without a second query; otherwise it runs the same query as SearchMany (with the pager) and returns the page plus the total.
users, total, err := entities.UserEntityProvider.SearchManyWithTotal(ctx,
orm.NewQuery().
Filter(entities.UserEntityProvider.Fields.Status.Is(enums.UserStatusList.Active)).
SortByDESC(entities.UserEntityProvider.Fields.CreatedAt).
Pager(orm.NewPager(1, 20)),
)
fmt.Printf("showing %d of %d users\n", len(users), total)
Count
func (p userEntityProvider) Count(ctx orm.Context, query *orm.DBQuery) (int, error)
Runs only SELECT COUNT(*) FROM <table> [WHERE ...]. Sort clauses and the pager are ignored.
active, err := entities.UserEntityProvider.Count(ctx,
orm.NewQuery().Filter(entities.UserEntityProvider.Fields.Status.Is(enums.UserStatusList.Active)),
)
SearchOne
func (p userEntityProvider) SearchOne(ctx orm.Context, query *orm.DBQuery) (*entities.UserEntity, error)
Runs the query with LIMIT 1 (honouring ORDER BY). With redisCache it selects the id and returns GetByID of it; without redisCache it selects the full row in that one query. A Pager on the query is ignored.
When no row matches, the entity is nil and the error is a *orm.NotFoundError (Entity is the table name, ID is 0). Detect it with errors.AsType:
user, err := entities.UserEntityProvider.SearchOne(ctx,
orm.NewQuery().
Filter(entities.UserEntityProvider.Fields.Email.Is("alice@example.com")),
)
if _, missing := errors.AsType[*orm.NotFoundError](err); missing {
fmt.Println("not found")
return nil
}
if err != nil {
return err
}
fmt.Println(user.GetName())
Cached unique index detection
When the entity declares CachedUniqueIndexes(), SearchOne inspects query.GetConditions() before building SQL. If the query has exactly as many typed conditions as an index has columns, and every one of them is an equality (Eq/Is) on exactly those columns, the lookup goes through the Redis unique-index key instead of a SELECT:
- Outside a transaction (
ctx.InTransaction() == false) the key<redisCachePrefix>u:<IndexName>@<hash>:<value hash>is read withGET. On a hit the id is loaded withGetByID, and the loaded row's column values (andFakeDelete == 0when present) are verified against the query before it is returned. - On a miss (or a failed verification)
SELECT ID ... WHERE <cols> = ? LIMIT 1runs, the id is written back to the key withorm.EntityCacheTTL, andGetByIDreturns the entity. - Inside
ctx.TransactionRedis is neither read nor written; theSELECTruns on the transaction.
Only GetConditions() participates in the detection: a condition qualifies when it implements orm.EqCondition (ColumnName(), EqValue()), which Eq, Is and IsEmpty() do and In, Like, comparisons and Not() do not. FilterWhere, sort clauses and WithFakeDeletes() are ignored on that path, and any non-equality condition (or an extra condition) falls back to the regular SELECT ... LIMIT 1. The key format and invalidation rules are described in Redis Cache.
// Uses the cached unique index "Email":
entities.UserEntityProvider.SearchOne(ctx, orm.NewQuery().
Filter(entities.UserEntityProvider.Fields.Email.Is("alice@example.com")))
// Regular SQL (Like is not an equality):
entities.UserEntityProvider.SearchOne(ctx, orm.NewQuery().
Filter(entities.UserEntityProvider.Fields.Email.Like("alice%")))
Fake-Deleted Rows
When the entity has a FakeDelete field, SearchOne, SearchMany, SearchManyWithTotal and Count append FakeDelete = 0 to the WHERE clause. To include fake-deleted rows, pass a Where created with WithFakeDeletes() through FilterWhere; DBQuery has no other switch for it:
users, err := entities.UserEntityProvider.SearchMany(ctx,
orm.NewQuery().
Filter(entities.UserEntityProvider.Fields.Status.Is(enums.UserStatusList.Active)).
FilterWhere(orm.NewWhere("1").WithFakeDeletes()),
)
See Fake Delete.
Transactions and Caches
All search SQL runs through ctx.DB(pool), which is the open transaction for that pool when the context is inside ctx.Transaction and a write has already opened one, otherwise the plain pool (see Transactions). Inside a transaction the Redis row cache and cached unique index keys are bypassed, so results reflect uncommitted writes of the same transaction.
Search never caches the list of ids; only the individual entities are cached (context cache and, with redisCache, the Redis row cache).
Reading ID Columns in Custom Queries
orm.ScanIDs reads a result set that has a single unsigned integer column. It is what the generated id queries use:
func ScanIDs(rows Rows, ids []uint64) ([]uint64, error)
It appends the column of every remaining row to ids and returns the result, or the error of rows.Scan / rows.Err().
rows, closeRows, err := ctx.DB(orm.DefaultPoolCode).Query(ctx, "SELECT `ID` FROM `UserEntity` WHERE `Age` >= ?", 18)
if err != nil {
return err
}
defer closeRows()
ids, err := orm.ScanIDs(rows, nil)
if err != nil {
return err
}
users, err := entities.UserEntityProvider.GetByIDs(ctx, ids...)
Loading by IDs
GetByIDs(ctx orm.Context, id ...uint64) ([]*entities.UserEntity, error) is what the search methods of an entity with redisCache use for hydration. It de-duplicates the ids, keeps the input order, serves context-cache hits first, then Redis row-cache hits (outside transactions), and loads the rest with a single SELECT ... WHERE ID IN (...). Ids that do not exist are silently dropped from the result (and, for entities with redisCache, negatively cached in Redis outside a transaction). Details are in CRUD.
Summary
| Method | Returns | Description |
|---|---|---|
SearchMany(ctx, query) | ([]*E, error) | Entities matching the query, SQL order, no LIMIT without a pager |
SearchManyWithTotal(ctx, query) | ([]*E, int, error) | COUNT(*) first, then the page; nil, 0, nil when the count is 0 |
SearchOne(ctx, query) | (*E, error) | LIMIT 1; *orm.NotFoundError when nothing matches; cached unique index fast path when the conditions match one |
Count(ctx, query) | (int, error) | COUNT(*) only |