Support String Concatenation in Query

[ElijahAhianyo]

Postgres, MySQL, and SQLite all support string concatenation via CONCAT/CONCAT_WS functions, and Postgres and SQLite additionally support it via the || operator. We currently don't handle either form, and both + and || silently produce broken SQL statements.

Example:

#[model]
#[derive(Debug, Clone)]
pub struct Product {
    #[model(primary_key)]
    id: Auto<i64>,
    #[model(unique)]
    title: LimitedString<64>,
    name: LimitedString<255>,
}

...
query!(Product, ($name + $title) == "mortal kombat").all(&db)

Currently produces

SELECT "id", "title", "name",  FROM "foo__product" WHERE "name" + "title" = "mortal kombat"

which is meaningless for text columns.
Trying || instead:

query!(Product, ($name || $title) == "mortal kombat").all(&db)

Also produces:

SELECT "id", "title", "name" FROM "foo__product" WHERE "name" OR "title" = "mortal kombat"

because || is being parsed as logical OR, not SQL string concatenation.

Ideally, we want this to yield a cot::db::query::Expr::Concat expression:

Usage in the query! macro, and for the + operator specifically, we'd like it to dispatch to Expr::Concat when operating on string-typed fields(whenever possible), while continuing to produce Expr::Add for numeric fields

One caveat: this can get tricky for concat chains like below:

query!(Product, ($name + "-" + $title) == "mortal - kombat" )

[m4tx]

Ah, this might be somewhat tricky to implement considering that the plus operator means two completely different things depending on the type of the left hand side.

The options I see right now are:

1. Rather than using + for concatenating strings, we could possibly add .concat() function or come up with a new operator (doesn't sound particularly tempting)
2. Make Expr typed somehow: Expr::<T>::eq produces Expr<bool>, Expr::<T>::add produces Expr<T>, etc.

Technically, 2 sounds better, but it might have some serious problems I can't see right now.

What do you think?

[oxura]

I had a look at the current expression layer, and I think the explicit method is the safer first step here. FieldRef<T> already limits the arithmetic traits to numeric types, but the query macro eventually lowers composite expressions into the untyped Expr enum. Making the whole enum generic would spread through the macro output, Query, and the backend conversion code, so this feature would turn into a fairly broad API rewrite.

I would start with syntax along these lines:

query!(Product, $name.concat($title) == "mortal kombat")

That can lower to an Expr::Concat node and emit CONCAT(lhs, rhs), which works consistently on PostgreSQL, MySQL, and SQLite. It also avoids silently giving + a different meaning based on a type that the lowered expression no longer carries. We could keep it binary for now and add variadic/concat_ws support separately if there is a real use case.

If that shape sounds reasonable, I can take it after #649 is out of review.