Compose filters with AND and OR

What this builds. A vendor scorecard with mixed-logic filters — “European customers with priority above 2 or status closed”, “vendors in AS or with priority above 5” — using nested FilterGroups instead of writing the boolean logic by hand. You’ll end up with. A working pattern for three increasingly expressive filter shapes plus an order_by / paginate recipe you can drop straight into a real query.

The mental model

A filter is groups of conditions:

Conditions inside one FilterGroup share an operator — AND (default) or OR.
The top-level CompoundFilter joins those groups together with its own operator — also AND (default) or OR.

So (region == "EU") AND (priority > 2 OR status == "closed") is naturally two groups: one with the region condition, one with the priority/status conditions joined by OR. The compound filter joins those two groups with AND. When the whole filter is a single AND or single OR, use CompoundFilter.of(...) — it wraps your conditions in a single group for you.

Setup

"""Compose AND/OR filters on a vendor scorecard table."""

from vectorshift.table import (
    ColumnSpec,
    CompoundFilter,
    FilterCondition,
    FilterGroup,
    NumberFormat,
    NumberKind,
    RelationalOperator,
    StringFormat,
    Table,
    TableFilterOperator,
)


def vendors(page) -> set[str]:
    return {r["values"]["vendor"] for r in page["rows"] if r["values"].get("vendor")}


t = Table.new(
    name="Vendor scorecard — filter demo",
    columns=[
        ColumnSpec(name="vendor", format=StringFormat()),
        ColumnSpec(name="region", format=StringFormat()),
        ColumnSpec(name="status", format=StringFormat()),
        ColumnSpec(name="priority", format=NumberFormat(number_kind=NumberKind.INT)),
    ],
)
t.clear()
t.insert_rows([
    {"vendor": "Acme",    "region": "EU", "status": "open",   "priority": 5},
    {"vendor": "Beta",    "region": "EU", "status": "open",   "priority": 1},
    {"vendor": "Gamma",   "region": "EU", "status": "closed", "priority": 3},
    {"vendor": "Delta",   "region": "US", "status": "open",   "priority": 9},
    {"vendor": "Echo",    "region": "US", "status": "closed", "priority": 2},
    {"vendor": "Foxtrot", "region": "AS", "status": "open",   "priority": 1},
])

Recipe 1 — all conditions must match (AND inside a group)

“European vendors that are still open.” Conditions inside a single group default to AND, so CompoundFilter.of(...) is enough:

eu_open = t.read_rows(filters=CompoundFilter.of(
    FilterCondition("region", TableFilterOperator.EQ, "EU"),
    FilterCondition("status", TableFilterOperator.EQ, "open"),
))
print("EU AND open ->", vendors(eu_open))
# {'Acme', 'Beta'}

Recipe 2 — any condition matches (OR inside a group)

“Vendors in EU or US.” Same single-group shape, but flip the group operator to OR:

eu_or_us = t.read_rows(filters=CompoundFilter.of(
    FilterCondition("region", TableFilterOperator.EQ, "EU"),
    FilterCondition("region", TableFilterOperator.EQ, "US"),
    logical_operator=RelationalOperator.OR,
))
print("EU OR US ->", vendors(eu_or_us))
# {'Acme', 'Beta', 'Gamma', 'Delta', 'Echo'}

For a flat IS-IN like this you could also use TableFilterOperator.IS_IN — but the OR shape generalises when the two conditions are on different columns.

Recipe 3 — mix AND and OR with two groups

“European vendors that are either high-priority or already closed.” Two clauses, with OR inside one and AND between groups:

filt = CompoundFilter(
    groups=[
        FilterGroup(conditions=[
            FilterCondition("region", TableFilterOperator.EQ, "EU"),
        ]),
        FilterGroup(
            conditions=[
                FilterCondition("priority", TableFilterOperator.GT, 2),
                FilterCondition("status",   TableFilterOperator.EQ, "closed"),
            ],
            logical_operator=RelationalOperator.OR,
        ),
    ],
    group_logical_operator=RelationalOperator.AND,
)
print("EU AND (priority>2 OR status=closed) ->", vendors(t.read_rows(filters=filt)))
# {'Acme', 'Gamma'}

Order and paginate the result

order_by and paginate chain onto any CompoundFilter. They return a new filter (the dataclass is frozen), so call them on the result of .of(...) or the constructor:

# Top 3 vendors by priority, descending.
top3 = t.read_rows(filters=(
    CompoundFilter()
    .order_by("priority", desc=True)
    .paginate(limit=3, offset=0)
))
print([r["values"]["vendor"] for r in top3["rows"]])
# ['Delta', 'Acme', 'Gamma']

# Next page of 3.
next3 = t.read_rows(filters=(
    CompoundFilter()
    .order_by("priority", desc=True)
    .paginate(limit=3, offset=3)
))
print([r["values"]["vendor"] for r in next3["rows"]])
# ['Echo', 'Beta', 'Foxtrot']

order_by is chainable for multi-key sorting — call it once per key, in priority order:

sorted_filt = (
    CompoundFilter()
    .order_by("region", desc=False)
    .order_by("priority", desc=True)
)

The first key wins ties; the second breaks them. Same filter can be used to drive a scroll(...) if you have more rows than fit in one read_rows page.

Cleanup

t.delete()

read_rows is one page (~1,000 rows by default). For larger tables iterate with scroll, passing the same CompoundFilter — scroll pages through every matching row for you.

Common filter recipes

One snippet per TableFilterOperator, grouped by use case.

CompoundFilter reference

Top-level shape and builder methods.

Rows and filters

Filters in context with insert / update / delete / scroll.

Get started

Guides

Pipeline

Agent

Knowledge Base

Integrations

Table

Transformation

Session

Analytics

Workspace

Compose filters with AND and OR

The mental model

Setup

Recipe 1 — all conditions must match (AND inside a group)

Recipe 2 — any condition matches (OR inside a group)

Recipe 3 — mix AND and OR with two groups

Order and paginate the result

Cleanup

See also

Common filter recipes

CompoundFilter reference

Rows and filters

​The mental model

​Setup

​Recipe 1 — all conditions must match (AND inside a group)

​Recipe 2 — any condition matches (OR inside a group)

​Recipe 3 — mix AND and OR with two groups

​Order and paginate the result

​Cleanup

​See also

Common filter recipes

CompoundFilter reference

Rows and filters

The mental model

Setup

Recipe 1 — all conditions must match (AND inside a group)

Recipe 2 — any condition matches (OR inside a group)

Recipe 3 — mix AND and OR with two groups

Order and paginate the result

Cleanup

See also