Filter and sort a table

Task: a task table the reader filters (open, done, all) and sorts by clicking a header.

# Recipe: a table the reader filters with links and sorts by its headers.
paths:
  components: ./components
  migrations: ./migrations

datasources:
  db:
    driver: sqlite
    database: ./data/tasks.db

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    priority INTEGER NOT NULL,
    done INTEGER NOT NULL DEFAULT 0
);

INSERT INTO tasks (title, priority, done) VALUES
    ('Write the report', 2, 0),
    ('Call the bank', 1, 0),
    ('Pay the rent', 3, 1);

The filter is ?show=, passed to the query as a parameter. sortable="true" on the query and sort="true" on the table turn each header into a link that orders the query in SQL by ?sort= and ?dir= — so it still works on a paginated query. A column the query does not return is ignored.

<q:component name="Tasks">
  <!-- ?show=open|done|all, from the links below; open by default. -->
  <q:set name="show" value="{query.show}" default="open" />

  <!-- sortable: the table's headers order the query in SQL (?sort= and ?dir=). -->
  <q:query name="tasks" datasource="db" sortable="true">
    SELECT id, title, priority FROM tasks
    WHERE :show = 'all' OR done = CASE :show WHEN 'done' THEN 1 ELSE 0 END
    <q:param name="show" value="{show}" type="string" />
  </q:query>

  <ui:window title="Tasks">
    <ui:hbox gap="md">
      <ui:link to="/?show=open">Open</ui:link>
      <ui:link to="/?show=done">Done</ui:link>
      <ui:link to="/?show=all">All</ui:link>
    </ui:hbox>
    <ui:text>Showing {show}: {tasks_result.recordCount}</ui:text>
    <ui:table source="{tasks}" sort="true">
      <ui:column key="title" label="Title" />
      <ui:column key="priority" label="Priority" />
    </ui:table>
  </ui:window>
</q:component>

<q:test name="open tasks by default" page="/">
  <test:visit />
  <test:expect text="Showing open: 2" />
  <test:expect no-text="Pay the rent" />
</q:test>

<q:test name="the links filter" page="/">
  <test:visit show="done" />
  <test:expect text="Showing done: 1" />
  <test:expect text="Pay the rent" />
</q:test>

<q:test name="a header sorts in SQL" page="/">
  <test:visit show="all" sort="priority" dir="desc" />
  <test:expect text="Pay the rent 3 Write the report 2 Call the bank 1" />
</q:test>

<q:test name="a column the query does not return is ignored" page="/">
  <test:visit sort="password" />
  <test:expect text="Showing open: 2" />
</q:test>

tests/tasks.test.q
  PASS  open tasks by default
  PASS  the links filter
  PASS  a header sorts in SQL
  PASS  a column the query does not return is ignored
4 passed, 0 failed

See UI-13.