Port Postgresql to sea-query

Question

I'm trying to port a Postgres query to a sea-queryin Rust. As I'm new to sea-queryI've reached a point where I have no idea how to port the following SQL code:

WITH agg_data AS
         (SELECT tableB_fk
               , tableB.name
               , MAX(version)   maxversion
               , SUM(downloads) sumdownloads
          FROM table1
                   INNER JOIN tableB on tableB.id = tableA.tableB_fk
          GROUP BY tableB.name, tableB_fk)
SELECT ad.*
     , t2.created
     , t2.downloads
FROM agg_data ad
         JOIN tableA t2 ON t2.version = ad.maxversion AND t2.tableB_fk = ad.tableB_fk;

I can't figure out how the query with the with and sub-select is done in sea-query. Unfortunately, there is no example in the docs for the with clause.

Any help is welcome!

Answer 1

The following works and produces the same string as above.

let base_query = sea_orm::sea_query::SelectStatement::new()
            .column(tableB_fk)
            .expr(Expr::tbl(tableB, tableB.name))
            .expr_as(
                Expr::tbl(
                    tableA,
                    tableA.Version,
                )
                .max(),
                Alias::new("maxversion"),
            )
            .expr_as(
                Expr::tbl(
                    tableA,
                    tableA.Downloads,
                )
                .sum()
                .cast_as(Alias::new("bigint")),
                Alias::new("sumdownloads"),
            )
            .from(tableA)
            .inner_join(
                tableB,
                Expr::tbl(tableB, tableB.Id).equals(
                    tableA,
                    tableB_fk,
                ),
            )
            .add_group_by(vec![
                Expr::col((tableB, tableB.name)).into(),
                Expr::col(tableB_fk).into(),
            ])
            .to_owned();

        let common_table_expression = CommonTableExpression::new()
            .query(base_query)
            .table_name(Alias::new("agg_data"))
            .to_owned();

        let select = sea_orm::sea_query::SelectStatement::new()
            .expr(Expr::expr(SimpleExpr::Custom("ad.*".to_string())))
            .column((Alias::new("t2"), tableA.Created))
            .column((Alias::new("t2"), tableA.Downloads))
            .from_as(Alias::new("agg_data"), Alias::new("ad"))
            .join_as(
                JoinType::Join,
                tableA,
                Alias::new("t2"),
                Condition::all()
                    .add(
                        Expr::tbl(Alias::new("t2"), tableA.Version)
                            .equals(Alias::new("ad"), Alias::new("maxversion")),
                    )
                    .add(
                        Expr::tbl(Alias::new("t2"), tableB_fk)
                            .equals(Alias::new("t2"), tableB_fk),
                    ),
            )
            .to_owned();

        let with_clause = WithClause::new().cte(common_table_expression).to_owned();

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

        let qs = query.to_string(PostgresQueryBuilder);
        println!("QUERY STRING: {qs}");