表达式

sqala包含了一个SQL表达式类型Expr，其包装了一个SQL的AST，为sqala带来了强大的抽象能力。下面我们将来介绍一些常用的sqala表达式。

字段

字段表达式是最基本的表达式之一，sqala会在查询构建时自动为实体类的伴生对象生成对应的字段表达式，比如实体类中的字段为Int类型，sqala会自动生成对应的Expr[Int]版本，存储数据库的表名和列名信息：

scala

// u.id是一个字段类型表达式
val q =
    from(User).map(u => u.id)

值

在sqala内置支持的数据类型，比如Int、String等，或是实现了CustomField的类型的值，都可以作为sqala的值表达式，它们可以很自然地出现在查询中，而不需要显式转换成Expr，这是使用Scala3强大的上下文抽象能力实现的：

scala

val q =
    from(User).map(u => (u.id, 1, "a"))

sqala的可空值使用Option来管理，但由于None没有实际的类型，会破坏sqala的类型安全性，所以sqala不支持None，而空值需要使用Option.empty[T]来构建：

scala

val q =
    from(User).map(u => (u.id, Option.empty[String]))

转换表达式

在sqala内置的几乎全部功能中，您都可以无缝将Expr、子查询、值等类型混合计算（这个能力是由trait AsExpr接管的），但在某些自定义功能比如自定义函数，自定义运算符等场景，为了方便使用，sqala允许您显式地将值、子查询转为Expr类型。

asExpr就是转换方法，上面的例子也可以写成：

scala

val q =
    from(User).map(u => (u.id, 1.asExpr, "a".asExpr))

原生表达式

某些数据库方言中可能含有无法被sqala表达式系统概括的特殊语法，比如MySQL的全文检索语法，这种场景我们可以使用rawExpr字符串插值器来创建原生表达式，然后使用as方法声明表达式类型：

scala

val word = "abc"
val q =
    from(Post)
        .filter(p => rawExpr"MATCH(${p.title}) AGAINST(${word})".as[Boolean])

表达式插值器支持值、表达式和他们组成的元组，字符串中无需手动拼接引号，圆括号等符号，sqala会自动处理并进行安全转义。

关系运算

符号运算符

除了字段和值之外，最常见的就是关系运算表达式，它常用于查询表达式的filter、on、having等方法中。

sqala支持以下的符号关系运算符：

运算符名称	对应SQL运算符
`==`	`=`
`!=`	`<>`
`<=>`	`IS NOT DISTINCT FROM`
`>`	`>`
`>=`	`>=`
`<`	`<`
`<=`	`<=`

我们可以像使用Scala自带的运算符那样使用它们：

scala

val q =
    from(User).filter(u => u.name == "小黑")

由于sqala有强大的类型兼容性，因此运算符的右侧不仅可以是值，也可以是其他表达式或子查询，这种情况常用于连接条件中：

scala

val q =
    from(User).filter(u => u.id == u.id)

==等运算符不要求两侧类型一致，只需要在SQL标准中允许兼容，比如我们知道，数据库允许数值类型之间跨类型比较，也允许可空类型和不可空类型之间直接比较，比如这样的SQL是合法的：

sql

SELECT
    CAST(1 AS INTEGER) = CAST(NULL AS BIGINT)

所以sqala允许这种行为：

scala

val q =
    // u.id是Expr[Int]，允许和Option[Long]直接比较
    from(User).filter(u => u.id == Option(1L))

我们知道，数据库允许时间类型和字符串之间比较，所以sqala允许时间和字符串类型的表达式直接比较：

scala

val q =
    // u.createTime是Expr[LocalDateTime]，允许和String直接比较
    from(Post).filter(p => u.createTime > "2020-01-01 00:00:00")

可以在此举出一个极端但能体现sqala类型兼容性的例子，我们有如下实体类：

scala

case class Entity(x: Array[Option[Array[Int]]], y: Option[Array[Array[Option[Int]]]])

实体类的两个字段都是嵌套了两层的Int数组，只有空值策略不同，sqala允许这样层数相同且类型兼容的两个数组字段比较：

scala

val q =
    from(Entity).filter(e => e.x == e.y)

但是如果两侧类型不兼容，sqala则会在编译期严格禁止：

scala

val q =
    // 编译错误
    from(User).filter(u => u.id == "abc")

类似的，如果我们把上面的复杂例子的右侧字段最内部改成字符串类型：

scala

case class Entity(x: Array[Option[Array[Int]]], y: Option[Array[Array[Option[String]]]])

sqala也不允许两个字段比较：

scala

val q =
    // 编译错误
    from(Entity).filter(e => e.x == e.y)

sqala的类型兼容性检查不是停留在表面的一层，而是递归检查整个类型树。

但由于Scala的限制，==和!=左侧如果不是Expr类型，则会产生编译错误，所以sqala提供了===和<>应对此类情况（其他运算符不受此影响）：

