How to run custom functions / expressions?

[jabdr]

Hello!

I try to implement a complex query using GROUP_CONCAT and also REGEXP, which are not available in sea query. Is there a way to still use these functions?

The following query is just an example:

SELECT GROUP_CONCAT(DISTINCT name ORDER BY name SEPARATOR ';') AS names, city FROM people WHERE city REGEXP '^[a-f].*' GROUP BY city ORDER BY city;

https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat
https://dev.mysql.com/doc/refman/8.0/en/regexp.html#operator_regexp

I tried Func::cust, but it only accepts values as arguments, but I don't have just values as arguments and I couldn't find anything about how to implement REGEXP.

Can you please help me with an example or is this currently not possible?

Johannes

[ikrivosheev]

@jabdr hello! Sorry for delay...

use sea_query::*;

#[derive(Iden)]
enum People {
    Table,
    City,
    Name,
}

fn main() {
    let query = Query::select()
        .column(People::City)
        .expr_as(
            Expr::cust("GROUP_CONCAT(DISTINCT `name` ORDER BY `name` SEPARATOR ';')"),
            Alias::new("names"),
        )
        .from(People::Table)
        .and_where(Expr::cust("`city` REGEXP '^[a-f].*'"))
        .group_by_col(People::City)
        .order_by(People::City, Order::Asc)
        .to_owned();
    println!("{}", query.to_string(MysqlQueryBuilder));
}