Use with_cte to create a single WITH block without a recursive step. Bundle
the CTE body and the consuming query using CteParts::new before passing them
to the helper.
use diesel::{dsl::sql, sqlite::SqliteConnection, sql_types::Text, RunQueryDsl};
use diesel_cte_ext::{CteParts, RecursiveCTEExt};
fn names() -> diesel::QueryResult<Vec<String>> {
let mut conn = SqliteConnection::establish(":memory:")?;
conn.with_cte(
"names",
&["label"],
CteParts::new(
sql::<Text>("SELECT 'root' AS label UNION ALL SELECT 'child'"),
sql::<Text>("SELECT label FROM names ORDER BY label"),
),
)
.load(&mut conn)
}