Invercargill-Sql/plans/phase-4-implementation-plan.md

Phase 4: Projections and Joins - Detailed Implementation Plan

---

⚠️ CRITICAL IMPLEMENTATION NOTES - READ FIRST ⚠️

Code Style Requirements

1. DO NOT use GLib.List, GLib.HashTable, or Libgee collections

   • Use ONLY Invercargill.DataStructures for all collections
   • Vector<T> instead of GLib.List<T> or arrays
   • Dictionary<TKey, TValue> instead of GLib.HashTable or GLib.Map
   • HashSet<T> for set operations

2. Reference the Invercargill Library

   • Analyze ../Invercargill/src/lib/ for DataStructures and Expressions patterns
   • Key directories: DataStructures/, Expressions/, Mapping/
   • Use Invercargill.Expressions for all expression handling

3. No Raw SQL in High-Level APIs

   • All expression parameters use Invercargill.Expressions syntax
   • Raw SQL is only generated internally by dialect implementations
   • Example: .where("user_id > 100") NOT .where("user_id > 100 AND ...")

4. Honor the Phase 4 Design Document

   • The phase-4-projections-and-joins.md document contains carefully designed APIs
   • Variable scoping and type-based translation are core concepts
   • WHERE/HAVING split for aggregate detection is required
   • Nested projections via select<TProjection>() and select_many<TProjection>()

Key Invercargill.DataStructures Types

Type	Usage
Vector<T>	Ordered collection, use instead of arrays or GLib.List
Dictionary<TKey, TValue>	Key-value mapping, use instead of GLib.HashTable
HashSet<T>	Unique items collection
Enumerable<T>	Base class for sequences, provides LINQ-style operations

Key Invercargill.Expressions Types

Type	Usage
Expression	Base interface for all expressions
ExpressionVisitor	Visitor pattern for expression traversal
ExpressionParser	Parses expression strings to expression trees
BinaryExpression	Binary operations (AND, OR, comparisons)
GlobalFunctionCallExpression	SQL functions like COUNT, SUM, etc.

---

Executive Summary

This document provides a detailed implementation plan for Phase 4: Projections and Joins. The implementation adds read-only, composable query shapes that can join multiple entities and support nested projections.

Architecture Overview

flowchart TB
    subgraph Public API
        OS[OrmSession]
        PB[ProjectionBuilder T]
        PQ[ProjectionQuery T]
    end
    
    subgraph Core Types
        PD[ProjectionDefinition]
        SD[SourceDefinition]
        SELD[SelectionDefinition]
        JD[JoinDefinition]
    end
    
    subgraph Expression Translation
        ETSV[ExpressionToSqlVisitor]
        AA[AggregateAnalyzer]
        FNR[FriendlyNameResolver]
        VT[VariableTranslator]
    end
    
    subgraph SQL Generation
        SDI[SqlDialect Interface]
        SQLD[SqliteDialect]
        PSB[ProjectionSqlBuilder]
    end
    
    subgraph Materialization
        PM[ProjectionMapper T]
    end
    
    OS --> PB
    OS --> PQ
    PB --> PD
    PD --> SD
    PD --> SELD
    PD --> JD
    PQ --> ETSV
    PQ --> PSB
    PSB --> SDI
    SDI --> SQLD
    ETSV --> AA
    ETSV --> FNR
    ETSV --> VT
    PQ --> PM

Part 1: New Files to Create

1.1 Core Projection Types

File: src/orm/projections/projection-definition.vala

Responsibility: Stores the complete definition of a projection including source, joins, selections, and group by clauses.

Key Classes:

• ProjectionDefinition - Main container class
• SourceDefinition - Represents the primary entity source with variable name and type
• JoinDefinition - Represents a joined entity with condition
• SelectionDefinition - Base class for selections

Dependencies: None (pure data structures)

