How to filter queries

Filter query results using Python operators and named field methods. Compose conditions with & (AND), | (OR), and ~ (NOT) for arbitrary boolean logic.

This guide uses the Sales model from Your first query:

from semolina import SemanticView, Metric, Dimension


class Sales(SemanticView, view="sales"):
    revenue = Metric()
    cost = Metric()
    country = Dimension()
    region = Dimension()

Use comparison operators

Standard Python comparison operators work directly on fields:

Operator

Meaning

Example

==

Equals

Sales.country == "US"

!=

Not equals

Sales.country != "US"

>

Greater than

Sales.revenue > 1000

>=

Greater than or equal

Sales.revenue >= 500

<

Less than

Sales.revenue < 100

<=

Less than or equal

Sales.revenue <= 999

 
# Revenue greater than 1000
query = (
    Sales.query()
    .metrics(Sales.revenue)
    .where(Sales.revenue > 1000)
)

# Country equals US
query = (
    Sales.query()
    .metrics(Sales.revenue)
    .where(Sales.country == "US")
)

# Revenue between bounds (explicit)
query = (
    Sales.query()
    .metrics(Sales.revenue)
    .where((Sales.revenue >= 500) & (Sales.revenue <= 2000))
)

SELECT AGG("revenue")
FROM "sales"
WHERE "revenue" > 1000

SELECT MEASURE(`revenue`)
FROM `sales`
WHERE `revenue` > 1000

Use named filter methods

Fields provide named methods for common SQL operations beyond simple comparisons.

.between(lo, hi)

Range check (inclusive):

query = (
    Sales.query()
    .metrics(Sales.revenue)
    .where(Sales.revenue.between(500, 2000))
)

WHERE "revenue" BETWEEN 500 AND 2000

WHERE `revenue` BETWEEN 500 AND 2000

.in_(values)

Membership in a collection:

query = (
    Sales.query()
    .metrics(Sales.revenue)
    .where(Sales.country.in_(["US", "CA", "MX"]))
)

WHERE "country" IN ('US', 'CA', 'MX')

WHERE `country` IN ('US', 'CA', 'MX')

.isnull()

Null check:

# Find rows where region IS NULL
query = (
    Sales.query()
    .metrics(Sales.revenue)
    .where(Sales.region.isnull())
)

WHERE "region" IS NULL

WHERE `region` IS NULL

.like(pattern) and .ilike(pattern)

SQL LIKE pattern matching with % and _ wildcards. .ilike() is case-insensitive:

# Case-sensitive
query = (
    Sales.query()
    .metrics(Sales.revenue)
    .where(Sales.country.like("U%"))
)

# Case-insensitive
query = (
    Sales.query()
    .metrics(Sales.revenue)
    .where(Sales.country.ilike("u%"))
)

WHERE "country" LIKE 'U%'

WHERE `country` LIKE 'U%'

.startswith(prefix) and .istartswith(prefix)

Prefix match. .istartswith() is case-insensitive:

query = (
    Sales.query()
    .metrics(Sales.revenue)
    .where(Sales.country.startswith("U"))
)

WHERE "country" LIKE 'U%'

WHERE `country` LIKE 'U%'

.endswith(suffix) and .iendswith(suffix)

Suffix match. .iendswith() is case-insensitive:

query = (
    Sales.query()
    .metrics(Sales.revenue)
    .where(Sales.region.endswith("est"))
)

WHERE "region" LIKE '%est'

WHERE `region` LIKE '%est'

.iexact(value)

Case-insensitive equality (no wildcards):

query = (
    Sales.query()
    .metrics(Sales.revenue)
    .where(Sales.country.iexact("united states"))
)

WHERE "country" ILIKE 'united states'

WHERE `country` ILIKE 'united states'

Combine conditions with OR

Use | to combine two conditions with OR logic:

# country = 'US' OR country = 'CA'
condition = (Sales.country == "US") | (
    Sales.country == "CA"
)

cursor = (
    Sales.query()
    .metrics(Sales.revenue)
    .where(condition)
    .execute()
)

SELECT AGG("revenue")
FROM "sales"
WHERE ("country" = 'US' OR "country" = 'CA')

