Skip to main content

addPgTableOrderBy

PostGraphile adds orderBy arguments to various of the table collection fields it builds so that you can control the order in which you receive the results. By default we add the table’s columns (or, if --no-ignore-indexes is enabled, only the columns that are indexed) in both ascending and descending order. For example, you could request the list of all forums ordered from newest to oldest:

PostGraphile 会为生成的各类表集合字段添加 orderBy 参数,方便你控制返回结果的排序方式。默认情况下,系统会将表的所有列(如果启用了 --no-ignore-indexes,则仅包含建有索引的列)以升序和降序两种方式加入排序选项。例如,你可以按创建时间从新到旧查询所有论坛:

query ForumsNewestToOldest {
allForums(orderBy: [CREATED_AT_DESC]) {
nodes {
id
name
}
}
}

Sometimes, however, you want to order by something a little more complex than the fields on that table; maybe you want to order by a field on a related table, or by a computation, or something else.

不过,有时你需要的排序依据比表自身字段更复杂,比如按关联表的字段、计算结果或其他自定义逻辑进行排序。

This plugin generator helps you build new orderBy enum values so that you can sort more flexibly (though you should keep in mind that they are enum values so they cannot accept arguments). Let’s make this clearer with an example:

这个插件生成器可以帮助你定义新的 orderBy enum 枚举值,从而实现更灵活的排序(但需要注意,它们本质上是枚举值,因此无法接收额外参数)。下面通过一个示例具体说明:

Example示例​

To sort a list of forums (stored in the table app_public.forums) by the date of their latest post (posts are stored in app_public.posts) you might create a plugin like this:

若要按最新一篇帖子的发布时间对论坛列表(存储在 app_public.forums 表中)进行排序(帖子存储在 app_public.posts 表中),可以编写如下插件:

import { addPgTableOrderBy, orderByAscDesc } from "postgraphile/utils";
import { TYPES } from "postgraphile/@dataplan/pg";

/* TODO: test this plugin works! */
export default addPgTableOrderBy(
{ schemaName: "app_public", tableName: "forums" },
({ sql }) => {
const sqlIdentifier = sql.identifier(Symbol("lastPostInForum"));
return orderByAscDesc(
"LAST_POST_CREATED_AT",
(queryBuilder) => {
const orderByFrag = sql`(
select ${sqlIdentifier}.created_at
from app_public.posts as ${sqlIdentifier}
where ${sqlIdentifier}.forum_id = ${queryBuilder.alias}.id
order by ${sqlIdentifier}.created_at desc
limit 1
)`;
return { fragment: orderByFrag, codec: TYPES.timestamptz };
},
{ nulls: "last-iff-ascending" },
);
},
);
Easily create orderBy variants轻松创建 orderBy 排序变体

We used the orderByAscDesc helper to easily create the _ASC and _DESC variants without needing redundant code.

这里使用了 orderByAscDesc 辅助函数,无需编写重复代码即可轻松生成 _ASC 和 _DESC 两种排序选项。

The above plugin adds the LAST_POST_CREATED_AT_ASC and LAST_POST_CREATED_AT_DESC enum values to the ForumOrderBy enum, so you can now order forums by these values from another table, e.g.:

上述插件会向 ForumOrderBy enum 中添加 LAST_POST_CREATED_AT_ASC 和 LAST_POST_CREATED_AT_DESC 两个枚举值,之后就可以根据关联表的数据对论坛进行排序了,例如:

query ForumsOrderedByMostRecentPost {
allForums(orderBy: [LAST_POST_CREATED_AT_DESC]) {
nodes {
id
name
}
}
}

Function signature函数签名​

addPgTableOrderBy​

The signature of the addPgTableOrderBy function is:

addPgTableOrderBy 函数的签名如下:

function addPgTableOrderBy(
match: {
serviceName?: string;
schemaName: string;
tableName: string;
},
ordersGenerator: (
build: GraphileBuild.Build,
) => MakeAddPgTableOrderByPluginOrders,
hint?: string,
): GraphileConfig.Plugin;

interface MakeAddPgTableOrderByPluginOrders {
[orderByEnumValue: string]: GraphQLEnumValueConfig;
}