// Key structure
public class ProjectionDefinition : Object {
    public Type result_type { get; set; }
    public SourceDefinition source { get; set; }
    public Vector<JoinDefinition> joins { get; set; }
    public Vector<SelectionDefinition> selections { get; set; }
    public Vector<string> group_by { get; set; }
}

public class SourceDefinition : Object {
    public Type entity_type { get; set; }
    public string variable_name { get; set; }
    public string table_name { get; set; }
}

public class JoinDefinition : Object {
    public Type entity_type { get; set; }
    public string variable_name { get; set; }
    public string table_name { get; set; }
    public string join_condition { get; set; }  // Invercargill expression
}

---

File: src/orm/projections/selection-types.vala

Responsibility: Defines the different types of selections (scalar, nested projection, collection).

Key Classes:

• ScalarSelection - Simple value selection with setter
• NestedProjectionSelection - 1:1 nested projection
• CollectionProjectionSelection - 1:N nested projection

Dependencies: ProjectionDefinition

public abstract class SelectionDefinition : Object {
    public string friendly_name { get; set; }
    public string expression { get; set; }  // Invercargill expression
    public Type value_type { get; set; }
}

public class ScalarSelection : SelectionDefinition {
    public PropertySetter setter { get; set; }
}

public class NestedProjectionSelection : SelectionDefinition {
    public Type projection_type { get; set; }
    public string entry_point_expression { get; set; }
    public PropertySetter setter { get; set; }
}

public class CollectionProjectionSelection : SelectionDefinition {
    public Type projection_type { get; set; }
    public string entry_point_expression { get; set; }
    public PropertySetter setter { get; set; }
}

---

File: src/orm/projections/projection-builder.vala

Responsibility: Fluent builder for creating projection definitions. Follows the same pattern as EntityMapperBuilder<T>.

Key Classes:

• ProjectionBuilder<T> - Generic builder with fluent API

Dependencies:

• ProjectionDefinition
• SelectionTypes
• OrmSession (for entity mapper lookup)

Pattern Reference: See EntityMapperBuilder<T>

public class ProjectionBuilder<T> : Object {
    private ProjectionDefinition _definition;
    private OrmSession _session;
    
    public ProjectionBuilder<T> source<TEntity>(string variable_name)
    public ProjectionBuilder<T> join<TEntity>(string variable_name, string condition)
    public ProjectionBuilder<T> select<TProp>(string friendly_name, string expr, owned PropertySetter<T, TProp> setter)
    public ProjectionBuilder<T> select<TProjection>(string friendly_name, string entry_point, owned PropertySetter<T, TProjection> setter)
    public ProjectionBuilder<T> select_many<TProjection>(string friendly_name, string entry_point, owned PropertySetter<T, Enumerable<TProjection>> setter)
    public ProjectionBuilder<T> group_by(params string[] expressions)
    public ProjectionDefinition build()
}

---

File: src/orm/projections/projection-query.vala

Responsibility: Query builder for projections, similar to Query<T> but with projection-specific features.

Key Classes:

• ProjectionQuery<T> - Fluent query builder

Dependencies:

• ProjectionDefinition
• OrmSession
• ExpressionToSqlVisitor

Pattern Reference: See Query<T>

public class ProjectionQuery<T> : Object {
    private OrmSession _session;
    private ProjectionDefinition _projection;
    private Expression? _where_filter;
    private Expression? _having_filter;
    private Vector<OrderByClause> _orderings;
    private int? _limit;
    private int? _offset;
    
    public ProjectionQuery<T> where(string expression)
    public ProjectionQuery<T> or_where(string expression)
    public ProjectionQuery<T> order_by(string expression)
    public ProjectionQuery<T> order_by_desc(string expression)
    public ProjectionQuery<T> limit(int count)
    public ProjectionQuery<T> offset(int count)
    public Enumerable<T> materialise() throws SqlError
    public async Enumerable<T> materialise_async() throws SqlError
}

---

File: src/orm/projections/projection-mapper.vala

