Mise Common Table Expressions

Common table expressions

With gives a child query a name for use in the statement. WithRecursive combines an anchor query with a recursive member using UNION ALL. Mise handles the WITH syntax and parameter ordering for the selected dialect.

Name a query

var largeCourses = new PostgreSqlQueryBuilder()
    .Select($"{CourseTbl.CourseIdCol:raw}")
    .Select($"{CourseTbl.TitleCol:raw}")
    .Select($"{CourseTbl.CapacityCol:raw}")
    .From($"{CourseTbl.Table:raw}")
    .Where($"{CourseTbl.CapacityCol:raw} >= {minimumCapacity}");

var query = new PostgreSqlQueryBuilder()
    .With("large_courses", largeCourses)
    .Select($"large_courses.course_id")
    .Select($"large_courses.title")
    .Select($"large_courses.capacity")
    .From($"large_courses")
    .OrderBy($"large_courses.course_id");

The CTE is part of one statement. It is not a temporary table created by a separate operation. Multiple With calls add CTEs in registration order, and each name must be unique in that builder.

Traverse a hierarchy

// CategoryTbl.cs
[PostgreSqlTable("categories")]
public static partial class CategoryTbl
{
    [Column("category_id")]
    [PrimaryKey]
    private static int CategoryId { get; }

    [Column("parent_id")]
    private static int? ParentId { get; }
}

Use TreeTbl from Table mappings to name the CTE and reference its column:

var anchor = new PostgreSqlQueryBuilder()
    .Select($"{CategoryTbl.CategoryIdCol:raw}")
    .From($"{CategoryTbl.Table:raw}")
    .Where($"{CategoryTbl.CategoryIdCol:raw} = {rootCategoryId}");

var recursive = new PostgreSqlQueryBuilder()
    .Select($"{CategoryTbl.CategoryIdCol:raw}")
    .From($"{CategoryTbl.Table:raw}")
    .InnerJoin(
        $"{TreeTbl.Table:raw} ON {CategoryTbl.ParentIdCol:raw} = {TreeTbl.CategoryIdCol:raw}"
    );

var query = new PostgreSqlQueryBuilder()
    .WithRecursive(TreeTbl.Name, anchor, recursive)
    .Select($"{TreeTbl.CategoryIdCol:raw}")
    .From($"{TreeTbl.Table:raw}");

The anchor finds the root. The recursive member finds categories whose parent appeared in the previous iteration. It selects the same column shape as the anchor.

Control recursion

WithRecursive uses UNION ALL. Ensure your hierarchy and predicates terminate; a data cycle can repeat rows. Add the database-specific cycle or depth handling your application needs when working with arbitrary graphs.

SQL Server omits the RECURSIVE keyword and provides SqlServerQueryBuilder.MaxRecursion(0..32767). Zero requests no SQL Server recursion limit. Configure that method on the concrete builder before base fluent calls, or retain the concrete variable.

Other dialects use their own recursive syntax and server limits. Child builders must use the same engine. See Database dialects and Subqueries.