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.

wanted_status
optional_status_filter.sql
Replay: real traced execution (multi-file project)
CREATE TABLE filter_choice AS WITH params(wanted_status) AS (VALUES ('open')) 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 ('closed')) 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 ('all')) 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;

  1. choice ← 1 row

    1CREATE TABLE filter_choice AS WITH params(wanted_status) AS (VALUES ('open')) SELECT wanted_status FROM params;2CREATE TABLE tickets (id INTEGER, title TEXT, status TEXT);
    values this step1 rowchoice

  2. tickets ← 4 rows

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

  3. result ← 2 rows

    3INSERT INTO tickets VALUES (1, 'login', 'open'), (2, 'billing', 'closed'), (3, 'export', 'open'), (4, 'profile', 'closed');4SELECT id, title, status FROM tickets WHERE (SELECT wanted_status FROM filter_choice) = 'all' OR status = (SELECT wanted_status FROM filter_choice) ORDER BY id;
    values this step2 rowsresult

  1. choice ← 1 row

    1CREATE TABLE filter_choice AS WITH params(wanted_status) AS (VALUES ('closed')) SELECT wanted_status FROM params;2CREATE TABLE tickets (id INTEGER, title TEXT, status TEXT);
    values this step1 rowchoice

  2. tickets ← 4 rows

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

  3. result ← 2 rows

    3INSERT INTO tickets VALUES (1, 'login', 'open'), (2, 'billing', 'closed'), (3, 'export', 'open'), (4, 'profile', 'closed');4SELECT id, title, status FROM tickets WHERE (SELECT wanted_status FROM filter_choice) = 'all' OR status = (SELECT wanted_status FROM filter_choice) ORDER BY id;
    values this step2 rowsresult

  1. choice ← 1 row

    1CREATE TABLE filter_choice AS WITH params(wanted_status) AS (VALUES ('all')) SELECT wanted_status FROM params;2CREATE TABLE tickets (id INTEGER, title TEXT, status TEXT);
    values this step1 rowchoice

  2. tickets ← 4 rows

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

  3. result ← 4 rows

    3INSERT INTO tickets VALUES (1, 'login', 'open'), (2, 'billing', 'closed'), (3, 'export', 'open'), (4, 'profile', 'closed');4SELECT id, title, status FROM tickets WHERE (SELECT wanted_status FROM filter_choice) = 'all' OR status = (SELECT wanted_status FROM filter_choice) ORDER BY id;
    values this step4 rowsresult
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.