SELECT MEASURE(`revenue`)
FROM `sales`
WHERE (`country` = 'US' OR `country` = 'CA')

Combine conditions with AND

Use & to combine two conditions with AND logic:

# country = 'US' AND revenue > 500
condition = (Sales.country == "US") & (Sales.revenue > 500)

cursor = (
    Sales.query()
    .metrics(Sales.revenue)
    .where(condition)
    .execute()
)

WHERE ("country" = 'US' AND "revenue" > 500)

WHERE (`country` = 'US' AND `revenue` > 500)

Multiple .where() calls are also ANDed together:

# Equivalent to the & example above
cursor = (
    Sales.query()
    .metrics(Sales.revenue)
    .where(Sales.country == "US")
    .where(Sales.revenue > 500)
    .execute()
)

You can also pass multiple conditions as arguments to a single .where() call:

# Also equivalent -- varargs are ANDed together
cursor = (
    Sales.query()
    .metrics(Sales.revenue)
    .where(Sales.country == "US", Sales.revenue > 500)
    .execute()
)

Negate conditions with NOT

Use ~ to negate a condition:

# NOT (country = 'US')
condition = ~(Sales.country == "US")

cursor = (
    Sales.query()
    .metrics(Sales.revenue)
    .where(condition)
    .execute()
)

WHERE NOT ("country" = 'US')

WHERE NOT (`country` = 'US')

Negation composes with AND and OR:

# NOT (revenue < 100)
condition = ~(Sales.revenue < 100)

Build complex nested conditions

Combine |, &, and ~ to express arbitrary conditions. Use parentheses to control grouping:

# (country = 'US' OR country = 'CA') AND NOT (revenue < 100)
condition = (
    (Sales.country == "US") | (Sales.country == "CA")
) & ~(Sales.revenue < 100)

cursor = (
    Sales.query()
    .metrics(Sales.revenue)
    .where(condition)
    .execute()
)

WHERE (("country" = 'US' OR "country" = 'CA')
    AND NOT ("revenue" < 100))

WHERE ((`country` = 'US' OR `country` = 'CA')
    AND NOT (`revenue` < 100))

Build filters conditionally

Each .where() call ANDs with the accumulated filter. This is useful for conditionally building filters in application code:

query = (
    Sales.query()
    .metrics(Sales.revenue)
    .dimensions(Sales.country)
)

if region_filter:
    query = query.where(Sales.region == region_filter)

if min_revenue:
    query = query.where(Sales.revenue >= min_revenue)

cursor = query.execute()

.where() also accepts None as a no-op, making conditional filters a one-liner:

query = (
    Sales.query()
    .metrics(Sales.revenue)
    .dimensions(Sales.country)
    .where(
        Sales.region == region_filter
        if region_filter
        else None
    )
    .where(
        Sales.revenue >= min_revenue
        if min_revenue
        else None
    )
)

cursor = query.execute()

Use custom lookups

For filter operations not covered by the built-in operators or named methods, define a custom Lookup subclass and use .lookup():

from semolina.filters import Lookup


class RegexpMatch(Lookup[str]):
    """Regexp match: ``field REGEXP pattern``."""


# Use with .lookup()
query = (
    Sales.query()
    .metrics(Sales.revenue)
    .where(Sales.country.lookup(RegexpMatch, "^U.*S$"))
)

Custom lookups require a corresponding case branch in the SQL compiler to generate the correct SQL. This is an advanced extension point for users who need to add backend-specific filter operations.

Warning

Operator precedence: & binds tighter than |

Python evaluates & before | – the same precedence as bitwise operators. This can produce unexpected results when mixing them:

# DANGEROUS: reads as a | (b & c)
condition = (Sales.country == "US") | (
    Sales.revenue > 500
) & (Sales.cost < 100)

# SAFE: parentheses make intent explicit
condition = (
    (Sales.country == "US") | (Sales.revenue > 500)
) & (Sales.cost < 100)
condition = (Sales.country == "US") | (
    (Sales.revenue > 500) & (Sales.cost < 100)
)

Always use parentheses when mixing | and & in the same expression.

See also