When a recursive step references the CTE itself, declare a table! schema
whose name and columns match the CTE. Then allow that synthetic CTE table to
appear beside the base table so Diesel can type-check the join used by the
recursive step.
use diesel::{allow_tables_to_appear_in_same_query, prelude::*, table};
use diesel_cte_ext::RecursiveParts;
table! {
categories (id) {
id -> Integer,
parent_category_id -> Nullable<Integer>,
}
}
table! {
parents (id) {
id -> Nullable<Integer>,
}
}
allow_tables_to_appear_in_same_query!(categories, parents);
let parts = RecursiveParts::new(
categories::table
.select(categories::parent_category_id)
.filter(categories::id.eq(4)),
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()),
);
Pass those parts to with_recursive or with_recursive_not_all with the same
CTE name and column list used by the synthetic schema. The construction also
works with diesel-async; call get_results(...).await or load(...).await
on the async connection instead of shipping a separate query shape.