How to use custom expressions and functions

Custom Expressions

Custom expressions are merely just SimpleExpr that you write yourself in SQL syntax instead of using the library's API. Many functions uses this type as parameters, see this.

Assume we have the following table:

#![allow(unused)]
fn main() {
thing {
    id: i32,
    field: String,
}
}

Here is one example of the use of Expr::cust:

#![allow(unused)]
fn main() {
Query::select()
    .column(thing::Column::Id)
    .from(thing::Table)
    .and_where(Expr::cust("field = \"asdf\""));

Query::select()
    .column(thing::Column::Id)
    .from(thing::Table)
    .and_where(Expr::col(thing::Column::Field).eq("asdf"));
}

They both evaluate to SELECT id FROM thing WHERE field = "asdf"

2 more advanced versions of Expr::cust are Expr::cust_with_exprs and Expr::cust_with_values, where IntoIterator<Item = Into<SimpleExpr>> and IntoIterator<Item = Into<Value>> parameters are accepted in addition to format the given string.

The example below is self explanatory:

#![allow(unused)]
fn main() {
let values = ["asdf", "fdas"];

// Evaluates to `SELECT id FROM thing WHERE field = "asdf" OR field = "fdsa"`
Query::select()
    .column(thing::Column::Id)
    .from(thing::Table)
    .and_where(Expr::cust_with_values("field = ? OR field = ?", values));

// Evaluates to `SELECT id FROM thing WHERE field = "fdsa" OR field = "asdf"`
// note the difference in order
Query::select()
    .column(thing::Column::Id)
    .from(thing::Table)
    .and_where(Expr::cust_with_values("field = $2 OR field = $1", values));
}

Expr::cust_with_exprs's usage is the exact same except only types that implement Into<SimpleExpr> are accepted.

Custom Functions

Custom functions are those defined in the following ways: MySQL, PostgreSQL, and SQLite.

SeaQuery provides a way to systematically call the custom functions, by the use of Iden and Func::cust.

Assume we have defined a function called MY_FUNCTION in the database. Before we are able to invoke it, a struct that implements Iden should be defined first since Func::cust accepts a IntoIden:

#![allow(unused)]
fn main() {
// Method 1: Do it the hard way
struct MyFunction;

impl Iden for MyFunction {
    fn unquoted(&self, s: &mut dyn Write) {
        write!(s, "MY_FUNCTION").unwrap();
    }
}

// Method 2: Do it the macro way
#[derive(Iden)]
#[iden = "MY_FUNCTION"]
struct MyFunction;
}

Now we can use Func::cust:

#![allow(unused)]
fn main() {
// `MY_FUNCTION()`
Func::cust(MyFunction);

// `MY_FUNCTION('hello')`
Func::cust(MyFunction).arg("hello");

// `MY_FUNCTION('a', 'b', 'c', 'd')`
Func::cust(MyFunction).args(["a", "b", "c", "d"]);
}

Func::cust can be used in many places, with the following being a few of them:

#![allow(unused)]
fn main() {
// `SELECT MY_FUNCTION('hello')`
Query::select()
    .expr(Func::cust(MyFunction).arg("hello"));

// `SELECT * FROM thing WHERE MY_FUNCTION(IFNULL('field', 'asdf'))`
Query::select()
    .from(thing::Table)
    .and_where(
        Func::cust(MyFunction).arg(
            Expr::col(thing::Column::Field)
                .if_null("asdf")
            )
        );
}