Skip to main content
Version: 6.4

使用原始 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 cached key will be recognized by the ORM. This adds a 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() });

// value can be empty array
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 } }) })

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`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>').