fn list_for_filters

The list_for_filters function provides a unified interface for querying entities with optional filtering and flexible sorting. It uses a struct-based API where each filter field is optional, allowing filtering by N columns simultaneously — making it ideal for UI table filtering use cases.

The function accepts:

1. A filters struct with Option<T> fields (e.g., UserFilters { name: Some("Alice".into()), ..Default::default() })
2. A sort specification with direction
3. Pagination arguments

When a filter field is None, that column is not filtered. When Some(value), only rows matching that value are returned.

How It Works

For each entity with list_for columns, the macro generates:

A Filters Struct

A #[derive(Debug, Default)] struct with one Option<T> field per list_for column:

#[derive(Debug, Default)]
pub struct UserDocumentFilters {
    pub user_id: Option<UserId>,
    pub status: Option<DocumentStatus>,
}

Use Default::default() for no filtering, or set specific fields:

// No filters - returns all entities
let filters = UserDocumentFilters::default();

// Filter by user_id only
let filters = UserDocumentFilters {
    user_id: Some(owner_id),
    ..Default::default()
};

// Filter by both user_id and status
let filters = UserDocumentFilters {
    user_id: Some(owner_id),
    status: Some(DocumentStatus::Active),
};

Per-Sort-Column Functions

For each list_by column, a list_for_filters_by_{sort_col} function is generated. Instead of one catch-all query, it contains one static SQL query per filter combination × cursor state and dispatches at runtime on which filters are Some. Present filters compile to plain, index-friendly (sargable) predicates; absent filters are omitted entirely:

-- user_id = Some(..), status = None, first page
SELECT id FROM user_documents
  WHERE user_id = $1
  ORDER BY id ASC LIMIT $2

-- both filters set, paginating from a cursor
SELECT id FROM user_documents
  WHERE user_id = $1 AND status = $2 AND (id > $4)
  ORDER BY id ASC LIMIT $3

This matters for performance: the planner can turn col = $k into an index condition, which is impossible through a COALESCE(col = $k, $k IS NULL) catch-all (a single generic plan must serve both NULL and non-NULL parameters, so the predicate never becomes an index qual and every call full-scans the table).

For entities with more than 4 list_for columns the combination matrix is capped: only the no-filter, single-filter, and all-filters combinations get specialized queries, and remaining combinations fall back to the catch-all COALESCE-based SQL (correct, just not sargable):

SELECT id FROM user_documents
  WHERE COALESCE(user_id = $1, $1 IS NULL)
    AND COALESCE(status = $2, $2 IS NULL)
    AND (COALESCE(id > $4, true))
  ORDER BY id ASC LIMIT $3

Specialization is driven by your physical indexes

A multi-filter combination gets a specialized sargable query only when a matching composite index physically exists — because sargable and catch-all SQL differ only when there is an index to ride. The derive discovers your indexes by parsing the committed migration .sql files at compile time (the migrations directory is the source of truth, exactly as sqlx::migrate! reads it), so there is nothing to declare and no per-repo flag: a combination filtering on the (order-insensitive) columns C and paginating by sort column S is specialized iff some index’s leading key columns are a permutation of C immediately followed by S. Everything else uses the catch-all COALESCE query above (correct, just not sargable). Adding a CREATE INDEX migration is the single, PR-visible way to expand the specialized surface — build cost tracks your real indexes, never 2^N.

For scoped repos each scope column is auto-prefixed: each column’s dispatch arm looks for (scope_col, C…, S) and the unscoped (All) arm for (C…, S), matched independently against the catalog per dimension — a repo may have partner-led composite indexes but no customer-led ones, in which case the partner arm specializes while the customer arm falls back.

Single-filter (list_for_{col}_by_{sort}) and no-filter (list_by_{sort}) queries, and all cursor pagination, are always emitted as a single unified query regardless of the catalog — sargable when the matching (…, id) index exists, harmlessly equivalent when it does not.

