How to run recursive CTE (seaquery WithQuery) on SeaORM?

[torleivf]

Note: This question might be more or less answered in this question on recursive queries and tied to this issue on a SeaQuery migration guide. However, I did not manage to solve my problem with those answers, as I did not understand how to get a SelectStatement from a WithQuery.

I am trying to run a recursive CTE with SeaORM, and use SeaQuery to produce the SQL query.
I have used the Cookbook to successfully run the base query of the cte, but can't figure out how to run the complete query.

Currently, the following code snippet shows what I do:

    let inherits_query = select.with(with_clause).to_owned();

    let builder = db.get_database_backend();

    // This common SelectStatement runs successfully
    let inherits = Inherits::find_by_statement(
        builder.build(&inherits_base_query))
        .all(db).await?;

    // This WithQuery fails as the trait `StatementBuilder` is not implemented for `WithQuery`
    let inherits = Inherits::find_by_statement(
        builder.build(&inherits_query))
        .all(db).await?;

The complete diagnostic message is:

error[E0277]: the trait bound `WithQuery: StatementBuilder` is not satisfied
   --> src\main.rs:174:27
    |
174 |             builder.build(&inherits_query))
    |                     ----- ^^^^^^^^^^^^^^^ the trait `StatementBuilder` is not implemented for `WithQuery`
    |                     |
    |                     required by a bound introduced by this call
    |
    = help: the following other types implement trait `StatementBuilder`:
              ForeignKeyCreateStatement
              ForeignKeyDropStatement
              IndexCreateStatement
              IndexDropStatement
              TableAlterStatement
              TableCreateStatement
              TableDropStatement
              TableRenameStatement
            and 8 others
note: required by a bound in `DatabaseBackend::build`
   --> C:\...\.cargo\registry\src\github.com-1ecc6299db9ec823\sea-orm-0.9.3\src\database\db_connection.rs:299:12
    |
299 |         S: StatementBuilder,
    |            ^^^^^^^^^^^^^^^^ required by this bound in `DatabaseBackend::build`

[torleivf]

I have now solved this by making a Statement from string, which feels like a workaround, but might be the way to query a WithQuery?
The code now looks like this:

let inherits_query = select.with(with_clause).to_owned();

let db_backend = db.get_database_backend();
// let builder = db_backend.get_query_builder();  //Desired way to get SQLBuilder
let builder = MySqlQueryBuilder::default();  //Manual way to get SQLBuilder

// Query Statement from WithQuery string
let inherits = Inherits::find_by_statement(
        Statement::from_string(
            db_backend, 
            inherits_query.to_string(builder))
        ).all(db).await?;

For my solution I needed a Builder (not the same as the DatabaseBackend named builder in the Cookbook), which I had to set manually. Is there any way to manipulate the result of db.get_database_backend().get_query_builder() for use in .to_string?
The full diagnostic message explaining the problem with using .get_query_builder() is:

error[E0277]: the trait bound `std::boxed::Box<dyn QueryBuilder>: QueryBuilder` is not satisfied
   --> src\main.rs:175:38
    |
175 |             inherits_query.to_string(builder)))
    |                            --------- ^^^^^^^ the trait `QueryBuilder` is not implemented for `std::boxed::Box<dyn QueryBuilder>`
    |                            |
    |                            required by a bound introduced by this call
    |
    = help: the following other types implement trait `QueryBuilder`:
              MysqlQueryBuilder
              PostgresQueryBuilder
              SqliteQueryBuilder
note: required by a bound in `sea_orm::sea_query::QueryStatementWriter::to_string`
   --> C:\...\.cargo\registry\src\github.com-1ecc6299db9ec823\sea-query-0.26.4\src\query\traits.rs:61:21
    |
61  |     fn to_string<T: QueryBuilder>(&self, query_builder: T) -> String {
    |                     ^^^^^^^^^^^^ required by this bound in `QueryStatementWriter::to_string`

[billy1624]

Hey @torleivf, please read https://www.sea-ql.org/sea-orm-cookbook/003-run-sea-query-statement-on-sea-orm.html

[torleivf]

Sorry, I could have made this clear in the initial question, but this is the Cookbook example that I used. It does not work for CTEs, as WithQuery does not implement the StatementBuilder trait. Judging by line 96-110 in

sea-orm/src/database/statement.rs

Line 108 in 0f50f55

build_query_stmt!(sea_query::SelectStatement);

only the basic CRUD-Statements implement the StatementBuilder trait that the Cookbook answer requires.

[billy1624]

Hey @torleivf, I just added a PR. It should solve it.

• implements StatementBuilder for sea_query::WithQuery #1679

[torleivf]

Nice, thanks.

[billy1624]

No problem