英文:
How to filter on enum and include all rows if no filter value provided
问题
我正在开发一个项目资源管理应用程序,我的资源表有几个字段,其中一个是以下枚举类型:
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'
);
在查询数据时,我希望能够在URL中提供查询字符串参数,然后将其应用于数据库查询的过滤器。
例如(在本地开发机器上):
curl localhost:6543/v1/resources # 返回分页查询中的所有资源
curl localhost:6543/v1/resources?specialties=NSX # 返回具有NSX专长的所有资源
curl localhost:6543/v1/resources?manager=John+Smith # 返回报告给John Smith的资源
curl localhost:6543/v1/resources?jobTitle=Senior+Consultant # 返回所有高级顾问
等等。
然而,我遇到的问题是,我还想能够按照安全许可级别进行过滤,像这样:
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) {
// 这是一个问题
// 如果应返回所有许可级别,则需要提供一个空的过滤器参数。
// 尚未找到一种好的方法来过滤枚举以在不提供过滤参数时包含所有值
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
);
英文:
I'm working on a project resource management application and my resource table has several fields, one of which is an enum as below:
CREATE TYPE "clearance" AS ENUM (
'None',
'Baseline',
'NV1',
'NV2',
'TSPV'
);
Then, my resource table includes that 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'
);
When querying the data, I want to be able to provide query string parameters in the url, that then apply filters to the database query.
For example (using a local dev machine):
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
etc.
Where I'm running into an issue though is that I also want to be able to filter on the security clearance level like this:
curl localhost:6543/v1/resources?clearance=NV2
When I provide a clearance filter I can get the query to work fine:
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())
However, I can't figure out a reasonably way to implement the filtering, so that all results are returned when no clearance filter is provided.
The poor way I have made it work is to just apply an empty string filter on another field when no clearance is filtered for and substitute in the correct filter when a clearance argument is provided.
It works, but smells really bad:
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())
...
...
}
Is there a better way to approach this?
It feels like a really poor solution to the point that I'm thinking of dropping the enum and making it another table that just establishes a domain of values:
CREATE TABLE clearance (
"level" varchar NOT NULL
);
答案1
得分: 1
对于将来需要这个非常特定的用例的任何人,答案是基于@mkopriva的初始提示构建的。
方法是将clearance_level转换为文本,所以过滤器是:
...
AND(r.clearance_level::text = $2 OR $2 = '')
...
当没有提供clearance过滤器时,这将返回所有结果,无论清除级别如何,并且当提供了过滤器时,它将只返回与提供的clearance_level匹配的结果。
非常感谢@mkopriva的帮助。
英文:
For anyone that needs this very niche use case in the future, the answer was built on the initial hint from @mkopriva
The approach was to cast the clearance_level to text, so the filter is:
...
AND(r.clearance_level::text = $2 OR $2 = '')
...
This returns all results, regardless of clearance when no clearance filter is provided and returns only the result that match the provided clearance_level when a filter is provided.
Must appreciated to @mkopriva for the assistance.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论