Responsibility: Materializes projection results from database rows into result objects.

Key Classes:

• ProjectionMapper<T> - Materialization logic

Dependencies:

• ProjectionDefinition
• SelectionTypes

Pattern Reference: See EntityMapper<T>

public class ProjectionMapper<T> : Object {
    private ProjectionDefinition _definition;
    
    public T materialise(Properties row) throws Error
    public Enumerable<T> materialise_all(IEnumerable<Properties> rows) throws Error
}

---

1.2 Expression Translation

File: src/orm/projections/aggregate-analyzer.vala

Responsibility: Analyzes expressions to detect aggregate functions for WHERE/HAVING split.

Key Classes:

• AggregateAnalyzer - Expression visitor that detects aggregates

Dependencies: Invercargill.Expressions

public class AggregateAnalyzer : Object, ExpressionVisitor {
    private bool _contains_aggregate;
    
    public bool contains_aggregate(Expression expr)
    
    // Visitor methods detect COUNT, SUM, AVG, MIN, MAX
    public void visit_global_function_call(GlobalFunctionCallExpression expr)
}

---

File: src/orm/projections/friendly-name-resolver.vala

Responsibility: Resolves friendly names to their underlying expressions.

Key Classes:

• FriendlyNameResolver - Lookup service

Dependencies: ProjectionDefinition

public class FriendlyNameResolver : Object {
    private ProjectionDefinition _projection;
    private Map<string, string> _friendly_names;  // name -> expression
    
    public string? resolve(string friendly_name)
    public Expression? resolve_as_expression(string friendly_name)
    public bool has_nested_projection(string friendly_name)
    public ProjectionDefinition? get_nested_projection(string friendly_name)
}

---

File: src/orm/projections/variable-translator.vala

Responsibility: Translates user-defined variable names to SQL aliases with type tracking.

Key Classes:

• VariableTranslator - Variable to alias mapping
• VariableScope - Scope for a single projection

Dependencies: ProjectionDefinition

public class VariableTranslator : Object {
    private Map<string, string> _user_to_sql;     // user var -> sql alias
    private Map<string, Type> _variable_types;    // user var -> entity type
    private int _alias_counter;
    
    public void register_variable(string user_var, Type entity_type, string table_name)
    public string translate(string user_var)
    public Type get_variable_type(string user_var)
    public string generate_alias(Type entity_type)
}

---

File: src/orm/projections/projection-sql-builder.vala

Responsibility: Builds complete SELECT queries from ProjectionDefinition.

Key Classes:

• ProjectionSqlBuilder - SQL generation coordinator

Dependencies:

• ProjectionDefinition
• VariableTranslator
• FriendlyNameResolver
• AggregateAnalyzer

• SqlDialect

  public class ProjectionSqlBuilder : Object {
  private ProjectionDefinition _definition;
  private SqlDialect _dialect;
  private VariableTranslator _translator;
  private FriendlyNameResolver _resolver;
      
  public string build_select(
      Expression? where_clause,
      Expression? having_clause,
      Vector<OrderByClause> order_by,
      int64? limit,
      int64? offset
  )
      
  public string build_subquery_wrapper(string inner_sql, string combined_where)
  }
  

---

1.3 Error Types

File: src/orm/projections/projection-errors.vala

Responsibility: Define projection-specific error types.

public errordomain ProjectionError {
    ENTRY_TYPE_MISMATCH,      // Entry point type doesn't match child's source
    UNDEFINED_FRIENDLY_NAME,  // Friendly name not found
    DUPLICATE_VARIABLE,       // Variable name already defined
    MISSING_GROUP_BY,         // Aggregate without group_by
    NOT_REGISTERED,           // Projection not registered
    NESTED_PROJECTION_ERROR   // Error in nested projection
}

---

Part 2: Existing Files to Modify

2.1 src/orm/orm-session.vala

Changes Required:

1. Add projection registry:

   private Dictionary<Type, ProjectionDefinition> _projections;
   