scala

val q =
    from(User).filter(u => "小黑" === u.name)

或是显式使用asExpr将值转为Expr类型：

scala

val q =
    from(User).filter(u => "小黑".asExpr == u.name)

IS NULL

isNull方法对应SQL的IS NULL，用于探测值是否为空：

scala

val q =
    from(User).filter(u => u.name.isNull)

IN

in方法对应SQL的IN运算符，sqala支持两种in模式，第一种是使用Seq、List等传入一个值的集合，这是最常见的用法：

scala

val q =
    from(User).filter(u => u.id.in(List(1, 2, 3)))

由于SQL中IN ()通常是语法错误，因此sqala会在空集合时开启优化，将此段表达式优化成FALSE。

in方法也可以传入一个表达式元组，此元组同时兼容Expr、值、子查询：

scala

val q =
    from(User).filter(u => u.id.in(1, u.id, from(User).map(_.id).take(1)))

并且，sqala的运算符通常有着极强的类型兼容性，比如以下写法也是合法的：

scala

val list: List[Option[Long]] = List(Some(1L), None, Some(2L))

val q =
    from(User).filter(u => u.id.in(list))

但当类型不符时，将会返回编译错误：

scala

val list: List[String] = List("a", "b", "c")

val q =
    // 编译错误
    from(User).filter(u => u.id.in(list))

LIKE

like方法对应SQL的LIKE运算符，右侧兼容Expr、值、子查询：

scala

val q =
    from(User).filter(u => u.name.like("%小%"))

contains是一个like的简易版本，不需要手动填写%%，但右侧只兼容String：

scala

val q =
    from(User).filter(u => u.name.contains("小"))

startsWith和endsWith与contains类似：

scala

val q =
    from(User).filter(u => u.name.startsWith("小"))

BETWEEN

between是一个有两个参数的方法，对应三元表达式BETWEEN，参数兼容Expr、值、子查询：

scala

val q =
    from(User).filter(u => u.id.between(1, 10))

逻辑运算

sqala支持二元逻辑运算&&（对应AND）和||（对应OR）：

scala

val q =
    from(User).filter(u => u.id == 1 && u.name == "小黑")

通常来说&&的优先级比||高，我们可以通过()控制优先级：

scala

val q =
    from(User).filter(u => u.id < 5 && (u.name == "小黑" || u.name == "小白"))

一元逻辑运算!对应SQL的NOT运算符：

scala

val q =
    from(User).filter(u => !(u.name == "小黑"))

在!的右侧是like、in、between等运算符时，实际生成的是NOT LIKE、NOT IN、NOT BETWEEN等。

多列比较

sqala允许多列同时参与关系运算，但==需要替换为===，!=需要替换为<>：

scala

val q1 =
    from(User).filter: u =>
        (u.id, u.name) === (1, "小黑")

val q2 =
    from(User).filter: u =>
        (u.id, u.name).in(List((1, "小黑"), (2, "小白")))

val q3 =
    from(User).filter: u =>
        (u.id, u.name).in(from(User).map(uu => (uu.id, uu.name)))

类型兼容的表达式、值、子查询可以在比较时任意混合：

scala

val q4 =
    from(User).filter: u =>
        (u.id, u.name) === (Option(1L), from(User).map(uu => uu.name).take(1))

在上面的例子中u.id和u.name的类型是Expr[...]，Option(1L)的类型是Option[Long]，子查询的类型是Query[...]，sqala通过统一的trait AsExpr为这些类型提供了兼容性保证，而传统编程语言中的“方法重载”，在此场景会组合数爆炸，因此无法实现这样符合直觉的调用形式。

数值运算

sqala支持以下数值运算符：

运算符名称	对应SQL运算符
`+`	`+`
`-`	`-`
`*`	`*`
`/`	`/`

scala

val q =
    from(User).filter(u => u.id + 1 > 5).map(_.id * 100)

sqala的数值运算依然有极其强大的类型兼容性，比如Expr[Int]和Option[Double]类型的两个表达式进行数值运算，实际上会返回Expr[Option[Double]]类型的表达式。

sqala还支持%运算符，但实际会生成SQL标准的MOD函数。

字符串拼接

sqala支持数据库的字符串拼接运算，我们可以使用+来拼接字符串，sqala会自动识别两侧的表达式类型，如果是数值则生成+运算符，如果是字符串则生成||运算符：

scala

val q =
    from(User).map(u => u.name + "abc")

在MySQL等不支持||运算符的数据库中，这个操作将生成CONCAT函数表达式。

条件表达式

caseWhen/when/otherwise等方法对应SQL的CASE WHEN THEN ELSE END表达式，参数同样兼容Expr、值、子查询：

scala

val q =
    from(User).map(u => caseWhen(u.id == 1)(1).when(u.id == 2)(u.id).otherWise(Option.empty[Int]))