Resolving the migrations directory. By default the derive reads $CARGO_MANIFEST_DIR/migrations. When your migrations live in a different crate (e.g. a core/* crate whose migrations are in ../../app/migrations), point the derive at them from a build.rs:

// build.rs
fn main() {
    let dir = std::path::Path::new(env!("CARGO_MANIFEST_DIR")).join("../../app/migrations");
    let dir = std::fs::canonicalize(&dir).unwrap();
    println!("cargo:rustc-env=ES_ENTITY_MIGRATIONS_DIR={}", dir.display());
    println!("cargo:rerun-if-changed={}", dir.display());
}

The ES_ENTITY_MIGRATIONS_DIR env var (or a per-repo #[es_repo(migrations_dir = "…")] override) takes precedence over the default. With no directory resolved, the catalog is empty and every multi-filter combination falls back to the COALESCE query.

Rebuild invalidation. The derive include_bytes!s the resolved migration files, so editing a migration re-runs it automatically — even for an auto-discovered ancestor migrations/. Picking up a newly added migration needs a rebuild trigger: the build.rs rerun-if-changed above (which also sets ES_ENTITY_MIGRATIONS_DIR, and then supersedes the per-file include_bytes!), or a cargo clean.

A Dispatch Function

The list_for_filters function matches on the sort column and intelligently delegates to the most efficient underlying function:

• No filters set (Filters::default()): proxies to list_by_{sort} (simple query, full index usage)
• Exactly one filter set: proxies to list_for_{col}_by_{sort} (single-column WHERE, full index usage)
• Two or more filters set: uses the per-sort specialized query matching the exact filter combination when a composite index backs it (sargable), falling back to the COALESCE-based SQL for un-indexed combinations

Important Notes

Cursor and Sort Alignment: The cursor type in PaginatedQueryArgs must match the sort field specified in the Sort parameter.

Column Options: Filter fields are generated for columns with the list_for option. Sort options are generated for columns with list_by (ID and created_at are included by default).

Virtual filters

A columns(...) entry may declare a SQL predicate instead of a physical column with virtual = "<sql>" plus bare list_for:

#[derive(EsRepo)]
#[es_repo(
    entity = "Order",
    columns(
        status(ty = "OrderStatus", list_for),
        has_open_ticket(
            ty = "bool",
            virtual = "EXISTS (SELECT 1 FROM support_tickets t
                       WHERE t.order_id = orders.id
                         AND t.status = 'open')",
            list_for
        ),
    ),
)]
pub struct OrderRepo { /* .. */ }

This is for filtering an entity by a fact that lives on another table — composing that fact into the ordinary list_for_filters query (same sort, same cursor, same scope) instead of hand-writing a bespoke query outside the repo. Nothing else about the entity, the DB table, or any other generated fn changes.

ty picks one of two kinds:

Polarity virtuals (ty = "bool")

The filters struct gains a matching Option<bool> field with three states:

• None — no conjunct is added; the predicate plays no part in the query.
• Some(true) — the predicate is added as AND (<sql>).
• Some(false) — the predicate is added as AND NOT (<sql>).

The predicate is opaque, unparameterized SQL spliced verbatim into the generated queries (still validated against your schema by sqlx at compile time) — it binds no query parameters of its own.

Value virtuals (any other ty)

Any ty other than bool is a value virtual: the predicate must contain the placeholder {value}, which the macro rewrites to a bound $k query parameter — the filter value is genuinely bound, not interpolated into the SQL text:

#[derive(EsRepo)]
#[es_repo(
    entity = "Order",
    columns(
        status(ty = "OrderStatus", list_for),
        min_open_tickets(
            ty = "i64",
            virtual = "(SELECT COUNT(*) FROM support_tickets t
                        WHERE t.order_id = orders.id
                          AND t.status = 'open') >= {value}",
            list_for
        ),
    ),
)]
pub struct OrderRepo { /* .. */ }

The filters struct gains min_open_tickets: Option<i64>: None adds no conjunct; Some(3) adds AND ((SELECT COUNT(*) ... ) >= $k) with 3 bound at $k through the same path a physical list_for column’s value is bound — any type that already works as a physical list_for column (including a custom entity_id! type) works here too.

{value} rules:

• Every occurrence of {value} in the predicate rewrites to the same $k — one column, one value.
• {{ and }} are escapes for a literal { / }, mirroring format!.
• {value} is not rewritten inside a single-quoted SQL string literal.
• The token is purely positional; a Postgres-side cast is your own SQL — {value}::int4.
• A value virtual has no NOT form and no NULL-filtering — negation and Option<Option<T>> belong in your own SQL/schema, not this mechanism.

