How to filter on enum and include all rows if no filter value provided-CodePudding

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
);

CodePudding user response：

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.