2. Add registration method:

   public void register_projection<T>(owned GLib.Func<ProjectionBuilder<T>> func)
   

3. Modify query<T>() to support both entities and projections:

   public Query<T> query<T>()  // Existing - for entities
   public ProjectionQuery<T> projection_query<T>()  // New - for projections
   

4. Add internal method for projection execution:

   internal Enumerable<T> execute_projection_query<T>(ProjectionQuery<T> query) throws SqlError
   internal async Enumerable<T> execute_projection_query_async<T>(ProjectionQuery<T> query) throws SqlError
   

5. Add helper method to check if type is registered:

   public bool is_entity_registered<T>()
   public bool is_projection_registered<T>()
   

Lines to modify: ~29-41 (constructor), ~110-112 (query method), add new methods after line 327

---

2.2 src/expressions/expression-to-sql-visitor.vala

Changes Required:

1. Add constructor overload for projection context:

   private ProjectionDefinition? _projection;
   private Map<string, string>? _variable_aliases;
   
   public ExpressionToSqlVisitor.with_projection(
   SqlDialect dialect, 
   ProjectionDefinition? projection,
   Map<string, string>? variable_aliases
   )
   

2. Modify visit_variable() to handle variable translation:

   public void visit_variable(VariableExpression expr) {
   if (_variable_aliases != null && _variable_aliases.has_key(expr.variable_name)) {
       _sql.append(_variable_aliases[expr.variable_name]);
       return;
   }
   // Existing logic...
   }
   

3. Add aggregate detection method:

   public bool contains_aggregate(Expression expr)
   

4. Track aggregate functions during visit:

   private bool _found_aggregate;
   
   public void visit_global_function_call(GlobalFunctionCallExpression expr) {
   string func_name = expr.function_name.up();
   if (func_name == "COUNT" || func_name == "SUM" || ...) {
       _found_aggregate = true;
   }
   // Existing logic...
   }
   

Lines to modify: ~23-46 (fields and constructors), ~169-178 (visit_variable), ~256-293 (visit_global_function_call)

---

2.3 src/dialects/sql-dialect.vala

Changes Required:

1. Add projection SELECT builder method:

   /**
   * Builds a SELECT statement with JOINs for a projection query.
   */
   public abstract string build_projection_select(
   Vector<SourceDefinition> sources,
   Vector<SelectionDefinition> selections,
   Vector<string> group_by,
   string? where_clause,
   string? having_clause,
   Vector<OrderByClause> order_by,
   int64? limit,
   int64? offset
   );
   

2. Add subquery wrapper method:

   /**
   * Builds a subquery wrapper for mixed aggregate/non-aggregate OR conditions.
   */
   public abstract string wrap_subquery_for_mixed_or(
   string inner_query,
   string combined_where
   );
   

3. Add alias generation method:

   /**
   * Generates a table alias with type information for debugging.
   */
   public abstract string generate_table_alias(int index, string type_name);
   

Lines to add: After line 170 (end of interface)

---

2.4 src/dialects/sqlite-dialect.vala

Changes Required:

1. Implement build_projection_select():

   public string build_projection_select(
   Vector<SourceDefinition> sources,
   Vector<SelectionDefinition> selections,
   Vector<string> group_by,
   string? where_clause,
   string? having_clause,
   Vector<OrderByClause> order_by,
   int64? limit,
   int64? offset
   ) {
   // Implementation as specified in phase-4-projections-and-joins.md
   }
   

2. Implement wrap_subquery_for_mixed_or():

   public string wrap_subquery_for_mixed_or(string inner_query, string combined_where) {
   return @"SELECT * FROM ($inner_query) subq WHERE $combined_where";
   }
   

3. Implement generate_table_alias():

   public string generate_table_alias(int index, string type_name) {
   return @"val_$(index)_$(type_name)";
   }
   

Lines to add: After line 544 (end of class)

