使用原始 SQL 查询片段

raw() 助手

¥raw() helper

当你想将原始 SQL 片段用作查询的一部分时，可以使用 raw() 助手。它创建一个原始 SQL 查询片段实例，可以将其分配给属性或过滤器的一部分。此片段由 RawQueryFragment 类实例表示，可以序列化为字符串，因此它既可以用作对象值，也可以用作键。序列化时，片段键会被缓存，并且只有缓存的键才能被 ORM 识别。这为原始查询片段增加了运行时安全性。

¥When you want to use a raw SQL fragment as part of your query, you can use the raw() helper. It creates a raw SQL query fragment instance that can be assigned to a property or part of a filter. This fragment is represented by RawQueryFragment class instance that can be serialized to a string, so it can be used both as an object value and key. When serialized, the fragment key gets cached and only such a cached key will be recognized by the ORM. This adds runtime safety to the raw query fragments.

自 v6 以来，需要 raw() 助手才能在查询中使用原始 SQL 片段，无论是通过 EntityManager 还是 QueryBuilder。

¥raw() helper is required since v6 to use a raw SQL fragment in your query, both through EntityManager and QueryBuilder.

// as a value
await em.find(User, { time: raw('now()') });

// as a key
await em.find(User, { [raw('lower(name)')]: name.toLowerCase() });

// with operators
await em.find(User, { [raw('lower(name)')]: { $like: name.toLowerCase() } });

// value can be empty array to skip operator
await em.find(User, { [raw('(select 1 = 1)')]: [] });

raw 助手支持多种签名，你可以传入接收当前属性别名的回调：

¥The raw helper supports several signatures, you can pass in a callback that receives the current property alias:

await em.find(User, { [raw(alias => `lower(${alias}.name)`)]: name.toLowerCase() });

过滤器中的原始片段

¥Raw fragments in filters

在过滤器中使用原始查询片段时，你可能必须使用回调签名来为每个过滤器使用创建新的原始实例 - 即当你将片段用作对象键时，需要对其进行序列化。

¥When using raw query fragment inside a filter, you might have to use a callback signature to create new raw instance for every filter usage - namely when you use the fragment as an object key, which requires its serialization.

@Filter({ name: 'long', cond: () => ({ [raw('length(perex)')]: { $gt: 10000 } }) })

索引和唯一值中的原始片段

¥Raw fragments in indexes and uniques

raw 辅助函数可用于索引和唯一值中，以编写与数据库无关的 SQL 表达式。在这种情况下，你可以使用 '??' 在表达式中标记数据库标识符（表名、列名、索引名等），并将这些标识符作为第二个参数传递给 raw 辅助函数。在内部，这些标识符将根据所使用的数据库自动加引号：

¥The raw helper can be used within indexes and uniques to write database-agnostic SQL expressions. In that case, you can use '??' to tag your database identifiers (table name, column names, index name, ...) inside your expression, and pass those identifiers as a second parameter to the raw helper. Internally, those will automatically be quoted according to the database in use:

// On postgres, will produce: create index "index custom_idx_on_name" on "library.author" ("country")
// On mysql, will produce: create index `index custom_idx_on_name` on `library.author` (`country`)
@Index({ name: 'custom_idx_on_name', expression: (table, columns) => raw(`create index ?? on ?? (??)`, ['custom_idx_on_name', table, columns.name]) })
@Entity({ schema: 'library' })
export class Author { ... }

你还可以使用 quote 标签函数编写与数据库无关的 SQL 表达式。最终结果与使用 raw 函数对数据库标识符进行引用相同，只是表达式语法更简洁：

¥You can also use the quote tag function to write database-agnostic SQL expressions. The end-result is the same as using the raw function regarding database identifiers quoting, only to have a more elegant expression syntax:

@Index({ name: 'custom_idx_on_name', expression: (table, columns) => quote`create index ${'custom_idx_on_name'} on ${table} (${columns.name})` })
@Entity({ schema: 'library' })
export class Author { ... }

使用 QueryBuilder 的 raw 查询

¥raw queries with QueryBuilder

你也可以将 raw 辅助方法用于 QueryBuilder 或 Knex.QueryBuilder 实例。请注意，此功能仅在从 SQL 驱动程序导出的 raw 辅助函数中可用，而不在从 @mikro-orm/core 包导出的辅助函数中可用。

¥You can use the raw helper for QueryBuilder or Knex.QueryBuilder instances too. Note that this is only available in the raw helper exported from SQL drivers, not with the one exported from the @mikro-orm/core package.

import { raw } from '@mikro-orm/postgresql';

const knexRaw = em.getKnex().raw('select 1');

const r = await em.find(User, {
  id: raw(knexRaw),
});

sql 标记模板

¥sql tagged templates

你还可以使用 sql 标记模板函数，其工作原理相同，但仅支持简单的字符串签名：

¥You can also use the sql tagged template function, which works the same, but supports only the simple string signature:

// as a value
await em.find(User, { time: sql`now()` });

// as a key
await em.find(User, { [sql`lower(name)`]: name.toLowerCase() });

// value can be empty array
await em.find(User, { [sql`(select ${1} = ${1})`]: [] });

sql.ref()

当你想引用列时，可以使用 sql.ref() 函数：

¥When you want to refer to a column, you can use the sql.ref() function:

await em.find(User, { foo: sql.ref('bar') });

sql.now()

当你想为日期时间列定义默认值时，可以使用 sql.now() 函数。它解析为 current_timestamp SQL 函数，并接受 length 参数。

¥When you want to define a default value for a datetime column, you can use the sql.now() function. It resolves to current_timestamp SQL function, and accepts a length parameter.

@Property({ default: sql.now() })
createdAt: Date & Opt;

sql.lower() 和 sql.upper()

¥sql.lower() and sql.upper()

要将键转换为小写或大写，可以使用 sql.lower() 和 sql.upper() 函数

¥To convert a key to lowercase or uppercase, you can use the sql.lower() and sql.upper() functions

const books = await orm.em.find(Book, {
  [sql.upper('title')]: 'TITLE',
});

别名

¥Aliasing

要选择原始片段，我们需要为其添加别名。为此，我们可以使用 sql(select 1 + 1).as('<alias>')。

¥To select a raw fragment, we need to alias it. For that, we can use sql`(select 1 + 1)`.as('<alias>').