Using aggreagation functions (e.g. ARRAY_AGG)

[cosmincatalin]

From this:

Parents::find()
    .filter(
        Condition::all().add(
            Expr::tuple([
                Expr::col(parents::Column::LastName).into(),
                Expr::col(parents::Column::FirstName).into(),
            ])
            .is_in(keys.iter().map(|t| {
                Expr::tuple([Expr::val(t.0.clone()).into(), Expr::val(t.1.clone()).into()])
            })),
        ),
    )
    .group_by(Expr::tuple([
        Expr::col(parents::Column::LastName).into(),
        Expr::col(parents::Column::FirstName).into(),
    ]))
    .all(&self.db)
    .await

I get:

SELECT
    "parents"."LastName",
    "parents"."FirstName",
    "parents"."Child"
FROM
    "parents"
WHERE ("LastName", "FirstName") IN (('Doe', 'John'), ('Doe', 'Jane'))
GROUP BY ("LastName", "FirstName")

The question is how can I modify the code so I can get to:

SELECT
    "parents"."LastName",
    "parents"."FirstName",
    ARRAY_AGG("parents"."Child")
FROM
    "parents"
WHERE ("LastName", "FirstName") IN (('Doe', 'John'), ('Doe', 'Jane'))
GROUP BY ("LastName", "FirstName")

Essentially, for every parents I want the list of his children with the help of ARRAY_AGG.

[cosmincatalin]

After some digging up, I have found out at least one way to solve this

Using custom expressions and custom struct

References:

• Custom Expressions
• Custom Struct

This can probably be made prettier, but the concept stands

#[derive(FromQueryResult)]
struct ImplodedParentChildren {
    first_name: String,
    last_name: String,
    children: Vec<String>,
}

Parents::find()
    .select_only()
    .column_as(parents::Column::LastName, "first_name")
    .column_as(parents::Column::FirstName, "last_name")
    .column_as(Expr::cust("ARRAY_AGG(\"parents\".\"Child\")"), "children")
    .filter(
        Condition::all().add(
            Expr::tuple([
                Expr::col(parents::Column::LastName).into(),
                Expr::col(parents::Column::FirstName).into(),
            ])
            .is_in(keys.iter().map(|t| {
                Expr::tuple([Expr::val(t.0.clone()).into(), Expr::val(t.1.clone()).into()])
            })),
        ),
    )
    .group_by(Expr::tuple([
        Expr::col(parents::Column::LastName).into(),
        Expr::col(parents::Column::FirstName).into(),
    ]))
    .into_model::<ImplodedParentChildren>()
    .all(&self.db)
    .await

[ns-sjorgedeaguiar]

Nice, thanks for sharing! That could be made into an example in the docs