---

2.5 src/meson.build

Changes Required:

1. Add new projection source files:

   # Projection sources
   sources += files('orm/projections/projection-definition.vala')
   sources += files('orm/projections/selection-types.vala')
   sources += files('orm/projections/projection-builder.vala')
   sources += files('orm/projections/projection-query.vala')
   sources += files('orm/projections/projection-mapper.vala')
   sources += files('orm/projections/aggregate-analyzer.vala')
   sources += files('orm/projections/friendly-name-resolver.vala')
   sources += files('orm/projections/variable-translator.vala')
   sources += files('orm/projections/projection-sql-builder.vala')
   sources += files('orm/projections/projection-errors.vala')
   

2. Add projection test executable:

   # Projection test executable
   projection_test_exe = executable('projection-test', 'tests/projection-test.vala',
   dependencies: [dependencies, invercargill_sql_dep],
   link_with: invercargill_sql
   )
   
   test('Projection tests', projection_test_exe)
   

Lines to modify: After line 41 (ORM sources section), after line 119 (test section)

---

Part 3: Implementation Order

Phase 3.1: Foundation (No Dependencies)

1. src/orm/projections/projection-errors.vala
   └── Pure error definitions

2. src/orm/projections/projection-definition.vala
   └── Core data structures

3. src/orm/projections/selection-types.vala
   └── Selection type hierarchy

Phase 3.2: Analysis Components (Depend on Foundation)

4. src/orm/projections/aggregate-analyzer.vala
   └── Depends: Invercargill.Expressions

5. src/orm/projections/variable-translator.vala
   └── Depends: ProjectionDefinition

6. src/orm/projections/friendly-name-resolver.vala
   └── Depends: ProjectionDefinition, SelectionTypes

Phase 3.3: Builder Pattern (Depends on Foundation + Analysis)

7. src/orm/projections/projection-builder.vala
   └── Depends: ProjectionDefinition, SelectionTypes, OrmSession

Phase 3.4: Dialect Extensions (Depends on Foundation)

8. Modify: src/dialects/sql-dialect.vala
   └── Add abstract methods

9. Modify: src/dialects/sqlite-dialect.vala
   └── Implement abstract methods

Phase 3.5: Expression Visitor Extensions (Depends on Foundation)

10. Modify: src/expressions/expression-to-sql-visitor.vala
    └── Add projection context support

Phase 3.6: SQL Builder (Depends on Analysis + Dialect)

11. src/orm/projections/projection-sql-builder.vala
    └── Depends: All above components

Phase 3.7: Query and Mapper (Depends on SQL Builder)

12. src/orm/projections/projection-mapper.vala
    └── Depends: ProjectionDefinition

13. src/orm/projections/projection-query.vala
    └── Depends: All above components

Phase 3.8: Session Integration (Depends on All)

14. Modify: src/orm/orm-session.vala
    └── Add projection registration and query methods

Phase 3.9: Build and Test

15. Modify: src/meson.build
    └── Add new files and test target

16. Create: src/tests/projection-test.vala
    └── Comprehensive tests

---

Part 4: Dependency Graph