MakeAddPgTableOrderByPluginOrders is a hash (POJO ─ plain old JavaScript object) which maps from the name of the enum value to a GraphQLEnumValueConfig spec. Importantly, these enum values have an associated extensions.grafast.apply method which will be used to apply the ordering to the parent PgSelectQueryBuilder via queryBuilder.orderBy(...). The apply can also choose to set the order as unique via queryBuilder.setOrderIsUnique(), which means that the primary key does not need to be added to the order by clause.

MakeAddPgTableOrderByPluginOrders 是一个映射对象(普通 JavaScript 对象,即 POJO),键为枚举值名称,值为 GraphQLEnumValueConfig 规范配置。关键在于,这些枚举值配置中需要关联一个 extensions.grafast.apply 方法,系统会通过它调用 queryBuilder.orderBy(...) 将排序逻辑应用到父级 PgSelectQueryBuilder 上。apply 方法还可以调用 queryBuilder.setOrderIsUnique() 将当前排序标记为唯一排序,这样查询时就无需再将主键追加到 ORDER BY 子句中。

Use helpers使用辅助函数

Note that you wouldn’t typically build the MakeAddPgTableOrderByPluginOrders yourself, instead you would use the orderByAscDesc helper.

注意,通常不需要手动构造 MakeAddPgTableOrderByPluginOrders 对象,直接使用 orderByAscDesc 辅助函数即可。

orderByAscDesc​

The orderByAscDesc helper makes it easier to build the _ASC and _DESC orders which are typically identical except for name and reversed sort:

通常升序和降序排序除了名称与排序方向相反之外完全相同,使用 orderByAscDesc 辅助函数可以更方便地同时生成 _ASC 和 _DESC 排序选项:

export function orderByAscDesc(
baseName: string,
orderBySpec: OrderBySpecIdentity,
uniqueOrOptions: boolean | OrderByAscDescOptions = false,
): MakeAddPgTableOrderByPluginOrders;

type OrderBySpecIdentity =
| string // Column name
| Omit<PgOrderSpec, "direction"> // Expression
| ((
queryBuilder: PgSelectQueryBuilder,
info: { scope: unknown },
) => Omit<PgOrderSpec, "direction">); // Callback, allows for joins/etc

The baseName will have _ASC and _DESC appended for the two enum values this function creates.

该函数会在 baseName 后分别拼接 _ASC 和 _DESC,生成对应的两个枚举值。

orderBySpec is where the order is specified, it either specifies a column name (string), an order spec without the “direction”, or a callback which returns the order spec without the “direction”.

orderBySpec 用于指定排序规则,它可以是一个列名(字符串)、一个不含 direction 字段的排序配置对象,或一个返回不含 direction 字段排序配置对象的回调函数。

uniqueOrOptions define 1) whether the sort order is unique, and 2) how to sort null values when sorting by ascending and descending order. Only set uniqueOrOptions (or uniqueOrOptions.unique) to true if you can guarantee that the sort order is unique.

uniqueOrOptions 用于指定两项设置:1)排序结果是否具有唯一性;2)在升序和降序排列时如何处理空值(null)。只有在能够确保排序结果绝对唯一的情况下,才应将 uniqueOrOptions(或 uniqueOrOptions.unique)设为 true。

You can also customize how nulls are sorted:

你还可以自定义空值(null)的排序位置:

export type NullsSortMethod =
| "first"
| "last"
| "first-iff-ascending"
| "last-iff-ascending"
| undefined;

export interface OrderByAscDescOptions {
unique?: boolean;
nulls?: NullsSortMethod;
nullable?: boolean;
}

The nulls option extends the ORDER BY clause of the SQL query with either NULLS FIRST or NULLS LAST according to the following rules:

nulls 选项会根据以下规则在生成的 SQL ORDER BY 子句中追加 NULLS FIRST 或 NULLS LAST:

  • "first": specify NULLS FIRST for both ascending and descending;

    "first":无论是升序还是降序,均指定 NULLS FIRST;

  • "last": specify NULLS LAST for both ascending and descending;

    "last":无论是升序还是降序,均指定 NULLS LAST;

  • "first-iff-ascending": specify NULLS FIRST when ordering by ascending, and NULLS LAST when ordering by descending;

    "first-iff-ascending":升序时指定 NULLS FIRST,降序时指定 NULLS LAST;

  • "last-iff-ascending": specify NULLS LAST when ordering by ascending, and NULLS FIRST when ordering by descending;

    "last-iff-ascending":升序时指定 NULLS LAST,降序时指定 NULLS FIRST;

  • (default) undefined: omit both NULLS FIRST and NULLS LAST in the order by clause for both ascending and descending, thus using the default ordering behavior.

    undefined(默认值):升序和降序时均省略 NULLS FIRST 与 NULLS LAST,即采用数据库的默认排序行为。

