如果没有提供过滤值,如何过滤枚举并包含所有行?这是许多开发者在使用枚举类型时经常遇到的问题。在PHP中,我们可以使用array_filter函数来实现这个目标。array_filter函数可以将数组中的每个元素传递给一个回调函数进行过滤,并返回满足条件的元素所组成的新数组。回调函数可以使用in_array函数来判断元素是否存在于枚举中。通过这种方式,我们可以轻松地过滤枚举并包含所有行。
问题内容
我正在开发一个项目资源管理应用程序,我的资源表有多个字段,其中之一是一个枚举,如下所示:
create type "clearance" as enum (
'none',
'baseline',
'nv1',
'nv2',
'tspv'
);
然后,我的资源表包含该枚举:
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'
);
查询数据时,我希望能够在网址中提供查询字符串参数,然后将过滤器应用于数据库查询。
例如(使用本地开发机器):
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
);
解决方法
对于未来需要这个非常利基用例的任何人,答案是基于@mkopriva 的最初提示
该方法是将clearance_level转换为文本,因此过滤器是:
...
AND(r.clearance_level::text = $2 OR $2 = '')
...
当未提供间隙过滤器时,无论间隙如何,都会返回所有结果;当提供过滤器时,仅返回与所提供的间隙_级别匹配的结果。
非常感谢@mkopriva 的帮助。