Materialized Views

// Get database manager
const db = client.database('your-app-id');

// Create a materialized view with SDK
await db.createView('topSellers', ['products', 'orders'], {
  select: ['id', 'name', 'price', 'salesCount'],
  where: { status: 'active' },
  orderBy: { salesCount: 'desc' },
  limit: 100
});

// Build complex view with JOINs using query builder
const viewQuery = client.queryBuilder()
  .collection('user_top_tracks')
  .joinOne('user', 'users')
    .onField('user_id').equals('$data.user_id')
    .selectFields(['country'])
    .build()
  .orderBy('playcount')
  .selectAll()
  .limit(10000)
  .getQueryRequest();

await db.createView(
  'top_tracks_with_countries',
  ['user_top_tracks', 'users'],
  viewQuery
);

// View with aggregation
const aggregatedView = {
  name: 'plays_by_country',
  source_collections: ['top_tracks_with_countries'],
  query: {
    find: {},
    select: {},
    group_by: ['country'],
    aggregate: {
      total_plays: { '$sum': 'playcount' },
      unique_tracks: { '$countDistinct': 'track_name' },
      unique_artists: { '$countDistinct': 'artist_name' }
    },
    sort_by: ['total_plays'],
    limit: 100
  }
};

// Create via API
await db.createView(
  aggregatedView.name,
  aggregatedView.source_collections,
  aggregatedView.query
);

const db = client.database('your-app-id');

// List all views
const views = await db.listViews();

// Get specific view
const view = await db.getView('topSellers');

// Refresh view data
await db.refreshView('topSellers');

// Delete view
await db.deleteView('topSellers');

// Query the view (no blockchain fetch needed)
const results = await client.query({
  collection: 'top_tracks_with_countries',
  limit: 1000
});

// View results already include JOINed data
results.records.forEach(record => {
  console.log(`Track: ${record.track_name}`);
  console.log(`Country: ${record.user.country}`);
  console.log(`Plays: ${record.playcount}`);
});

// Create a view for dashboard metrics
await db.createView('daily_metrics', ['orders'], {
  find: {},
  select: {},
  group_by: ['date'],
  aggregate: {
    total_orders: { '$count': '*' },
    total_revenue: { '$sum': 'amount' },
    avg_order_value: { '$avg': 'amount' },
    unique_customers: { '$countDistinct': 'customer_id' }
  },
  sort_by: ['date'],
  limit: 365
});

// Query instantly
const metrics = await client.query({
  collection: 'daily_metrics',
  limit: 30 // Last 30 days
});

// Create leaderboard view
await db.createView('user_leaderboard', ['scores'], {
  find: {},
  select: {},
  group_by: ['user_id'],
  aggregate: {
    total_score: { '$sum': 'points' },
    games_played: { '$count': '*' },
    highest_score: { '$max': 'points' }
  },
  sort_by: ['total_score'],
  limit: 100
});

// Create a denormalized feed with user info
const feedQuery = client.queryBuilder()
  .collection('posts')
  .joinOne('author', 'users')
    .onField('id').equals('$data.author_id')
    .selectFields(['name', 'avatar', 'verified'])
    .build()
  .joinMany('recent_comments', 'comments')
    .onField('post_id').equals('$data.id')
    .selectFields(['id', 'content', 'author_name'])
    .build()
  .orderBy('created_at', 'desc')
  .selectAll()
  .limit(1000)
  .getQueryRequest();

await db.createView('feed', ['posts', 'users', 'comments'], feedQuery);