Indexes

const position = table(
  { name: 'position', public: true },
  {
    id: t.u32().primaryKey().index('direct'),
    x: t.f32(),
    y: t.f32(),
    z: t.f32(),
  }
);

#[spacetimedb::table(accessor = position, public)]
pub struct Position {
    #[primary_key]
    #[index(direct)]
    id: u32,
    x: f32,
    y: f32,
    z: f32,
}

const user = table(
  { name: 'user', public: true },
  {
    id: t.u32().primaryKey(),
    name: t.string().index('btree'),
    age: t.u8().index('btree'),
  }
);

[SpacetimeDB.Table(Accessor = "User", Public = true)]
public partial struct User
{
    [SpacetimeDB.PrimaryKey]
    public uint Id;

    [SpacetimeDB.Index.BTree]
    public string Name;

    [SpacetimeDB.Index.BTree]
    public byte Age;
}

#[spacetimedb::table(accessor = user, public)]
pub struct User {
    #[primary_key]
    id: u32,
    #[index(btree)]
    name: String,
    #[index(btree)]
    age: u8,
}

struct User {
  uint32_t id;
  std::string name;
  uint8_t age;
};
SPACETIMEDB_STRUCT(User, id, name, age)
SPACETIMEDB_TABLE(User, user, Public)
FIELD_PrimaryKey(user, id)
FIELD_Index(user, name)
FIELD_Index(user, age)

const user = table(
  {
    name: 'user',
    public: true,
    indexes: [
      { accessor: 'idx_age', algorithm: 'btree', columns: ['age'] },
    ],
  },
  {
    id: t.u32().primaryKey(),
    name: t.string(),
    age: t.u8(),
  }
);

[SpacetimeDB.Table(Accessor = "User", Public = true)]
[SpacetimeDB.Index.BTree(Accessor = "idx_age", Columns = new[] { "Age" })]
public partial struct User
{
    [SpacetimeDB.PrimaryKey]
    public uint Id;

    public string Name;

    public byte Age;
}

#[spacetimedb::table(accessor = user, public, index(accessor = idx_age, btree(columns = [age])))]
pub struct User {
    #[primary_key]
    id: u32,
    name: String,
    age: u8,
}

const score = table(
  {
    name: 'score',
    public: true,
    indexes: [
      { accessor: 'by_player_and_level', algorithm: 'btree', columns: ['player_id', 'level'] },
    ],
  },
  {
    player_id: t.u32(),
    level: t.u32(),
    points: t.i64(),
  }
);

[SpacetimeDB.Table(Accessor = "Score", Public = true)]
[SpacetimeDB.Index.BTree(Accessor = "by_player_and_level", Columns = new[] { "PlayerId", "Level" })]
public partial struct Score
{
    public uint PlayerId;
    public uint Level;
    public long Points;
}

#[spacetimedb::table(accessor = score, public, index(accessor = by_player_and_level, btree(columns = [player_id, level])))]
pub struct Score {
    player_id: u32,
    level: u32,
    points: i64,
}

struct Score {
  uint32_t player_id;
  uint32_t level;
  int64_t points;
};
SPACETIMEDB_STRUCT(Score, player_id, level, points)
SPACETIMEDB_TABLE(Score, score, Public)
FIELD_NamedMultiColumnIndex(score, by_player_and_level, player_id, level)

// Find users with a specific name
for (const user of ctx.db.user.name.filter('Alice')) {
  console.log(`Found user: ${user.id}`);
}

// Find users with a specific name
foreach (var user in ctx.Db.User.Name.Filter("Alice"))
{
    Log.Info($"Found user: {user.Id}");
}

// Find users with a specific name
for user in ctx.db.user().name().filter("Alice") {
    log::info!("Found user: {}", user.id);
}

// Find users with a specific name
for (auto user : ctx.db[user_name].filter("Alice")) {
    LOG_INFO("Found user: " + user.name);
}

import { Range } from 'spacetimedb/server';

// Find users aged 18 to 65 (inclusive)
for (const user of ctx.db.user.age.filter(
  new Range({ tag: 'included', value: 18 }, { tag: 'included', value: 65 })
)) {
  console.log(`${user.name} is ${user.age}`);
}

