| 1572 | |
| 1573 | #[test] |
| 1574 | fn build_with_query() { |
| 1575 | use sea_orm::{DbBackend, Statement}; |
| 1576 | use sea_query::*; |
| 1577 | |
| 1578 | let base_query = SelectStatement::new() |
| 1579 | .column("id") |
| 1580 | .expr(1i32) |
| 1581 | .column("next") |
| 1582 | .column("value") |
| 1583 | .from("table") |
| 1584 | .to_owned(); |
| 1585 | |
| 1586 | let cte_referencing = SelectStatement::new() |
| 1587 | .column("id") |
| 1588 | .expr(Expr::col("depth").add(1i32)) |
| 1589 | .column("next") |
| 1590 | .column("value") |
| 1591 | .from("table") |
| 1592 | .join( |
| 1593 | JoinType::InnerJoin, |
| 1594 | "cte_traversal", |
| 1595 | Expr::col(("cte_traversal", "next")).equals(("table", "id")), |
| 1596 | ) |
| 1597 | .to_owned(); |
| 1598 | |
| 1599 | let common_table_expression = CommonTableExpression::new() |
| 1600 | .query( |
| 1601 | base_query |
| 1602 | .clone() |
| 1603 | .union(UnionType::All, cte_referencing) |
| 1604 | .to_owned(), |
| 1605 | ) |
| 1606 | .columns(["id", "depth", "next", "value"]) |
| 1607 | .table_name("cte_traversal") |
| 1608 | .to_owned(); |
| 1609 | |
| 1610 | let select = SelectStatement::new() |
| 1611 | .column(ColumnRef::Asterisk) |
| 1612 | .from("cte_traversal") |
| 1613 | .to_owned(); |
| 1614 | |
| 1615 | let with_clause = WithClause::new() |
| 1616 | .recursive(true) |
| 1617 | .cte(common_table_expression) |
| 1618 | .cycle(Cycle::new_from_expr_set_using( |
| 1619 | SimpleExpr::Column(ColumnRef::Column("id".into_iden())), |
| 1620 | "looped", |
| 1621 | "traversal_path", |
| 1622 | )) |
| 1623 | .to_owned(); |
| 1624 | |
| 1625 | let with_query = select.with(with_clause).to_owned(); |
| 1626 | |
| 1627 | assert_eq!( |
| 1628 | DbBackend::MySql.build(&with_query), |
| 1629 | Statement::from_sql_and_values( |
| 1630 | DbBackend::MySql, |
| 1631 | r#"WITH RECURSIVE `cte_traversal` (`id`, `depth`, `next`, `value`) AS (SELECT `id`, ?, `next`, `value` FROM `table` UNION ALL (SELECT `id`, `depth` + ?, `next`, `value` FROM `table` INNER JOIN `cte_traversal` ON `cte_traversal`.`next` = `table`.`id`)) SELECT * FROM `cte_traversal`"#, |