prax-orm 0.11.0

---
import DocsLayout from '../../layouts/DocsLayout.astro';
import CodeBlock from '../../components/CodeBlock.astro';

const basicView = `// Define a view based on SQL query
view UserStats {
    id          Int
    email       String
    postCount   Int      @map("post_count")
    totalLikes  Int      @map("total_likes")
    lastPostAt  DateTime? @map("last_post_at")

    @@sql("""
        SELECT
            u.id,
            u.email,
            COUNT(p.id) as post_count,
            COALESCE(SUM(p.likes), 0) as total_likes,
            MAX(p.created_at) as last_post_at
        FROM users u
        LEFT JOIN posts p ON p.author_id = u.id
        GROUP BY u.id, u.email
    """)
}`;

const simpleView = `// Simple view for filtering active users
view ActiveUser {
    id        Int
    email     String
    name      String?
    createdAt DateTime @map("created_at")

    @@sql("SELECT id, email, name, created_at FROM users WHERE active = true")
}

// View with joined data
view PostWithAuthor {
    id          Int
    title       String
    authorName  String  @map("author_name")
    authorEmail String  @map("author_email")
    publishedAt DateTime? @map("published_at")

    @@sql("""
        SELECT
            p.id,
            p.title,
            u.name as author_name,
            u.email as author_email,
            p.published_at
        FROM posts p
        JOIN users u ON u.id = p.author_id
        WHERE p.published = true
    """)
}`;

const materializedView = `// Materialized view for expensive queries (PostgreSQL)
view MonthlyRevenue {
    month       String    @id
    year        Int
    revenue     Decimal
    orderCount  Int       @map("order_count")
    avgOrder    Decimal   @map("avg_order")

    @@sql("""
        SELECT
            TO_CHAR(created_at, 'YYYY-MM') as month,
            EXTRACT(YEAR FROM created_at)::INT as year,
            SUM(total) as revenue,
            COUNT(*) as order_count,
            AVG(total) as avg_order
        FROM orders
        WHERE status = 'COMPLETED'
        GROUP BY TO_CHAR(created_at, 'YYYY-MM'),
                 EXTRACT(YEAR FROM created_at)
    """)

    @@materialized          // Create as MATERIALIZED VIEW
    @@refreshInterval("1h") // Refresh every hour (if supported)
}`;

const viewWithRelations = `// Base models
model User {
    id    Int    @id @auto
    email String @unique
    name  String?
    posts Post[]
}

model Post {
    id        Int      @id @auto
    title     String
    content   String?
    published Boolean  @default(false)
    likes     Int      @default(0)
    author    User     @relation(fields: [authorId], references: [id])
    authorId  Int      @map("author_id")
    createdAt DateTime @default(now()) @map("created_at")
}

// View aggregating user stats
view UserDashboard {
    userId       Int     @id @map("user_id")
    email        String
    name         String?
    totalPosts   Int     @map("total_posts")
    publishedCnt Int     @map("published_count")
    totalLikes   Int     @map("total_likes")
    avgLikes     Float   @map("avg_likes")

    @@sql("""
        SELECT
            u.id as user_id,
            u.email,
            u.name,
            COUNT(p.id) as total_posts,
            COUNT(p.id) FILTER (WHERE p.published) as published_count,
            COALESCE(SUM(p.likes), 0) as total_likes,
            COALESCE(AVG(p.likes), 0) as avg_likes
        FROM users u
        LEFT JOIN posts p ON p.author_id = u.id
        GROUP BY u.id, u.email, u.name
    """)
}`;

const queryingViews = `// Views are queried like models (read-only)
use prax_query::filter::Filter;
use prax_query::types::OrderByField;
use crate::generated::{UserStats, user_stats};

// Find all user stats
let stats = client
    .user_stats()
    .find_many()
    .exec()
    .await?;

// Generated view field modules expose only a COLUMN constant
// (pub const COLUMN: &str) — no gte/desc/sum helper functions.
// Build filters from the COLUMN constants or plain column-name strings:
let top_users = client
    .user_stats()
    .find_many()
    .r#where(Filter::Gte(user_stats::post_count::COLUMN.into(), 10.into()))
    .order_by(OrderByField::desc(user_stats::total_likes::COLUMN))
    .take(10)
    .exec()
    .await?;

// Find unique by ID
let user_stat = client
    .user_stats()
    .find_unique()
    .r#where(Filter::Equals(user_stats::id::COLUMN.into(), 1.into()))
    .exec()
    .await?;

// The generated module also includes a lightweight Query builder for
// column selection, ordering, and pagination only — it does not accept
// filter predicates:
let query = user_stats::Query::new()
    .select(user_stats::email::COLUMN)
    .take(10);`;

