New
Release v1.11.0 - Typed Query Builder
Today we've released query builders for Rust and TypeScript modules. The purpose of the query builder API is so that you can write views that will take advantage of the unique performance guarantees of SpacetimeDB's query engine, particularly for realtime subscription updates.
The query builder also now allows you to iterate or scan a table in a view, something that previously wasn't possible using only the index accessors exposed by ViewContext and AnonymousViewContext.
The query builder exposes the following query operators:
.where()Used for filtering. Equivalent to aWHEREcondition in SQL..leftSemijoin()Equivalent to an inner join in sql where a row is return from thelhsonly if it matches with a row on therhs..rightSemijoin()Equivalent to an inner join in sql where a row is return from therhsonly if it matches with a row on thelhs.
Examples (Rust)
use spacetimedb::{Identity, Query, Table, ViewContext, AnonymousViewContext};
#[spacetimedb::table(name = player_state)]
pub struct PlayerState {
#[unique]
player_id: u64,
online: bool,
}
#[spacetimedb::table(name = player_internal)]
pub struct Player {
#[auto_inc]
#[primary_key]
id: u64,
#[unique]
identity: Identity,
name: String,
#[index(btree)]
age: u8,
}
#[spacetimedb::table(name = moderator_internal)]
pub struct Moderator {
#[unique]
player_id: u64,
}
/// Returns all players.
/// Equivalent to `SELECT * FROM player_internal`.
#[spacetimedb::view(name = player, public)]
fn player(ctx: &AnonymousViewContext) -> Query<Player> {
ctx.from.player_internal().build()
}
/// Returns the caller's player.
/// Equivalent to `SELECT * FROM player_internal WHERE "identity" = :sender`.
#[spacetimedb::view(name = my_player, public)]
fn my_player(ctx: &ViewContext) -> Query<Player> {
ctx.from.player_internal().r#where(|p| p.identity.eq(ctx.sender)).build()
}
/// Returns only online players.
/// Equivalent to:
/// ```sql
/// SELECT q.*
/// FROM player_state p JOIN player_internal q ON p.player_id = q.id
/// WHERE p.online
/// ```
#[spacetimedb::view(name = online_player, public)]
fn online_player(ctx: &AnonymousViewContext) -> Query<Player> {
ctx.from
.player_state()
.r#where(|p| p.online.eq(true))
.right_semijoin(ctx.from.player_internal(), |(p, q)| p.player_id.eq(q.id))
.build()
}
/// Returns only the caller's player if online.
/// Equivalent to:
/// ```sql
/// SELECT q.*
/// FROM player_state p JOIN player_internal q ON p.player_id = q.id
/// WHERE p.online AND q.identity = :sender
/// ```
#[spacetimedb::view(name = my_online_player, public)]
fn my_online_player(ctx: &ViewContext) -> Query<Player> {
ctx.from
.player_state()
.r#where(|p| p.online.eq(true))
.right_semijoin(ctx.from.player_internal(), |(p, q)| p.player_id.eq(q.id))
.r#where(|p| p.identity.eq(ctx.sender))
.build()
}
/// Returns the moderators.
/// Equivalent to:
/// ```sql
/// SELECT p.* FROM player_internal p JOIN moderator_internal m ON p.id = m.player_id
/// ```
#[spacetimedb::view(name = moderator, public)]
fn moderator(ctx: &AnonymousViewContext) -> Query<Player> {
ctx.from
.player_internal()
.left_semijoin(ctx.from.moderator_internal(), |(p, m)| p.id.eq(m.player_id))
.build()
}