生成的SQL表达式为：

sql

CASE WHEN "t1"."id" = 1 THEN 1 WHEN "t1"."id" = 2 THEN "t1"."id" ELSE CAST(NULL AS INTEGER)

sqala对返回值类型的推导能力极强，在关系运算部分我们定义极端的例子：

scala

case class Entity(x: Array[Option[Array[Int]]], y: Option[Array[Array[Option[Int]]]])

如果我们把两个字段放在条件表达式的两个分支里：

scala

val q =
    from(Entity)
        .map(e => caseWhen(t.x == t.y)(t.x).otherwise(t.y))

在数据库查询后会自动推导返回类型为：

scala

// 返回类型为 List[Option[Array[Option[Array[Option[Int]]]]]]
val result = db.fetch(q)

coalesce对应SQL的COALESCE表达式，用于返回参数中第一个非空值，但为了易用性，sqala也支持ifNull作为同义词：

scala

val q =
    from(User).map(u => coalesce(u.id, 1))

nullIf对应SQL的NULLIF表达式用于匹配两个值，如果相同则返回NULL：

scala

val q =
    from(User).map(u => nullIf(u.id, 1))

函数

由于各个数据库函数差异极大，因此sqala只内置了ISO/IEC 9075标准中定义的SQL函数，虽然这些函数是标准函数，但仍要参考您实际使用的数据库文档是否支持这些函数，这些函数的作用也请参考数据库相关文档，不在此标准函数列表中的，您可以使用sqala提供的自定义表达式功能自行创建。

函数使用示例：

scala

val q =
    from(User).map(u => substring(u.name, 1))

sqala内置支持的函数如下（此处没有列举聚合函数、窗口函数、时间操作函数、JSON函数等）：

函数	对应的SQL函数
`substring(a, b)`	`SUBSTRING(a FROM b)`
`substring(a, b, c)`	`SUBSTRING(a FROM b FOR c)`
`upper(a)`	`UPPER(a)`
`lower(a)`	`LOWER(a)`
`lpad(a, b, c)`	`LPAD(a, b, c)`
`rpad(a, b, c)`	`RPAD(a, b, c)`
`btrim(a, b)`	`BTRIM(a, b)`
`ltrim(a, b)`	`LTRIM(a, b)`
`rtrim(a, b)`	`RTRIM(a, b)`
`overlay(a, b, c)`	`OVERLAY(a PLACING b FROM c)`
`overlay(a, b, c, d)`	`OVERLAY(a PLACING b FROM c FOR d)`
`regexpLike(a, b)`	`REGEXP_LIKE(a, b)`
`position(a, b)`	`POSITION(a IN b)`
`charLength(a)`	`CHAR_LENGTH(a)`
`octetLength(a)`	`OCTET_LENGTH(a)`
`abs(a)`	`ABS(a)`
`mod(a, b)`	`MOD(a, b)`
`sin(a)`	`SIN(a)`
`cos(a)`	`COS(a)`
`tan(a)`	`TAN(a)`
`asin(a)`	`ASIN(a)`
`acos(a)`	`ACOS(a)`
`atan(a)`	`ATAN(a)`
`sinh(a)`	`SINH(a)`
`cosh(a)`	`COSH(a)`
`tanh(a)`	`TANH(a)`
`log(a, b)`	`LOG(a, b)`
`log10(a)`	`LOG10(a)`
`ln(a)`	`LN(a)`
`exp(a)`	`EXP(a)`
`sqrt(a)`	`SQRT(a)`
`power(a, b)`	`POWER(a, b)`
`ceil(a)`	`CEIL(a)`
`floor(a)`	`FLOOR(a)`
`round(a, b)`	`ROUND(a, b)`
`widthBucket(a, b, c, d)`	`WIDTH_BUCKET(a, b, c, d)`
`currentDate()`	`CURRENT_DATE`
`currentTime()`	`CURRENT_TIME`
`currentTimestamp()`	`CURRENT_TIMESTAMP`
`localTime()`	`LOCALTIME`
`localTimestamp()`	`LOCALTIMESTAMP`

类型转换

as方法配合类型参数进行类型转换，对应SQL的CAST表达式：

scala

val q =
    from(User).map(u => u.id.as[String])

表达式 ​

字段 ​

值 ​

转换表达式 ​

原生表达式 ​

关系运算 ​

符号运算符 ​

IS NULL ​

IN ​

LIKE ​

BETWEEN ​

逻辑运算 ​

多列比较 ​

数值运算 ​

字符串拼接 ​

条件表达式 ​

函数 ​

类型转换 ​

表达式

字段

值

转换表达式

原生表达式

关系运算

符号运算符

IS NULL

IN

LIKE

BETWEEN

逻辑运算

多列比较

数值运算

字符串拼接

条件表达式

函数

类型转换