const databaseSpecific = `// PostgreSQL-specific view features
view SearchResults {
    id         Int
    title      String
    content    String
    rank       Float

    @@sql("""
        SELECT
            id,
            title,
            content,
            ts_rank(search_vector, to_tsquery('english', 'prax')) as rank
        FROM documents
        WHERE search_vector @@ to_tsquery('english', 'prax')
        ORDER BY rank DESC
    """)
}

// MySQL-specific
view RecentOrders {
    id         Int
    total      Decimal
    status     String
    customerName String @map("customer_name")

    @@sql("""
        SELECT
            o.id,
            o.total,
            o.status,
            c.name as customer_name
        FROM orders o
        JOIN customers c ON c.id = o.customer_id
        WHERE o.created_at > DATE_SUB(NOW(), INTERVAL 30 DAY)
    """)
}`;

const viewBestPractices = `// ✅ Good: Descriptive view names that indicate aggregation
view UserPostStatistics { ... }
view MonthlyRevenueReport { ... }
view ActiveSubscriptions { ... }

// ✅ Good: Document complex views
/// Aggregated statistics for the admin dashboard
/// Updated: Real-time (not materialized)
/// Performance: ~200ms for 10k users
view AdminDashboardStats {
    ...
}

// ✅ Good: Use column aliases for clarity
@@sql("""
    SELECT
        user_id,          -- Clear reference
        COUNT(*) as order_count,  -- Descriptive alias
        SUM(total) as total_spent -- Meaningful name
    FROM orders
    GROUP BY user_id
""")

// ❌ Avoid: Overly complex views
// Break into smaller views or use CTEs

// ❌ Avoid: Views without indexes on base tables
// Ensure proper indexes exist for view queries`;
---

