addPgTableCondition
PostGraphile adds condition arguments to various of the table collection
fields it builds so that you can filter the result set down to just the records
you’re interested in. By default we add the table’s indexed columns to the
condition input, where you can specify their value, or null if you only want
the records where that column IS NULL.
PostGraphile 会为生成的各类表集合字段添加 condition 参数,方便你按条件过滤结果集,只保留需要的记录。默认情况下,系统会将表中建有索引的列添加到 condition input 中:你可以指定具体的列值,或传入 null 来仅筛选该列为 IS NULL 的记录。
Many GraphQL experts would opine that GraphQL filters should not be overly complicated, and should not reveal too much of the underlying data store. This is why we don’t have advanced filtering built in by default; however, should you desire that, please check out the filter plugin documented on our Filtering page.
许多 GraphQL 专家认为,GraphQL 的过滤条件不宜过于复杂,也不应过多暴露底层数据存储细节,因此 PostGraphile 默认并未内置高级过滤功能。不过,如果你确实有这方面的需求,可以查看过滤指南(Filtering)页面中介绍的过滤插件。
Here’s an example of filtering forums to those created by a particular user:
下面是一个筛选特定用户创建的论坛的示例:
query ForumsCreatedByUser1 {
allForums(condition: { creatorId: 1 }) {
nodes {
id
name
}
}
}
Sometimes, however, you want to filter by something a little more complex than the fields on that table; maybe you want to filter by a field on a related table, or by a computation, or something else.
不过,有时你需要的过滤条件比表自身字段更复杂,比如按关联表的字段、计算结果或其他自定义逻辑进行筛选。
This plugin generator helps you build new condition values so that you can
filter more flexibly. Let’s make this clearer with an example:
这个插件生成器可以帮助你添加新的 condition 条件,从而实现更灵活的过滤。下面通过具体示例来说明:
Example 1示例 1
To return a list of forums which match a list of primary keys:
返回匹配指定主键列表的论坛列表:
import { addPgTableCondition } from "postgraphile/utils";
import { TYPES, listOfCodec } from "postgraphile/@dataplan/pg";
export default addPgTableCondition(
{ schemaName: "app_public", tableName: "forums" },
"idIn",
(build) => {
const { sqlValueWithCodec, listOfCodec, TYPES } = build.dataplanPg;
const { GraphQLList, GraphQLNonNull, GraphQLInt } = build.graphql;
return {
description: "Filters to records matching one of these ids",
// This is graphql-js for `[Int!]`; assumes you're using
// an integer primary key.
type: new GraphQLList(new GraphQLNonNull(GraphQLInt)),
apply(condition /* : PgCondition */, ids) {
condition.where(
(sql) =>
sql`${condition.alias}.id = ANY(${sqlValueWithCodec(
ids,
listOfCodec(TYPES.int),
)})`,
);
},
};
},
);
Example 2示例 2
To filter a list of forums (stored in the table app_public.forums) to just
those where a particular user has posted in (posts are stored in
app_public.posts) you might create a plugin like this:
若要将论坛列表(存储在 app_public.forums 表中)过滤为仅包含特定用户在其中发过帖子的论坛(帖子存储在 app_public.posts 表中),可以编写如下插件:
import { addPgTableCondition } from "postgraphile/utils";
import { TYPES } from "postgraphile/@dataplan/pg";
export default addPgTableCondition(
{ schemaName: "app_public", tableName: "forums" },
"containsPostsByUserId",
(build) => {
const { sqlValueWithCodec, TYPES } = build.dataplanPg;
const { GraphQLInt } = build.graphql;
return {
description:
"Filters the list of forums to only those which " +
"contain posts written by the specified user.",
type: GraphQLInt,
apply(condition /* : PgCondition */, userId) {
condition.where((sql) => {
const sqlIdentifier = sql.identifier(Symbol("postsByUser"));
return sql`exists(
select 1
from app_public.posts as ${sqlIdentifier}
where ${sqlIdentifier}.forum_id = ${condition.alias}.id
and ${sqlIdentifier}.user_id = ${sqlValueWithCodec(
userId,
TYPES.int,
)}
)`;
});
},
};
},
);
The above plugin adds the containsPostsByUserId condition to collection fields
for the app_public.forums table. You might use it like this:
上述插件会为 app_public.forums 表的集合字段添加 containsPostsByUserId 过滤条件,使用方式如下:
query ForumsContainingPostsByUser1 {
allForums(condition: { containsPostsByUserId: 1 }) {
nodes {
id
name
}
}
}
condition.alias represents the app_public.forums table in the example
above (i.e. the schemaName.tableName table); if you don’t use it in your
implementation then there’s a good chance your plugin is incorrect.
在上面的示例中,condition.alias 指代 app_public.forums 表(即 schemaName.tableName 对应的表别名);如果在插件实现中没有用到它,你的查询逻辑很可能是有问题的。
Function signature函数签名
addPgTableCondition
The signature of the addPgTableCondition function is:
addPgTableCondition 函数的签名如下:
function addPgTableCondition(
match: { serviceName?: string; schemaName: string; tableName: string },
conditionFieldName: string,
fieldSpecGenerator: (build: GraphileBuild.Build) => GrafastInputFieldConfig,
conditionGenerator?: (
value: unknown,
helpers: {
sql: typeof sql;
sqlTableAlias: SQL;
sqlValueWithCodec: typeof sqlValueWithCodec;
build: ReturnType<typeof pruneBuild>;
condition: PgCondition;
},
) => SQL | null | undefined,
): GraphileConfig.Plugin;
The table to match is the table named tableName in the schema named
schemaName.
要匹配的目标表是位于名为 schemaName 的 schema 中、名称为 tableName 的表。
A new condition is added, named conditionFieldName, whose GraphQL
representation is specified by the result of fieldSpecGenerator.
系统会新增一个名为 conditionFieldName 的过滤条件,其在 GraphQL 中的类型和字段定义由 fieldSpecGenerator 的返回值指定。
Also inside fieldSpecGenerator should be an apply, which indicates how
this condition should work. It is passed two arguments: condition (a runtime
PgCondition modifier for the current select) and value (the concrete runtime
input value). The apply should typically use
condition.where((sql) => ...) to apply a condition to the fetch, and use
sqlValueWithCodec(...) for interpolated values.
fieldSpecGenerator 的返回对象中还应包含一个 apply 方法,用于定义该条件的过滤逻辑。它在运行时接收两个参数:condition(当前查询对应的 PgCondition 运行时修饰器)和 value(传入的具体运行时参数值)。apply 通常通过调用 condition.where((sql) => ...) 为本次查询添加过滤条件,并使用 sqlValueWithCodec(...) 对 SQL 插值进行编码处理。
When the field named in conditionFieldName is used in a query, the
apply is called at runtime, resulting in an additional WHERE clause on the
generated SQL (combined using AND).
当查询中使用了 conditionFieldName 指定的条件字段时,系统会在运行时调用 apply,在生成的 SQL 语句中追加一条 WHERE 子句(多个条件之间通过 AND 组合)。