Ticket Table Developer Guide

Ticket table - Developer Guide

How the ticket list becomes a paginated, server-side table without becoming a second ticket list: one query shared with the message list, the shared data table's new server mode, rows dressed as message-list rows so every inbox action still works, and a CSV export that is a setting.

User page: The ticket table. Design: The ticket workspace - design (PR #206), section 6. Review: Ticket workspace, task editor and table - review (#211-#213).

Contributed by @Dr4inder as #213, the third of the pull requests that replaced draft #206. Changelog #2635, #2637.

---

1. 📁 The files involved

Colour key: 📖 read · ✏️ write · 🖥️ UI · 🧩 shared engine · 🧪 tests

File	🎨	What it does
includes/ticket_list.php	📖	The inbox's query, moved here from api/tickets/get_emails.php (ticketListBase()), and everything the table adds: ticketListQuery() (validation), ticketListSql(), ticketListRead() (a page + filter counts), the column maps, TicketListExport, ticketListCanExport(), ticketListCsvCell()
api/tickets/get_emails.php	📖	The message list - now three lines around ticketListBase()
api/tickets/table.php	📖	One page of the table
api/tickets/table_export.php	📖	The whole list as CSV, if the setting allows
assets/js/tickets-table.js	🖥️	TicketTable: builds the table, maps the folder to filters, dresses the rows, the Messages / Table switch
assets/js/data-table.js, assets/css/data-table.css, includes/data-table-skeleton.php	🧩	The shared table: server mode, sortable / filterable column flags, onRowContextMenu / onRowsRendered hooks, $dtShowCsv
assets/js/inbox.js	🖥️	ticketRowSelector(), and loadEmails() / renderEmailList() handing over to the table
assets/css/tickets-table.css	🖥️	The table in the inbox's middle column
tickets/index.php	🖥️	The container, the action-row dropdown, window.TICKETS_LIST_LAYOUT
includes/table_views.php	📖	'tickets' added to TABLE_VIEW_KEYS - saved views
tickets/settings/index.php, tickets/settings/manifest.php, api/settings/save_system_settings.php	✏️	Who can export the ticket list (ticket_list_export), checked against the enum
system/preferences/index.php, includes/preference_templates.php	🖥️	tickets_list_layout (messages / table)
tests/ticket-workspace-table.php, tests/data-table-server.php, tests/ticket-table-live.php	🧪	See section 8

What you do not touch: the other five tables (Assets, Tasks, Calendar, Changes, Domains). They pass no server, so the engine behaves for them exactly as before - tests/data-table-server.php checks that.

---

2. ⭐ One list, two ways of showing it

The single most important rule: the table must never show a ticket the message list would not, nor miss one it would. So there is one query:

ticketListBase($conn, $analystId, $request, $extraColumns, $extraJoins)
    ├── get_emails.php        - the message list, $_GET passed straight in
    └── ticketListSql()       - the table: adds search + column filters
            ├── ticketListRead()     - COUNT, one page, the filter counts
            └── table_export.php     - every row, in batches

ticketListBase() holds the company scope, trash, snooze, merged-away tickets, and the My tickets / Hide closed filter - with all the comments they had in api/tickets/get_emails.php.

🔑 TRAP: change which tickets an analyst may see in ticketListBase() only. A rule added to one endpoint and not here is a list and a table that disagree - and with the export, a CSV that contains a ticket the screen would not show.

No SQL by text replacement

The first draft added the table's columns with str_replace() on the inbox's SQL - which would silently match nothing the next time anyone edited that query. Now they are parameters:

[$sql, $args] = ticketListBase($conn, $analystId, [...],
    ['t.created_datetime', 't.status_id', 't.ticket_type_id', 't.priority_id',
     'tp.display_order AS priority_order', 'qd.name AS department_name', ...],
    ['LEFT JOIN departments qd ON qd.id = t.department_id', ...]);

They are trusted code - never request values.

---

3. The request is checked before any SQL is built

ticketListQuery($_GET) turns the raw request into a checked array, and nothing after it reads $_GET. Anything it does not recognise is a 400, not ignored:

• page a whole number, page_size 25, 50 or 100;
• sort a key of ticketListSorts(), dir asc / desc, view active / snoozed / trash;
• filters JSON, at most one entry per filter column, at most 200 values each, each a positive id or 'unassigned';
• q at most 500 characters; the booleans 0 or 1.

Every column name that reaches SQL comes out of one of three fixed maps, keyed by the table's own column keys:

Map	Key →	Used for
ticketListFilters()	[row field, tickets column, lookup table, label column]	the IN (...) filters, and the filter counts with their labels
ticketListSorts()	row field	ORDER BY (priority sorts by display order, not name)
ticketListExportColumns()	row field	the CSV's columns

🔑 TRAP: the search box goes through likeContainsPattern() (includes/functions.php), never '%' . $term . '%'. Email subjects carry typographic quotes - Gmail sends We’re with U+2019 - and a keyboard types the straight one, so the plain pattern missed them (reported on the first day). Every apostrophe or quote becomes _, and a typed % or _ is escaped. api/tickets/search_tickets.php uses it too.

ticketListOrder() puts empty values last whichever way you sort, and adds ticket_id DESC as a tie-break - without it, rows with the same value could swap between two page loads and one ticket would appear on both pages while another appeared on neither.

Filter counts

ticketListRead() runs the query once per filter column leaving that column's own filter out ($omitFilter) and keeping every other one. So ticking Status = Open still shows how many are Closed - the list of values you could switch to. Six small grouped queries per page load; they share the page's scope, so a count can never reveal a ticket the analyst cannot see.

---

4. The shared table's server mode

assets/js/data-table.js was built to load every row and do the rest in the browser. A ticket queue can be tens of thousands of rows, so the table gained a second mode, chosen by passing server:

createDataTable({
    ...,
    server: {
        load: async query => ({rows, total, page, pageSize, facets}),
        exportCsv: async (query, abortSignal) => { ... },
    },
});
// query = {page, pageSize, sort, search, filters, context}

• Any change to sort, filter or search reloads page 1 - render() compares a signature of the query (less the page) with the one the rows on screen answer, and fetches when they differ.
• Last request wins. Each load takes a generation; a slower, older response is dropped.
• A failed load keeps the old rows, greyed and inert, with Retry. Never acted on - they may no longer be true.
• Filters list the server's values and counts (facets), with the server's label for each id, and keep a ticked value at 0 so it can be unticked.
• manual: true - the table is started by its module (table.start()), not on page load, because the ticket table may never be shown.
• getContext / applyContext carry state outside the table into a saved view and back: for tickets, the view (active / snoozed / trash), My tickets, Hide closed, and content search.

Every other table passes no server, and runs exactly as before.

---

5. 🔑 Rows dressed as message-list rows

The inbox's actions - click, Ctrl/Shift-click, the right-click menu, drag to a folder, the SLA dot, bulk actions, merge - all find rows with #emailList .email-item[data-email-id=...]. Rather than teach each of them about a table, onRowsRendered gives every <tr> what they look for:

node.classList.add('email-item', 'ticket-queue-row');
node.dataset.emailId      = row.inbox_id;     // the message list's row id
node.dataset.ticketId     = row.ticket_id;
node.dataset.ticketNumber = row.ticket_number;
node.draggable = true;

and assets/js/inbox.js looks in whichever list is showing:

function ticketRowSelector(suffix = '') {
    return (window.TicketTable?.isActive() ? '#ticketTableContainer' : '#emailList') + ' .email-item' + suffix;
}

row.inbox_id is the message list's row id: an email id, or the negative ticket id for a ticket with no email (see the 🔑 in ticketListBase()). It must be the same id the message list uses, or the reading pane, which keys off it, would not find the row.

⚠️ Exactly one list is in the page's flow at a time (TicketTable.paint() sets hidden on the other). If both were, a Shift-click range or a select-all would pick up rows from the hidden list.

⚠️ A page change clears the selection (onLoading). A bulk action must never reach a ticket on another page that is no longer on screen.

The folder is the starting filter. folderFilters() turns the folder pane's currentFilter into table filters - a department, team or analyst folder, its status sub-folder, or an Unassigned folder (empty in whichever column the folders are grouped by). Change a column filter so the rows no longer match the folder, and the folder highlight goes to All tickets rather than claim a folder the rows are not.

loadEmails() - called on a folder click, the view filter, and when the 60-second mailbox check finds something new - hands over to TicketTable.reload(), which replaces the filters on a new folder, goes to page 1 on a new view filter, and otherwise re-reads the current page.

---

6. 🔒 The CSV export

A one-click export of the whole filtered queue is a new way for ticket data to leave FreeITSM, so it is a setting (design decision 2), TicketListExport:

enum TicketListExport: string
{
    case Admins      = 'admins';         // the default, also when never saved
    case TicketUsers = 'ticket_users';
    case Nobody      = 'nobody';
}

• ticketListCanExport() is checked by api/tickets/table_export.php on every request, not only by hiding the button ($dtShowCsv in tickets/index.php). A direct request is refused with 403.
• The setting is under the TICKETS_GENERAL capability, like the rest of the General tab - whoever may change General settings may open the export to everyone. That is deliberate; it is the same trust as the other settings there.
• Columns: only ticketListExportColumns(), each once - an export is never a way to reach a field the table does not offer.
• Every row, in batches of 500, in one transaction, so every batch reads the same snapshot and a ticket closed mid-export cannot appear twice or not at all.
• Written to a buffer and sent only when complete (php://temp, spilling to the system temp directory past 2 MB - never the web root), so a failure half way returns an error, not half a file that looks whole.
• Formula injection: ticketListCsvCell() puts an apostrophe before a cell starting with = + - @ (after any spaces or control characters). Ticket subjects are text strangers wrote.
• Times: created and snoozed-until are UTC and get a Z. Work begins / ends are the wall-clock times as typed, with no zone - a Z would invent one (a TRAP: in the code).
• UTF-8 with a BOM, so Excel reads accents.

The browser fetches it with an AbortController; clicking CSV again cancels.

---

7. Adding a column

1. Select it in ticketListSql()'s extra columns (with any join it needs).
2. Add it to setup()'s column list in assets/js/tickets-table.js: key, the row field it shows, visible by default, sortable.
3. Sortable? Add it to ticketListSorts(). Filterable (an id column)? Add it to ticketListFilters() with its lookup table and label, and to the filterable list in setup(). In the CSV? Add it to ticketListExportColumns().
4. Its heading: tickets.queue.columns.<key> in lang/en/tickets.php - used by the table and the CSV header.
5. A date: decide whether it is UTC or wall-clock, and format it in displayValue() and in api/tickets/table_export.php to match.

---

8. Testing

Test	Needs	What it covers
tests/data-table-server.php	Chrome	The engine's server mode against a fake server: paging, global sorting, stale responses, failure and Retry, the export's query - and that a table without server still loads all its rows
tests/ticket-workspace-table.php	database + site	The export policy (default, admins, nobody), unauthorised settings changes refused, the table's tickets equal the message list's, 525 rows across pages and CSV batches, sort and filter validation (an SQL expression as a sort is a 400), formula protection, date labelling, saved-view visibility
tests/ticket-table-live.php	database + site + Chrome	The real page: folder kept, the reading pane, selection, right-click, drag, search, paging, export

The database tests need FREEITSM_TEST_URL and FREEITSM_TEST_DATABASE, and should run against a disposable install - see the workspace guide's testing section.

---

Related pages

• The ticket table · Saved table views - Developer Guide · Inbox.js - Developer Guide
• Ticket workspace - Developer Guide · Review (#211-#213) · The folder said 99 and the list showed 96 - why the query is driven from tickets