df12 Productions — Building recursive CTEs

Recursive queries delegate the three constituent fragments (seed, recursive step, and final body) to a RecursiveParts struct. Each fragment can be a normal Diesel query builder expression, so Diesel validates the AST at compile time instead of leaving the CTE body as raw SQL.

use diesel::{allow_tables_to_appear_in_same_query, pg::PgConnection, prelude::*, table};
use diesel_cte_ext::{RecursiveCTEExt, RecursiveParts};

fn parent_category_ids(
    conn: &mut PgConnection,
    category_id: i64,
) -> diesel::QueryResult<Vec<i64>> {
    table! {
        categories (id) {
            id -> BigInt,
            parent_category_id -> Nullable<BigInt>,
        }
    }

    table! {
        parents (id) {
            id -> Nullable<BigInt>,
        }
    }

    allow_tables_to_appear_in_same_query!(categories, parents);

    conn.with_recursive_not_all(
        "parents",
        &["id"],
        RecursiveParts::new(
            categories::table
                .select(categories::parent_category_id)
                .filter(categories::id.eq(category_id)),
            categories::table
                .select(categories::parent_category_id)
                .inner_join(
                    parents::table.on(parents::id.assume_not_null().eq(categories::id)),
                ),
            parents::table
                .select(parents::id.assume_not_null())
                .filter(parents::id.is_not_null())
                .order(parents::id.desc()),
        ),
    )
    .load(conn)
}

with_recursive renders a recursive UNION ALL. Use with_recursive_not_all when the recursive term should deduplicate at each iteration using UNION, as shown above.

Async connections receive the same helpers once the async feature is enabled:

use diesel::{allow_tables_to_appear_in_same_query, prelude::*, table};
use diesel_async::{AsyncPgConnection, RunQueryDsl};
use diesel_cte_ext::{RecursiveCTEExt, RecursiveParts};

async fn parent_category_ids_async(
    conn: &mut AsyncPgConnection,
    category_id: i64,
) -> diesel::QueryResult<Vec<i64>> {
    table! {
        categories (id) {
            id -> BigInt,
            parent_category_id -> Nullable<BigInt>,
        }
    }

    table! {
        parents (id) {
            id -> Nullable<BigInt>,
        }
    }

    allow_tables_to_appear_in_same_query!(categories, parents);

    conn.with_recursive_not_all(
        "parents",
        &["id"],
        RecursiveParts::new(
            categories::table
                .select(categories::parent_category_id)
                .filter(categories::id.eq(category_id)),
            categories::table
                .select(categories::parent_category_id)
                .inner_join(
                    parents::table.on(parents::id.assume_not_null().eq(categories::id)),
                ),
            parents::table
                .select(parents::id.assume_not_null())
                .filter(parents::id.is_not_null())
                .order(parents::id.desc()),
        ),
    )
    .load(conn)
    .await
}