Rai Querying

Querying

For define() / computed properties, see rai-pyrel-coding. For solver formulation, see rai-prescriptive-problem-formulation.

---

Before you write any query

A few seconds of grounding prevents the most common multi-turn failure loops:

1. Match your task to a known shape — the [Recipe Card](#recipe-card) below covers the common 80%; the [Examples](#examples) table points to fuller patterns.
2. Confirm the surface against the real model — run inspect.schema(model) to verify concept and property names and types (see [inspect-module.md](references/inspect-module.md)). Your recall of a model's surface drifts; check it rather than guessing.
3. For a relationship with multiple fields (one relationship packing several positional typed fields), run inspect.fields(Concept.relationship) first — the prose words in the reading string are not field names. Then access them per [joins-and-export.md § Multi-Argument Relationships](references/joins-and-export.md#multi-argument-relationships) (labeled value fields by name; unlabeled by integer position; entity links via raw source columns).

Then write and execute — don't guess syntax from memory.

---

Recipe Card

The 80% of queries fit one of these shapes. Copy, adapt the concept names, alias every column.

from relationalai.semantics import distinct
from relationalai.semantics.std import aggregates as aggs
from relationalai.semantics.std.aggregates import string_join, rank, desc, asc, top, bottom

Basic select + filter:

model.where(Order.status == "active").select(
    Order.id.alias("order_id"),
    Order.total.alias("total"),
).to_df()

Grouped aggregation by entity:

model.where(Order.placed_by(Customer)).select(
    Customer.name.alias("customer"),
    aggs.sum(Order.total).per(Customer).alias("revenue"),
).to_df()

Grouped aggregation by property value (REQUIRES distinct()):

# Without distinct, you get one row per Item with duplicated counts.
model.select(
    distinct(
        Item.category.alias("category"),
        aggs.count(Item).per(Item.category).alias("count"),
        aggs.sum(Item.value).per(Item.category).alias("total"),
    )
).to_df()

Multi-key property-value grouping (also REQUIRES distinct()):

model.select(
    distinct(
        RevenueForecast.region.alias("region"),
        RevenueForecast.forecast_month.alias("month"),
        aggs.sum(RevenueForecast.actual).per(
            RevenueForecast.region, RevenueForecast.forecast_month
        ).alias("actual"),
    )
).to_df()

Multi-hop join (linear path, each concept once):

# Bare concept references work — they are bound by the where() applications
# and resolved consistently across select(). Use .ref() only when a concept
# appears twice (self-join) or in independent aggregation contexts.
model.where(
    LineItem.part_of_order(Order),
    Order.placed_by(Customer),
    Customer.located_in(Country),
    LineItem.extended_price > 1000.0,
).select(
    LineItem.id.alias("line_item_id"),
    Customer.name.alias("customer"),
    Country.name.alias("country"),
).to_df()

Ratio of two aggregates:

# Both sums must group by the same key. distinct() collapses to one row per group.
model.select(
    distinct(
        Item.group.alias("group"),
        (aggs.sum(Item.numerator).per(Item.group)
         / aggs.sum(Item.denominator).per(Item.group)
        ).alias("ratio"),
    )
).to_df()

HAVING (filter on an aggregated value):

# Bind the aggregate in where() with :=, then filter on it.
model.where(
    Customer.placed_order(Order),
    Order.ordered_at_location(Store),
    revenue := aggs.sum(Order.total).per(Store),
    revenue > 10000,
).select(
    Store.name.alias("store"),
    revenue.alias("revenue"),
).to_df()

Top-N (native — no pandas):

# top(N, expr) in where() keeps the N highest rows; add .per(Group) for top-N per group.
model.where(
    FailurePrediction.period == 12,
    top(5, FailurePrediction.failure_probability),
).select(
    FailurePrediction.machine_id.alias("machine_id"),
    FailurePrediction.failure_probability.alias("p"),
).to_df()
# Need the rank value as a column, or the inverse? rank(desc(expr)) / bottom(N, expr) —
# see references/aggregation-advanced.md (ranking aggregates).

---

Silent Corruptions — Read First

These produce wrong results without errors. They are responsible for most "the numbers look right but they're wrong" bugs.

1. Missing .alias() triggers silent _2 / _3 column suffixes

When two concepts in a select() share a property name (.name, .id), to_df() silently appends _2 to disambiguate. Downstream code expecting the original name gets a KeyError or, worse, reads the wrong column.

# WRONG — both Business and Site have .name. Output columns are
#   name, name_2, count   (not supplier, destination, count)
model.where(Shipment.supplier(Business), Shipment.destination(Site)).select(
    Business.name,
    Site.name,
    aggs.count(Shipment).per(Business).alias("count"),
).to_df()

# CORRECT — explicit .alias() on every property guarantees the column name.
model.where(Shipment.supplier(Business), Shipment.destination(Site)).select(
    Business.name.alias("supplier"),
    Site.name.alias("destination"),
    aggs.count(Shipment).per(Business).alias("count"),
).to_df()

Rule: alias every column in every multi-concept query. Cost is one line; savings is silent column corruption.

2. Property-value grouping without distinct() returns N rows, not one per group

Grouping by an entity is unique by definition. Grouping by a property value (a string, a date, a status) is not — without distinct() you get one row per source entity with the aggregate repeated.

# WRONG — returns one row per Item, each carrying the same per-category total.
model.select(
    Item.category.alias("category"),
    aggs.sum(Item.value).per(Item.category).alias("total"),
).to_df()

# CORRECT — distinct() collapses to one row per category.
model.select(distinct(
    Item.category.alias("category"),
    aggs.sum(Item.value).per(Item.category).alias("total"),
)).to_df()

Decision rule:

• Group by entity (.per(Customer), .per(Machine, Product)): distinct() usually unnecessary.
• Group by property value (.per(Customer.region)): distinct() required.
• Group by mix (.per(Customer, Customer.region)): treat as property-value — use distinct().

3. Dot-chains in select drop where-bindings

A dot-chain like Employee.uses.id compiles as a fresh independent lookup of uses — it does NOT pick up filters established by Employee.uses(Asset) in a sibling where().

# WRONG — returns id of every Asset that any Employee uses, not just asset 102.
model.select(Employee.nr, Employee.uses.id).where(
    Employee.uses(Asset),
    Asset.id == 102,
).to_df()

# CORRECT (option A) — chain through the relationship application:
model.select(Employee.nr, Employee.uses(Asset).id).where(
    Employee.uses(Asset),
    Asset.id == 102,
).to_df()

# CORRECT (option B) — reference the bound concept directly:
model.select(Employee.nr, Asset.id).where(
    Employee.uses(Asset),
    Asset.id == 102,
).to_df()

Rule: avoid bare dot-chains in select(). Either reference the bound concept directly (Asset.id) or route through the relationship application (Employee.uses(Asset).id).

4. Multi-relationship select inflates aggregations via cartesian product

Binding two relationships through the same concept in one select creates a cartesian product of matching pairs. Aggregations over this scope silently double- or triple-count.

# WRONG — pairs every shipment-from-supplier with every shipment-to-destination
# through the shared Site, multiplying counts.
model.where(Shipment.supplier(Site), Shipment.destination(Site)).select(
    aggs.count(Shipment).alias("n"),
).to_df()

# CORRECT — split into separate queries, or pre-aggregate via define()d properties.

When a query touches multiple relationships through a shared concept and the numbers feel high, suspect this first.

5. .per(FK_property) cartesian-inflates when the bare Concept is bound elsewhere

A Concept-typed FK property (Order.customer) introduces an anonymous Customer iterator, separate from the bare Customer bound by where(Order.customer(Customer)). Aggregating with .per(Order.customer) while selecting Customer.name cartesian-multiplies rows.

# WRONG — .per(Order.customer) doesn't unify with bound Customer in select.
model.where(Order.customer(Customer)).select(
    Customer.name.alias("customer"),
    aggs.sum(Order.amount).per(Order.customer).alias("total"),
).to_df()

# CORRECT — bare Concept as both select and .per() key.
model.where(Order.customer(Customer)).select(
    Customer.name.alias("customer"),
    aggs.sum(Order.amount).per(Customer).alias("total"),
).to_df()

Looks like missing distinct() (corruption #2) but distinct() won't fix it.

6. String-equality filter on a value the data doesn't have → empty result, no error

The user's question is phrased in their vocabulary; the data uses whatever spelling and casing the source system stored. Order.status == "Active" returns zero rows if the column actually holds "ACTIVE" or "active_orders". Stack two or three of these silent mismatches in one query and the join collapses to nothing, with no error — the agent then assumes the join itself is broken and divide-and-conquers for many turns.

Discover actual values before filtering on them:

# One-line discovery query for any property you'll filter on:
model.select(distinct(Order.status)).to_df()
# Then filter with the exact spelling/casing you saw.

For partial / informal names from the question, use substring match instead of ==:

from relationalai.semantics.std import strings

# User refers to a company informally; data has the full registered name.
model.where(strings.contains(Customer.legal_name, "Acme")).select(...)

# Also available in the same module: startswith, endswith, like

Rule: every == against a string literal that came from a natural-language question is a discovery opportunity. Run distinct() on the property first, or fall back to strings.contains().

7. Joining a one-to-many relationship fans out — collapse to the grain before ranking

When an entity links to many rows of another concept — a record per period, per version, per event — binding that relationship multiplies the entity across all its matches. Rank or limit over the fanned-out scope and duplicate rows crowd out distinct entities; any per-entity metric is also computed against the wrong row.

# WRONG — each Supplier fans out to one row per forecast period, so "top 5 by
# value" repeats the same supplier and drops genuinely distinct ones.
model.where(Supplier.forecast(F)).select(
    Supplier.id.alias("id"), F.value.alias("v"),
).to_df()

# CORRECT — fix the grain first: filter to the single intended row...
model.where(Supplier.forecast(F), F.period == latest_period).select(...)
# ...or aggregate the many-side to one value per entity (aggs.max, latest-by, etc.).

Rule: when you join to a relationship that can return more than one row per entity, decide the grain — one row (which one?) or an aggregate — before you rank, limit, or compute a per-entity metric. When you expect one row per entity, verify row_count == entity_count.

---

Query Basics

model.where(conditions).select(expressions).to_df() is the executable form.

Default reflex: compose results in a single model.select(...), not multiple to_df() calls merged in pandas. Express grouping, joining, filtering, and aggregation inline (aggs.(...).per().where(...).alias(...)). Reach for pandas only when the consumer needs DataFrame arithmetic the ontology can't express. Multiple .to_df() + .merge() re-derives joins the ontology already defines.

Use model.where() / model.select() over the standalone functions. The standalone forms only work when exactly one Model exists in the process; with multiple they raise "Multiple Models have been defined.". The model-method form is portable.

.to_df() — execute and return a pandas DataFrame.

distinct(...) — deduplicate rows. All columns in a select() must be either ALL inside distinct() or ALL outside; mixing raises a runtime error. For multi-column distinct() over joined concepts (dedup without aggregation), see [distinct-patterns.md](references/distinct-patterns.md).

Set membership and negation:

model.where(LineItem.ship_mode.in_(["AIR", "AIR REG"])).select(...)

# Entities WITHOUT a relationship — bind the concept with a ref so you can
# both negate against it and surface it in select().
model.where(
    order := Order.ref(),
    model.not_(order.customer),
).select(order.id.alias("orphan_order_id"))

# NOT (A AND B) — together inside one not_()
model.not_(Person.pets, Person.pets.name == "boots")
# (NOT A) AND (NOT B) — separate not_() calls
model.not_(Person.pets), model.not_(Person.pets.name == "boots")

model.union() vs |: model.union() collects ALL matching branches (set union / OR-filter). | evaluates left-to-right and picks the first that succeeds (case-when / default). Use | for fallbacks; union() for OR-filtering.

For extended not_() examples and OR-filter patterns, see [filtering-advanced.md](references/filtering-advanced.md).

---

Aggregation Patterns

Value aggregates: count, sum, min, max, avg, product, stddev_samp, string_join. avg, product, and stddev_samp are query-only — they raise NotImplementedError in solver satisfy/minimize/maximize contexts. Solver-supported aggregates: sum, min, max, count. product and stddev_samp are both relationalai>=1.12 and always return Float — product is an exp-log approximation (not an exact product) and stddev_samp is the sample (n-1) estimator; see [product](references/aggregation-advanced.md#product) and [stddevsamp](references/aggregation-advanced.md#stddevsamp).

For ranking and top-N — top / bottom / limit / rank, evaluated in-query (no pandas) — see [ranking aggregates](references/aggregation-advanced.md#ranking-aggregates).

.per(K) declares the dimensions of the result — one row per distinct value of K. Use the same concept variables that appear in your select(). Given Shipment.supplier(Supplier) in where(), write .per(Supplier), not .per(Shipment.supplier) — the bare concept names an entity cleanly, while a relationship application carries its source concept (Shipment) into the implicit key and over-groups.

# Single entity grouping (Supplier bound via Shipment.supplier(Supplier) in where)
aggs.count(Shipment).per(Supplier).alias("shipments")

# Multi-key
aggs.sum(Shipment.quantity).per(Supplier, Destination).alias("qty")

# Arithmetic inside aggregate (NOT the same as ratio of two aggregates)
aggs.sum(Shipment.quantity * Shipment.delay_days).per(Supplier).alias("qty_delay")

# Filter the aggregation scope
aggs.count(Shipment).per(Supplier).where(Shipment.is_delayed()).alias("delayed")

Empty aggregations return no row, not zero. Use | 0 for missing groups: aggs.count(Shipment).where(...).alias("n") | 0.

Integer aggregate division. aggs.count(X) is integer; aggs.sum(X) matches input. Dividing two integers hits integer-division semantics — multiply one operand by 1.0 (or 100.0 for a percentage) to get a float ratio.

For count(X, condition), conditional .where() on aggregates, and per(X).sum(Y) standalone form, see [aggregation-advanced.md](references/aggregation-advanced.md).

---

Multi-Concept Joins

Join concepts by relationship application in where(). Use

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: RelationalAI
• Source: RelationalAI/rai-agent-skills
• License: Apache-2.0
• Homepage: https://relational.ai

Install and usage instructions live in the source repository linked above.