flowchart TD
    subgraph Foundation
        PE[projection-errors.vala]
        PD[projection-definition.vala]
        ST[selection-types.vala]
    end
    
    subgraph Analysis
        AA[aggregate-analyzer.vala]
        VT[variable-translator.vala]
        FNR[friendly-name-resolver.vala]
    end
    
    subgraph Dialect
        SD[sql-dialect.vala - modify]
        SQLD[sqlite-dialect.vala - modify]
    end
    
    subgraph Expression
        ETSV[expression-to-sql-visitor.vala - modify]
    end
    
    subgraph Builder
        PB[projection-builder.vala]
    end
    
    subgraph SQLGen
        PSB[projection-sql-builder.vala]
    end
    
    subgraph Query
        PM[projection-mapper.vala]
        PQ[projection-query.vala]
    end
    
    subgraph Integration
        OS[orm-session.vala - modify]
        MB[meson.build - modify]
        TEST[projection-test.vala - create]
    end
    
    PE --> PD
    PD --> ST
    PD --> AA
    PD --> VT
    PD --> FNR
    ST --> FNR
    
    PD --> PB
    ST --> PB
    
    SD --> SQLD
    PD --> SD
    
    PD --> ETSV
    VT --> ETSV
    
    AA --> PSB
    VT --> PSB
    FNR --> PSB
    SQLD --> PSB
    ETSV --> PSB
    
    PD --> PM
    ST --> PM
    
    PD --> PQ
    PSB --> PQ
    PM --> PQ
    
    PB --> OS
    PQ --> OS
    
    PE --> OS
    PD --> OS
    
    OS --> MB
    PQ --> TEST
    PM --> TEST

---

Part 5: Design Decisions and Caveats

5.1 Variable Scoping

Decision: Each projection maintains its own variable scope. Variable names are translated to SQL aliases using the format val_N_TypeName.

Rationale: This prevents collisions when nesting projections and makes debugging easier.

Caveat: When nesting projections, the child's variables must be translated based on TYPE, not name, since the parent may use different variable names.

5.2 WHERE/HAVING Split

Decision: Automatically detect aggregate functions and split mixed expressions into WHERE (non-aggregate) and HAVING (aggregate) clauses.

Rationale: SQL requires this split, and automating it improves developer experience.

Caveat: When OR combines aggregate and non-aggregate expressions, wrap in subquery. This may have performance implications.

5.3 Client-Side Grouping for 1:N

Decision: select_many collections are grouped client-side after fetching flat results.

Rationale: Avoids complex JSON aggregation SQL that varies between databases.

Caveat: Large result sets may have memory implications. Consider pagination.

5.4 Entry Point Type Checking

Decision: The entry point variable type must exactly match the child projection's source type.

Rationale: Type safety prevents runtime errors.

Caveat: This is checked at registration time, not compile time. Consider adding compile-time checks via generics if possible.

5.5 Friendly Name Uniqueness

Decision: Friendly names must be unique within a projection.

Rationale: Used for WHERE/ORDER BY expressions - duplicates would be ambiguous.

Caveat: Nested projection friendly names are accessed via dot notation (e.g., profile.id).

5.6 Binary Data Types

Decision: Binary blob types should use Invercargill.BinaryData interface with Invercargill.DataStructures.ByteBuffer() - NOT uint8[] on external APIs.

Rationale: Consistency with the Invercargill framework conventions.

---

Part 6: Testing Strategy

6.1 Unit Tests

1. ProjectionBuilder Tests

   • Simple projection with single source
   • Multiple joins
   • Scalar selections
   • Nested projections (1:1)
   • Collection projections (1:N)
   • Group by clauses

2. VariableTranslator Tests

   • Variable registration
   • Alias generation
   • Type tracking
   • Collision detection

3. FriendlyNameResolver Tests

   • Simple name resolution
   • Nested navigation
   • Missing name errors

4. AggregateAnalyzer Tests

   • Detect COUNT, SUM, AVG, MIN, MAX
   • Non-aggregate expressions
   • Mixed expressions

6.2 Integration Tests

1. Simple Projection Query

   • Single table
   • Basic WHERE clause
   • ORDER BY

2. Join Projections

   • Two-table join
   • Multiple joins to same type
   • Complex conditions

3. Aggregate Projections

   • GROUP BY
   • HAVING clause
   • Mixed WHERE/HAVING

4. Nested Projections

   • 1:1 relationship
   • 1:N relationship
   • Deep nesting

5. Error Cases

   • Type mismatch
   • Undefined friendly name
   • Missing GROUP BY

---

Part 7: Code Mode Task Breakdown

When switching to Code mode, the implementation should be broken into these tasks:

