climate_ref.results._query

Filter and Select builders for metric-value queries.

This module is the single source of truth for how metric values are filtered and joined. Both the typed facade (climate_ref.results.values.Reader) and any power-user / API caller build on the select_* functions here, so the filter/join semantics live in exactly one place. This mirrors the existing house style of module-level query functions that take a Session (see climate_ref.models.execution.get_execution_group_and_latest_filtered).

The functions return a SQLAlchemy Select and never touch a Session -- callers add their own pagination, wrap in exists(), or hand them to the facade to materialise.

MetricValueFilter

Declarative filter over metric values.

Every field is optional; None/empty means "do not constrain on this axis". The same object drives scalar and series queries, frame conversion and facet collection, so a filter is written once and reused across output shapes.

Two styles of diagnostic/provider filtering are offered deliberately:

• diagnostic_slug / provider_slug are exact matches, for API path-scoped queries (/diagnostics/{provider_slug}/{diagnostic_slug}/values).
• diagnostic_contains / provider_contains are case-insensitive substring matches (OR-combined), for CLI-style search.

Keeping them separate avoids the Diagnostic.slug vs Diagnostic.name divergence that exists today between the CLI and the ref-app API.

Source code in packages/climate-ref/src/climate_ref/results/_query.py

@attrs.frozen(kw_only=True)
class MetricValueFilter:
    """
    Declarative filter over metric values.

    Every field is optional; ``None``/empty means "do not constrain on this axis". The same
    object drives scalar and series queries, frame conversion and facet collection, so a filter
    is written once and reused across output shapes.

    Two styles of diagnostic/provider filtering are offered deliberately:

    * ``diagnostic_slug`` / ``provider_slug`` are **exact** matches, for API path-scoped queries
      (``/diagnostics/{provider_slug}/{diagnostic_slug}/values``).
    * ``diagnostic_contains`` / ``provider_contains`` are case-insensitive **substring** matches
      (OR-combined), for CLI-style search.

    Keeping them separate avoids the ``Diagnostic.slug`` vs ``Diagnostic.name`` divergence that
    exists today between the CLI and the ref-app API.
    """

    execution_ids: Sequence[int] | None = None
    """Restrict to values produced by these executions."""

    execution_group_ids: Sequence[int] | None = None
    """Restrict to values produced by executions belonging to these groups."""

    diagnostic_slug: str | None = None
    """Exact-match diagnostic slug, for API path-scoped queries."""

    provider_slug: str | None = None
    """Exact-match provider slug, for API path-scoped queries."""

    diagnostic_contains: Sequence[str] | None = attrs.field(default=None, converter=_as_str_tuple)
    """Case-insensitive substring matches on diagnostic slug (OR-combined), for CLI-style search."""

    provider_contains: Sequence[str] | None = attrs.field(default=None, converter=_as_str_tuple)
    """Case-insensitive substring matches on provider slug (OR-combined), for CLI-style search."""

    dimensions: Mapping[str, str | Sequence[str]] | None = None
    """CV dimension filters keyed by registered dimension name; a string is equality, a sequence is IN."""

    isolate_ids: Sequence[int] | None = None
    """Restrict to exactly these value ids; takes precedence over ``exclude_ids``, matching the API."""

    exclude_ids: Sequence[int] | None = None
    """Exclude these value ids; ignored when ``isolate_ids`` is set."""

    reference_only: bool | None = None
    """Series-only: ``True`` for observation/reference series, ``False`` for model series."""

    promoted_only: bool = True
    """Restrict to execution groups at the diagnostic's currently promoted version."""

    include_retracted: bool = False
    """Include values produced by retracted executions."""

    def dimension_clauses(self, entity: type[MetricValue]) -> list[Any]:
        """
        Build validated SQLAlchemy clauses for the dynamic CV dimension columns.

        Raises
        ------
        KeyError
            If a key is not a registered CV dimension on ``entity``.
        """
        clauses: list[Any] = []
        for key, value in (self.dimensions or {}).items():
            if key not in entity._cv_dimensions:
                raise KeyError(f"Unknown dimension column {key!r}")
            col = getattr(entity, key)
            if isinstance(value, str) or not isinstance(value, Sequence):
                clauses.append(col == value)
            else:
                vals = list(value)
                clauses.append(col == vals[0] if len(vals) == 1 else col.in_(vals))
        return clauses

diagnostic_contains = attrs.field(default=None, converter=_as_str_tuple) class-attribute instance-attribute

Case-insensitive substring matches on diagnostic slug (OR-combined), for CLI-style search.

diagnostic_slug = None class-attribute instance-attribute

Exact-match diagnostic slug, for API path-scoped queries.

dimensions = None class-attribute instance-attribute

CV dimension filters keyed by registered dimension name; a string is equality, a sequence is IN.

exclude_ids = None class-attribute instance-attribute

Exclude these value ids; ignored when isolate_ids is set.

execution_group_ids = None class-attribute instance-attribute

