Example

Search and Filter Examples

This is the shape you reach for when a page has half a dozen optional inputs: text search, facet filters, safe sort keys, and pagination that should not change the query into a string puzzle.

Directory search

type EmployeeSearch struct {
    TenantID   int
    Query      string
    Departments []string
    Status     *string
    Sort       string
    Page       int
    PerPage    int
}

func anySlice[T any](values []T) []any {
    out := make([]any, 0, len(values))
    for _, v := range values {
        out = append(out, v)
    }
    return out
}

func BuildEmployeeSearch(qq *quarry.Quarry, req EmployeeSearch) quarry.SQLer {
    return qq.Select(
        "id", "email", "department", "status", "created_at",
    ).
        From("employees").
        Where(
            quarry.Eq("tenant_id", req.TenantID),
            quarry.Or(
                quarry.OptionalILike("email", req.Query),
                quarry.OptionalILike("name", req.Query),
            ),
            quarry.OptionalEq("status", req.Status),
            quarry.OptionalIn("department", anySlice(req.Departments)...),
        ).
        OrderBySafeDefault(req.Sort, quarry.SortMap{
            "newest": "created_at DESC",
            "email":  "email ASC",
            "name":   "name ASC",
        }, "newest").
        Page(req.Page, req.PerPage)
}

Why this shape works Empty strings, nil pointers, and empty slices disappear. The query stays stable, and the SQL text only changes where the user actually supplied something.

Facet filters and ranges

type TicketSearch struct {
    TenantID   int
    Owners     []int
    CreatedMin  *time.Time
    CreatedMax  *time.Time
    Escalated  *bool
}

q := qq.Select("id", "subject", "owner_id", "created_at").
    From("tickets").
    Where(
        quarry.Eq("tenant_id", req.TenantID),
        quarry.OptionalIn("owner_id", anySlice(req.Owners)...),
        quarry.WhereIf(req.CreatedMin != nil && req.CreatedMax != nil,
            quarry.Between("created_at", *req.CreatedMin, *req.CreatedMax),
        ),
        quarry.OptionalEq("escalated", req.Escalated),
    )

SELECT id, subject, owner_id, created_at
FROM tickets
WHERE tenant_id = $1
  AND owner_id IN ($2, $3, $4)
  AND created_at BETWEEN $5 AND $6

Composite matching

When a search UI is really looking for a set of known combinations, TupleIn keeps the intent obvious.

q := qq.Select("account_id", "user_id", "role").
    From("account_memberships").
    Where(quarry.TupleIn([]any{
        quarry.C("account_id"),
        quarry.C("user_id"),
    }, [][]any{
        {42, 9},
        {42, 11},
        {42, 19},
    }))

Use it for sets, not everything Tuple predicates are handy when the combinations are known and finite. If the filter is really free-form, keep using the simpler optional predicates.

What the SQL looks like

SELECT id, email, department, status, created_at
FROM employees
WHERE tenant_id = $1
  AND (LOWER(email) LIKE LOWER($2) OR LOWER(name) LIKE LOWER($3))
  AND status = $4
ORDER BY created_at DESC
LIMIT 50 OFFSET 0

The important part is not the exact values. It is that each optional clause appears or disappears without changing the rest of the builder.