// Find users aged 18 or older (from 18 inclusive, unbounded above)
for (const user of ctx.db.user.age.filter(
  new Range({ tag: 'included', value: 18 }, { tag: 'unbounded' })
)) {
  console.log(`${user.name} is an adult`);
}

// Find users younger than 18 (unbounded below, to 18 exclusive)
for (const user of ctx.db.user.age.filter(
  new Range({ tag: 'unbounded' }, { tag: 'excluded', value: 18 })
)) {
  console.log(`${user.name} is a minor`);
}

// Find users aged 18 to 65 (inclusive)
foreach (var user in ctx.Db.User.Age.Filter(new Bound<byte>(18, 65)))
{
    Log.Info($"{user.Name} is {user.Age}");
}

// Find users aged 18 or older (inclusive, unbounded above)
foreach (var user in ctx.Db.User.Age.Filter(new Bound<byte>(18, byte.MaxValue)))
{
    Log.Info($"{user.Name} is an adult");
}

// Find users younger than 18 (unbounded below, to 17 inclusive)
foreach (var user in ctx.Db.User.Age.Filter(new Bound<byte>(byte.MinValue, 17)))
{
    Log.Info($"{user.Name} is a minor");
}

// Find users aged 18 to 65 (inclusive)
for user in ctx.db.user().age().filter(18..=65) {
    log::info!("{} is {}", user.name, user.age);
}

// Find users aged 18 or older
for user in ctx.db.user().age().filter(18..) {
    log::info!("{} is an adult", user.name);
}

// Find users younger than 18
for user in ctx.db.user().age().filter(..18) {
    log::info!("{} is a minor", user.name);
}

// Find users aged 18 to 65 (inclusive)
for (auto user : ctx.db[user_age].filter(range_inclusive(uint8_t(18), uint8_t(65)))) {
    // Process user
}

// Find users aged 18 or older
for (auto user : ctx.db[user_age].filter(range_from(uint8_t(18)))) {
    // Process user
}

// Find users younger than 18
for (auto user : ctx.db[user_age].filter(range_to(uint8_t(18)))) {
    // Process user
}

import { Range } from 'spacetimedb/server';

// Find all scores for player 123 (prefix match on first column)
for (const score of ctx.db.score.by_player_and_level.filter(123)) {
  console.log(`Level ${score.level}: ${score.points} points`);
}

// Find scores for player 123 at levels 1-10 (inclusive)
for (const score of ctx.db.score.by_player_and_level.filter([
  123,
  new Range({ tag: 'included', value: 1 }, { tag: 'included', value: 10 })
])) {
  console.log(`Level ${score.level}: ${score.points} points`);
}

// Find the exact score for player 123 at level 5
for (const score of ctx.db.score.by_player_and_level.filter([123, 5])) {
  console.log(`Points: ${score.points}`);
}

// Find all scores for player 123
foreach (var score in ctx.Db.Score.by_player_and_level.Filter(123u))
{
    Log.Info($"Level {score.Level}: {score.Points} points");
}

// Find all scores for player 123 (prefix match)
for score in ctx.db.score().by_player_and_level().filter(&123u32) {
    log::info!("Level {}: {} points", score.level, score.points);
}

// Find scores for player 123 at levels 1-10
for score in ctx.db.score().by_player_and_level().filter((123u32, 1u32..=10u32)) {
    log::info!("Level {}: {} points", score.level, score.points);
}

// Find the exact score for player 123 at level 5
for score in ctx.db.score().by_player_and_level().filter((123u32, 5u32)) {
    log::info!("Points: {}", score.points);
}

import { Range } from 'spacetimedb/server';

// Delete all users named "Alice"
const deleted = ctx.db.user.name.delete('Alice');
console.log(`Deleted ${deleted} user(s)`);

// Delete users younger than 18
const deletedMinors = ctx.db.user.age.delete(
  new Range({ tag: 'unbounded' }, { tag: 'excluded', value: 18 })
);
console.log(`Deleted ${deletedMinors} minor(s)`);

// Delete all users named "Alice"
var deleted = ctx.Db.User.Name.Delete("Alice");
Log.Info($"Deleted {deleted} user(s)");

// Delete all users named "Alice"
let deleted = ctx.db.user().name().delete("Alice");
log::info!("Deleted {} user(s)", deleted);

// Delete users in an age range
let deleted = ctx.db.user().age().delete(..18);
log::info!("Deleted {} minor(s)", deleted);