<DocsLayout title="Views - Prax ORM">
  <article class="max-w-4xl mx-auto px-6 py-12">
    <header class="mb-12">
      <h1 class="text-4xl font-bold mb-4">Views</h1>
      <p class="text-xl text-muted">
        Define read-only database views with custom SQL for aggregations, joins, and complex queries.
      </p>
    </header>

    <div class="space-y-12">
      <!-- Introduction -->
      <section>
        <h2 class="text-2xl font-semibold mb-4">What are Views?</h2>
        <p class="text-muted mb-4">
          Database views are virtual tables defined by SQL queries. They provide a way to encapsulate
          complex queries, aggregate data, and expose a simplified interface for reading data.
          Views in Prax are read-only and generate type-safe Rust code for querying.
        </p>
        <div class="grid md:grid-cols-2 gap-4 mb-6">
          <div class="p-4 rounded-xl bg-success-500/10 border border-success-500/30">
            <h4 class="font-semibold text-success-400 mb-2">Use Cases</h4>
            <ul class="text-muted text-sm space-y-1">
              <li>• Aggregate statistics (counts, sums, averages)</li>
              <li>• Pre-joined data for dashboards</li>
              <li>• Filtered subsets of data</li>
              <li>• Denormalized data for APIs</li>
              <li>• Full-text search results</li>
            </ul>
          </div>
          <div class="p-4 rounded-xl bg-warning-500/10 border border-warning-500/30">
            <h4 class="font-semibold text-warning-400 mb-2">Limitations</h4>
            <ul class="text-muted text-sm space-y-1">
              <li>• Read-only (no create, update, delete)</li>
              <li>• No relations to other models</li>
              <li>• Query performance depends on base tables</li>
              <li>• Materialized views need manual refresh</li>
            </ul>
          </div>
        </div>
      </section>

      <!-- Basic View -->
      <section>
        <h2 class="text-2xl font-semibold mb-4">Basic View Definition</h2>
        <p class="text-muted mb-4">
          Define a view with the <code class="px-2 py-1 bg-surface-elevated rounded">view</code> keyword,
          fields that match your SQL output, and a <code class="px-2 py-1 bg-surface-elevated rounded">&#64;&#64;sql()</code>
          attribute containing the query.
        </p>
        <CodeBlock code={basicView} lang="prax" filename="prax/schema.prax" />
      </section>

      <!-- Simple Views -->
      <section>
        <h2 class="text-2xl font-semibold mb-4">Simple Views</h2>
        <p class="text-muted mb-4">
          Views can be as simple as filtering or joining data. Use <code class="px-2 py-1 bg-surface-elevated rounded">&#64;map()</code>
          to map field names to SQL column aliases.
        </p>
        <CodeBlock code={simpleView} lang="prax" filename="prax/schema.prax" />
      </section>

      <!-- Materialized Views -->
      <section>
        <h2 class="text-2xl font-semibold mb-4">Materialized Views</h2>
        <p class="text-muted mb-4">
          For expensive queries that don't need real-time data, use materialized views. They store
          the query results and can be refreshed periodically. Supported on PostgreSQL.
        </p>
        <CodeBlock code={materializedView} lang="prax" filename="prax/schema.prax" />
        <div class="mt-4 p-4 rounded-xl bg-info-500/10 border border-info-500/30">
          <p class="text-info-400 text-sm">
            <strong>Note:</strong> Materialized views need to be refreshed manually or on a schedule.
            Use <code class="px-1 bg-surface-elevated rounded">REFRESH MATERIALIZED VIEW view_name</code> in PostgreSQL
            or configure automatic refresh with <code class="px-1 bg-surface-elevated rounded">&#64;&#64;refreshInterval</code>.
          </p>
        </div>
      </section>

      <!-- Views with Base Models -->
      <section>
        <h2 class="text-2xl font-semibold mb-4">Complete Example with Models</h2>
        <p class="text-muted mb-4">
          Here's a complete example showing models and a view that aggregates their data:
        </p>
        <CodeBlock code={viewWithRelations} lang="prax" filename="prax/schema.prax" />
      </section>

      <!-- Querying Views -->
      <section>
        <h2 class="text-2xl font-semibold mb-4">Querying Views</h2>
        <p class="text-muted mb-4">
          Views are queried using the same type-safe API as models. All read operations
          (find_many, find_unique, find_first, count, aggregate) are available.
        </p>
        <CodeBlock code={queryingViews} lang="rust" filename="src/main.rs" />
        <div class="mt-4 p-4 rounded-xl bg-warning-500/10 border border-warning-500/30">
          <p class="text-warning-400 text-sm">
            <strong>v0.11 breaking change:</strong> the generated view <code>Query</code> builder no
            longer accepts raw-string <code>where_conditions</code>. It supports column selection,
            ordering, and pagination only. For filtered view queries, use the typed client with
            <code>prax_query::filter::Filter</code>, which binds predicate values as parameters
            instead of interpolating raw strings into SQL.
          </p>
        </div>
      </section>

      <!-- Database-Specific Features -->
      <section>
        <h2 class="text-2xl font-semibold mb-4">Database-Specific Features</h2>
        <p class="text-muted mb-4">
          Different databases offer unique view capabilities. Prax supports database-specific SQL in your view definitions.
        </p>
        <CodeBlock code={databaseSpecific} lang="prax" filename="prax/schema.prax" />
      </section>

      <!-- View Attributes Reference -->
      <section>
        <h2 class="text-2xl font-semibold mb-4">View Attributes Reference</h2>
        <div class="overflow-x-auto">
          <table class="w-full text-sm">
            <thead>
              <tr class="border-b border-border">
                <th class="text-left py-3 px-4 font-semibold">Attribute</th>
                <th class="text-left py-3 px-4 font-semibold">Description</th>
                <th class="text-left py-3 px-4 font-semibold">Example</th>
              </tr>
            </thead>
            <tbody class="text-muted">
              <tr class="border-b border-border">
                <td class="py-3 px-4"><code class="text-primary-400">&#64;&#64;sql("...")</code></td>
                <td class="py-3 px-4">The SQL query that defines the view</td>
                <td class="py-3 px-4"><code>&#64;&#64;sql("SELECT * FROM users")</code></td>
              </tr>
              <tr class="border-b border-border">
                <td class="py-3 px-4"><code class="text-primary-400">&#64;&#64;materialized</code></td>
                <td class="py-3 px-4">Create as materialized view (PostgreSQL)</td>
                <td class="py-3 px-4"><code>&#64;&#64;materialized</code></td>
              </tr>
              <tr class="border-b border-border">
                <td class="py-3 px-4"><code class="text-primary-400">&#64;&#64;map("name")</code></td>
                <td class="py-3 px-4">Custom view name in database</td>
                <td class="py-3 px-4"><code>&#64;&#64;map("user_statistics")</code></td>
              </tr>
              <tr class="border-b border-border">
                <td class="py-3 px-4"><code class="text-primary-400">&#64;&#64;refreshInterval</code></td>
                <td class="py-3 px-4">Auto-refresh interval for materialized views</td>
                <td class="py-3 px-4"><code>&#64;&#64;refreshInterval("1h")</code></td>
              </tr>
            </tbody>
          </table>
        </div>
      </section>

      <!-- Best Practices -->
      <section>
        <h2 class="text-2xl font-semibold mb-4">Best Practices</h2>
        <CodeBlock code={viewBestPractices} lang="prax" />
        <div class="mt-6 grid gap-4">
          <div class="p-4 rounded-xl bg-surface border border-border">
            <h4 class="font-semibold mb-2 text-primary-400">Index Base Tables</h4>
            <p class="text-muted text-sm">
              Views inherit the performance characteristics of their underlying queries.
              Ensure proper indexes exist on columns used in WHERE clauses, JOINs, and GROUP BY.
            </p>
          </div>
          <div class="p-4 rounded-xl bg-surface border border-border">
            <h4 class="font-semibold mb-2 text-primary-400">Use Materialized Views for Heavy Aggregations</h4>
            <p class="text-muted text-sm">
              If a view performs expensive aggregations over large datasets, consider making it
              materialized and refreshing on a schedule rather than computing on every query.
            </p>
          </div>
          <div class="p-4 rounded-xl bg-surface border border-border">
            <h4 class="font-semibold mb-2 text-primary-400">Keep Views Focused</h4>
            <p class="text-muted text-sm">
              Create multiple focused views rather than one complex view. This improves
              maintainability, performance, and makes the API clearer.
            </p>
          </div>
        </div>
      </section>
    </div>
  </article>
</DocsLayout>