Application Query Patterns

Optional Filters

Keep One Query Shape

A search screen can keep one query shape while treating a special selector value as no filter.

Program

Play the script to choose a status filter or show every ticket.

optional_status_filter.sql 
CREATE TABLE filter_choice AS WITH params(wanted_status) AS (VALUES ()) SELECT wanted_status FROM params;
CREATE TABLE tickets (id INTEGER, title TEXT, status TEXT);
INSERT INTO tickets VALUES (1, 'login', 'open'), (2, 'billing', 'closed'), (3, 'export', 'open'), (4, 'profile', 'closed');
SELECT id, title, status FROM tickets WHERE (SELECT wanted_status FROM filter_choice) = 'all' OR status = (SELECT wanted_status FROM filter_choice) ORDER BY id;

CREATE TABLE filter_choice AS WITH params(wanted_status) AS (VALUES ()) SELECT wanted_status FROM params;
CREATE TABLE tickets (id INTEGER, title TEXT, status TEXT);
INSERT INTO tickets VALUES (1, 'login', 'open'), (2, 'billing', 'closed'), (3, 'export', 'open'), (4, 'profile', 'closed');
SELECT id, title, status FROM tickets WHERE (SELECT wanted_status FROM filter_choice) = 'all' OR status = (SELECT wanted_status FROM filter_choice) ORDER BY id;

CREATE TABLE filter_choice AS WITH params(wanted_status) AS (VALUES ()) SELECT wanted_status FROM params;
CREATE TABLE tickets (id INTEGER, title TEXT, status TEXT);
INSERT INTO tickets VALUES (1, 'login', 'open'), (2, 'billing', 'closed'), (3, 'export', 'open'), (4, 'profile', 'closed');
SELECT id, title, status FROM tickets WHERE (SELECT wanted_status FROM filter_choice) = 'all' OR status = (SELECT wanted_status FROM filter_choice) ORDER BY id;
optional filter `'all'` means the status predicate should not remove rows.
single query shape The same `SELECT` handles filtered and unfiltered requests.
application parameter The selector stands in for a user-selected filter value.