How to filter enum and include all rows if no filter value is provided

How to filter enum and include all rows if no filter value is provided？这是许多开发者在使用枚举类型时经常遇到的问题。在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 的帮助。

The above is the detailed content of How to filter enum and include all rows if no filter value is provided. For more information, please follow other related articles on the PHP Chinese website!