如果没有提供过滤值，如何过滤枚举并包括所有行

Question

我正在开发一个项目资源管理应用程序，我的资源表有几个字段，其中一个是enum，如下所示:

CREATE TYPE "clearance" AS ENUM (
'None',
'Baseline',
'NV1',
'NV2',
'TSPV'
);

然后，我的资源表包含enum:

CREATE TABLE "resource" (
"employee_id" integer PRIMARY KEY,
"name" varchar NOT NULL,
"email" varchar NOT NULL,
"job_title_id" integer NOT NULL,
"manager_id" integer NOT NULL,
"workgroup_id" integer NOT NULL,
"clearance_level" clearance,
"specialties" text[],
"certifications" text[],
"active" boolean DEFAULT 't'
);

在查询数据时，我希望能够在url中提供查询字符串参数，然后对数据库查询应用过滤器。

例如(使用本地开发机):

curl localhost:6543/v1/resources # returns all resources in a paginated query
curl localhost:6543/v1/resources?specialties=NSX  # returns all resources with NSX as a specialty
curl localhost:6543/v1/resources?manager=John+Smith # returns resources that report to John Smith
curl localhost:6543/v1/resources?jobTitle=Senior+Consultant # returns all Senior Consultants

等。

我遇到的一个问题是，我也希望能够过滤安全许可级别，像这样:

curl localhost:6543/v1/resources?clearance=NV2

当我提供一个清除过滤器，我可以得到查询工作良好:

query := fmt.Sprintf(`
SELECT count(*) OVER(), r.employee_id, r.name, r.email, job_title.title, m.name AS manager, workgroup.workgroup_name, r.clearance_level, r.specialties, r.certifications, r.active
FROM (((resource r
INNER JOIN job_title ON r.job_title_id=job_title.title_id)
INNER JOIN resource m ON r.manager_id=m.employee_id)
INNER JOIN workgroup ON workgroup.workgroup_id=r.workgroup_id)
WHERE (workgroup.workgroup_name = ANY($1) OR $1 = '{}')
AND (r.clearance_level = $2::clearance)
AND (r.specialties @> $3 OR $3 = '{}')
AND (r.certifications @> $4 OR $4 = '{}')
AND (m.name = $5 OR $5 = '')
AND (r.active = $6)
AND (r.name = $7 OR $7 = '')
ORDER BY %s %s, r.employee_id ASC
LIMIT $8 OFFSET $9`, clearance_filter, fmt.Sprintf("r.%s", filters.sortColumn()), filters.sortDirection())

但是，我不能想出一个合理的方法来实现过滤，以便在没有提供清除过滤器时返回所有结果。

我让它工作的糟糕方法是，当没有过滤清除时，在另一个字段上应用空字符串过滤器，并在提供清除参数时替换为正确的过滤器。

它可以工作，但气味真的很难闻:

func (m *ResourceModel) GetAll(name string, workgroups []string, clearance string, specialties []string,
certifications []string, manager string, active bool, filters Filters) ([]*Resource, Metadata, error) {
// THIS IS A SMELL
// Needed to provide a blank filter parameter if all clearance levels should be returned.
// Have not found a good way to filter on enums to include all values when no filter argument is provided
var clearance_filter = `AND (r.name = $2 OR $2 = '')`
if clearance != "" {
clearance_filter = `AND (r.clearance_level = $2::clearance)`
}
query := fmt.Sprintf(`
SELECT count(*) OVER(), r.employee_id, r.name, r.email, job_title.title, m.name AS manager, workgroup.workgroup_name, r.clearance_level, r.specialties, r.certifications, r.active
FROM (((resource r
INNER JOIN job_title ON r.job_title_id=job_title.title_id)
INNER JOIN resource m ON r.manager_id=m.employee_id)
INNER JOIN workgroup ON workgroup.workgroup_id=r.workgroup_id)
WHERE (workgroup.workgroup_name = ANY($1) OR $1 = '{}')
%s
AND (r.specialties @> $3 OR $3 = '{}')
AND (r.certifications @> $4 OR $4 = '{}')
AND (m.name = $5 OR $5 = '')
AND (r.active = $6)
AND (r.name = $7 OR $7 = '')
ORDER BY %s %s, r.employee_id ASC
LIMIT $8 OFFSET $9`, clearance_filter, fmt.Sprintf("r.%s", filters.sortColumn()), filters.sortDirection())
...
...
}

有更好的方法来处理这个问题吗?

感觉这是一个非常糟糕的解决方案，我正在考虑放弃枚举，使它成为另一个表，只是建立一个值的域:

CREATE TABLE clearance (
"level" varchar NOT NULL
);

Answer 1

对于将来需要这个小众用例的人来说，答案是建立在@mkopriva

的初始提示之上的。方法是将clearance_level转换为文本，因此过滤器为:

...
AND(r.clearance_level::text = $2 OR $2 = '')
...

如果没有提供清除过滤器，则返回所有结果，而不考虑清除;如果提供了过滤器，则只返回与所提供的clearance_level匹配的结果。

必须感谢@mkopriva的帮助。