Restrict to values produced by executions belonging to these groups.

execution_ids = None class-attribute instance-attribute

Restrict to values produced by these executions.

include_retracted = False class-attribute instance-attribute

Include values produced by retracted executions.

isolate_ids = None class-attribute instance-attribute

Restrict to exactly these value ids; takes precedence over exclude_ids, matching the API.

promoted_only = True class-attribute instance-attribute

Restrict to execution groups at the diagnostic's currently promoted version.

provider_contains = attrs.field(default=None, converter=_as_str_tuple) class-attribute instance-attribute

Case-insensitive substring matches on provider slug (OR-combined), for CLI-style search.

provider_slug = None class-attribute instance-attribute

Exact-match provider slug, for API path-scoped queries.

reference_only = None class-attribute instance-attribute

Series-only: True for observation/reference series, False for model series.

dimension_clauses(entity)

Build validated SQLAlchemy clauses for the dynamic CV dimension columns.

Raises:

Type	Description
KeyError	If a key is not a registered CV dimension on entity.

Source code in packages/climate-ref/src/climate_ref/results/_query.py

def dimension_clauses(self, entity: type[MetricValue]) -> list[Any]:
    """
    Build validated SQLAlchemy clauses for the dynamic CV dimension columns.

    Raises
    ------
    KeyError
        If a key is not a registered CV dimension on ``entity``.
    """
    clauses: list[Any] = []
    for key, value in (self.dimensions or {}).items():
        if key not in entity._cv_dimensions:
            raise KeyError(f"Unknown dimension column {key!r}")
        col = getattr(entity, key)
        if isinstance(value, str) or not isinstance(value, Sequence):
            clauses.append(col == value)
        else:
            vals = list(value)
            clauses.append(col == vals[0] if len(vals) == 1 else col.in_(vals))
    return clauses

count_values(session, stmt)

Total row count for a builder's Select, ignoring any offset/limit. For pagination.

Source code in packages/climate-ref/src/climate_ref/results/_query.py

def count_values(session: Session, stmt: Select[Any]) -> int:
    """Total row count for a builder's ``Select``, ignoring any offset/limit. For pagination."""
    return session.execute(select(func.count()).select_from(stmt.order_by(None).subquery())).scalar_one()

latest_execution_for_group(session, execution_group_id)

Return the most recent Execution for a group.

This is the read-side primitive behind the API's /executions/{group_id}/values behaviour of defaulting to the latest execution when no execution_id is supplied.

Source code in packages/climate-ref/src/climate_ref/results/_query.py

def latest_execution_for_group(session: Session, execution_group_id: int) -> Execution | None:
    """
    Return the most recent [Execution][climate_ref.models.execution.Execution] for a group.

    This is the read-side primitive behind the API's ``/executions/{group_id}/values`` behaviour
    of defaulting to the latest execution when no ``execution_id`` is supplied.
    """
    return session.execute(
        select(Execution)
        .where(Execution.execution_group_id == execution_group_id)
        .order_by(Execution.created_at.desc(), Execution.id.desc())
        .limit(1)
    ).scalar_one_or_none()

select_scalar_values(f=None)

Build a Select over ScalarMetricValue for the given filter.

Ordered by the value id ascending so SQL pagination is deterministic across pages.

Source code in packages/climate-ref/src/climate_ref/results/_query.py

def select_scalar_values(f: MetricValueFilter | None = None) -> Select[tuple[ScalarMetricValue]]:
    """
    Build a ``Select`` over ``ScalarMetricValue`` for the given filter.

    Ordered by the value id ascending so SQL pagination is deterministic across pages.
    """
    f = f or MetricValueFilter()
    stmt = _apply_common(select(ScalarMetricValue), ScalarMetricValue, f)
    return stmt.order_by(ScalarMetricValue.id)

select_series_values(f=None)

Build a Select over SeriesMetricValue.

The shared index axis is eager-loaded via the model relationship (lazy="joined"), so .index / .index_name are safe to read for the returned rows. Ordered by the value id ascending so SQL pagination is deterministic across pages.

Source code in packages/climate-ref/src/climate_ref/results/_query.py

def select_series_values(f: MetricValueFilter | None = None) -> Select[tuple[SeriesMetricValue]]:
    """
    Build a ``Select`` over ``SeriesMetricValue``.

    The shared index axis is eager-loaded via the model relationship (``lazy="joined"``), so
    ``.index`` / ``.index_name`` are safe to read for the returned rows.
    Ordered by the value id ascending so SQL pagination is deterministic across pages.
    """
    f = f or MetricValueFilter()
    stmt = _apply_common(select(SeriesMetricValue), SeriesMetricValue, f)
    if f.reference_only is True:
        stmt = stmt.where(SeriesMetricValue.reference_id.is_not(None))
    elif f.reference_only is False:
        stmt = stmt.where(SeriesMetricValue.reference_id.is_(None))
    return stmt.order_by(SeriesMetricValue.id)