Common to both kinds

• The predicate must reference the outer row by the repo’s full table name — the generated queries select FROM {table} with no alias, so orders.id (not a bare id, which would ambiguously resolve inside a correlated subquery) is what’s in scope.
• A virtual column never gets a per-column list_for_{name}_by_{sort} fn — it only ever participates in the unified list_for_filters* path. As soon as any virtual filter is set, dispatch always goes through that path, even for a repo with no physical list_for columns at all.
• It never affects index-catalog specialization: it is not an equality column, so it rides along as an extra conjunct on whichever arm (specialized or catch-all) the physical filters already selected.
• Build cost: each polarity virtual triples the static-query matrix for its list_for_filters_by_{sort} fns (one arm per None / Some(true) / Some(false)); each value virtual doubles it (one arm per None / Some(v)) — fine for the handful of virtual columns a repo is ever expected to declare, but keep that build-cost multiplier in mind before adding several.

Example

extern crate es_entity;
extern crate sqlx;
extern crate serde;
extern crate tokio;
extern crate anyhow;
extern crate uuid;
use serde::{Deserialize, Serialize};
es_entity::entity_id! { UserId }
#[derive(EsEvent, Debug, Serialize, Deserialize)]
#[serde(tag = "type", rename_all = "snake_case")]
#[es_event(id = "UserId")]
pub enum UserEvent {
    Initialized { id: UserId, name: String },
    NameUpdated { name: String },
}
impl IntoEvents<UserEvent> for NewUser {
    type IdSource = FromRepo;
    fn into_events(self, builder: EntityEventsBuilder<UserEvent, FromRepo>) -> EntityEvents<UserEvent> {
        let id = builder.id();
        builder.initial_events([UserEvent::Initialized { id, name: self.name }])
    }
}
impl TryFromEvents<UserEvent> for User {
    fn try_from_events(events: EntityEvents<UserEvent>) -> Result<Self, EntityHydrationError> {
        let mut name = String::new();
        for event in events.iter_all() {
            match event {
                UserEvent::Initialized { name: n, .. } => { name = n.clone(); }
                UserEvent::NameUpdated { name: n } => { name = n.clone(); }
            }
        }
        Ok(User { id: events.id().clone(), name, events })
    }
}
pub struct NewUser { name: String }
#[derive(EsEntity)]
pub struct User {
    pub id: UserId,
    pub name: String,
    events: EntityEvents<UserEvent>,
}
use es_entity::*;

#[derive(EsRepo)]
#[es_repo(
    entity = "User",
    columns(
        name(ty = "String", list_for(by(id, created_at)))
    )
)]
pub struct Users {
    pool: sqlx::PgPool
}
async fn init_pool() -> anyhow::Result<sqlx::PgPool> {
    let pg_con = std::env::var("PG_CON").unwrap_or_else(|_| "postgres://user:password@localhost:5432/pg".to_string());
    Ok(sqlx::PgPool::connect(&pg_con).await?)
}

#[tokio::main]
async fn main() -> anyhow::Result<()> {
    let users = Users { pool: init_pool().await? };

    // No filters - returns all users, sorted by ID
    let all_users = users.list_for_filters(
        UserFilters::default(),
        Sort {
            by: UserSortBy::Id,
            direction: ListDirection::Ascending,
        },
        PaginatedQueryArgs {
            first: 10,
            after: None,
        }
    ).await?;

    // Filter by name
    let filtered = users.list_for_filters(
        UserFilters {
            name: Some("Alice".to_string()),
        },
        Sort {
            by: UserSortBy::CreatedAt,
            direction: ListDirection::Descending,
        },
        PaginatedQueryArgs {
            first: 10,
            after: None,
        }
    ).await?;

    // Paginate through results
    if let Some(next_query) = filtered.into_next_query() {
        let next_page = users.list_for_filters(
            UserFilters {
                name: Some("Alice".to_string()),
            },
            Sort {
                by: UserSortBy::CreatedAt,
                direction: ListDirection::Descending,
            },
            next_query,
        ).await?;
    }

    Ok(())
}