1. Task 1: Create foundation files (errors, definitions, selection types)
2. Task 2: Create analysis components (aggregate analyzer, variable translator, friendly name resolver)
3. Task 3: Create projection builder
4. Task 4: Extend SqlDialect interface and implement in SqliteDialect
5. Task 5: Extend ExpressionToSqlVisitor for projection support
6. Task 6: Create projection SQL builder
7. Task 7: Create projection mapper
8. Task 8: Create projection query
9. Task 9: Modify OrmSession for projection support
10. Task 10: Update meson.build and create tests

---

Appendix A: Key Patterns from Existing Codebase

A.1 Builder Pattern (from EntityMapperBuilder)

// Fluent method returns this
public EntityMapperBuilder<T> column<TProp>(string name, ...) {
    // Configure internal state
    return this;
}

// Terminal method builds result
public EntityMapper<T> build() {
    var mapper = new EntityMapper<T>();
    // Apply configuration
    return mapper;
}

A.2 Query Pattern (from Query)

// Store state internally
private Expression? _filter;
private Vector<OrderByClause> _orderings;

// Fluent methods modify state
public Query<T> where(string expression) {
    _filter = ExpressionParser.parse(expression);
    return this;
}

// Terminal method delegates to session
public Enumerable<T> materialise() throws SqlError {
    return _session.execute_query<T>(this);
}

A.3 Visitor Pattern (from ExpressionToSqlVisitor)

public class ExpressionToSqlVisitor : Object, ExpressionVisitor {
    private StringBuilder _sql;
    
    public void visit_binary(BinaryExpression expr) {
        _sql.append("(");
        expr.left.accept(this);
        _sql.append(operator);
        expr.right.accept(this);
        _sql.append(")");
    }
    
    public string get_sql() { return _sql.str; }
}

---

Appendix B: API Examples

B.1 Simple Projection

// Define result type
public class UserSummary : Object {
    public int64 id { get; set; }
    public string name { get; set; }
}

// Register
session.register_projection<UserSummary>(p => p
    .source<User>("u")
    .select<int64>("id", "u.id", (x, v) => x.id = v)
    .select<string>("name", "u.name", (x, v) => x.name = v)
);

// Query
var users = session.projection_query<UserSummary>()
    .where("id > 100")
    .order_by("name")
    .materialise();

B.2 Aggregate Projection

public class OrderStats : Object {
    public int64 user_id { get; set; }
    public int64 order_count { get; set; }
    public double total_spent { get; set; }
}

session.register_projection<OrderStats>(p => p
    .source<User>("u")
    .join<Order>("o", "u.id == o.user_id")
    .group_by("u.id")
    .select<int64>("user_id", "u.id", (x, v) => x.user_id = v)
    .select<int64>("order_count", "COUNT(o.id)", (x, v) => x.order_count = v)
    .select<double>("total_spent", "SUM(o.total)", (x, v) => x.total_spent = v)
);

var stats = session.projection_query<OrderStats>()
    .where("order_count >= 5")  // Becomes HAVING
    .materialise();

B.3 Nested Projection

public class ProfileSummary : Object {
    public int64 id { get; set; }
    public string bio { get; set; }
}

public class UserWithProfile : Object {
    public int64 id { get; set; }
    public ProfileSummary profile { get; set; }
}

session.register_projection<ProfileSummary>(p => p
    .source<Profile>("pr")
    .select<int64>("id", "pr.id", (x, v) => x.id = v)
    .select<string>("bio", "pr.bio", (x, v) => x.bio = v)
);

session.register_projection<UserWithProfile>(p => p
    .source<User>("u")
    .join<Profile>("pr", "u.profile_id == pr.id")
    .select<int64>("id", "u.id", (x, v) => x.id = v)
    .select<ProfileSummary>("profile", "pr", (x, v) => x.profile = v)
);

var users = session.projection_query<UserWithProfile>()
    .where("profile.id == 5")  // Navigate into nested
    .materialise();