For example, you may wish to create a plugin to sort movies by either top-rated or lowest-rated first (meaning the average of the movie’s reviews):

例如,假设你想编写一个插件,按电影评分(即电影所有影评的平均分)从高到低或从低到高排序:

const customOrderBy = orderByAscDesc(
"RATING",
(queryBuilder) => {
const sqlIdentifier = sql.identifier(Symbol("movie_reviews"));
return sql`(
select avg(${sqlIdentifier}.rating)
from app_public.movie_reviews as ${sqlIdentifier}
where ${sqlIdentifier}.movie_id = ${queryBuilder.alias}.id
)`;
},
{ nulls: "last" },
);

To get the top-rated movies, one would then use the RATING_DESC option in the GraphQL query. However, by default, RATING_DESC would put movies with no reviews (and thus an average of null) first, followed by the sorted movies. A movie with no ratings is not exactly what one thinks of when one hears “top-rated”! By specifying { nulls: 'last' }, however, PostGraphile knows that this orderBy plugin should still show the movies without any reviews, but just put them at the end of the list.

要获取评分最高的电影,通常会在 GraphQL 查询中传入 RATING_DESC。但按默认行为,RATING_DESC 会把没有任何影评(平均分为 null)的电影排在最前面,然后才是其他已排序的电影——这显然不符合“评分最高”的直觉。而通过指定 { nulls: 'last' },PostGraphile 依然会保留那些暂无影评的电影,但会将它们统一排在结果列表的末尾。

Be explicit about nullability显式声明可空性(nullability)

If your column or expression is nullable, you must either specify nullable: true or pass a value for nulls; if you don’t do this then PostGraphile will use a simpler expression for comparisons which is not null-capable, and this will break cursor pagination when nulls occur.

如果你的列或排序表达式可能是 null(nullable),则必须显式设置 nullable: true 或传入 nulls 参数;否则 PostGraphile 会生成不支持空值处理的简化比较表达式,一旦数据中出现 null,就会导致基于 cursor 游标的分页出错。

Using context or other steps in the addPgTableOrderBy plugin在 addPgTableOrderBy 插件中使用 context 或其他 step​

You can use the applyScope method to access the context of the query. This method is called with the PgSelectQueryBuilder and can be used to apply additional filtering or ordering based on the context.

你可以通过 applyScope 方法获取查询的上下文(context),并在接收 PgSelectQueryBuilder 的回调中根据上下文施加额外的过滤或排序条件。

import { addPgTableOrderBy, orderByAscDesc } from "postgraphile/utils";

// Apply scope for the specific type.
const applyScopePlugin: GraphileConfig.Plugin = {
name: "applyScopePlugin",
schema: {
hooks: {
GraphQLEnumType(config, build) {
const { TYPES } = build.dataplanPg;
if (config.name === "CityOrderBy") {
const {
grafast: { context },
} = build;
config.extensions ??= {};
// @ts-expect-error The type is readonly by types you may get an error here.
config.extensions.grafast ??= {};
config.extensions.grafast.applyScope = () => context(); // Or any other unary step.
}
return config;
},
},
},
};

export default addPgTableOrderBy(
{ schemaName: "app_public", tableName: "users" },
({ sql, dataplanPg: { sqlValueWithCodec, TYPES } }) => {
return orderByAscDesc(
"CITY_NAME",
(queryBuilder, info) => {
const { scope } = info;

const context = scope; // As we applied context as scope.

const orderByFrag = sql`(
select coalesce(
(select value from public.transactions t where t.key = ${queryBuilder.alias}.name and language = ${sqlValueWithCodec(
context?.locale,
TYPES.text,
)}),
${queryBuilder.alias}.name
)
)`;

return { fragment: orderByFrag, codec: TYPES.text };
},
{ nulls: "last-iff-ascending" },
);
},
);