Custom SQL Reports

table() vs. query()

->table($name) backs a grid with one real, writable table. ->query($sql) is the alternative for anything that doesn't map to a single table - joins, aggregates, computed columns spanning several tables:

public function query(string $sql): self

$sql is used verbatim, wrapped as a derived table by the API layer - the exact same trust level where_raw()/subselect()'s own raw-SQL escape hatches have. It must be a plain string you wrote in your own page's PHP, never anything built from request input.

Report mode is read-only

A query-mode grid has no Add/Edit/Delete, no bulk actions, and no inline-edit - it's read-only by nature, since there's no single table a write could unambiguously target. This is enforced both client-side and server-side: the API rejects POST/PUT/DELETE against a query-mode route outright, regardless of what the client sends. Only list/search/sort/paginate/export remain.

columns() and route() are still required

A query-mode grid never opens a database connection of its own to discover columns - its column list is only ever what ->columns() says. ->route() is likewise required since there's no table name to default one from.

A JOIN-based example

$xcrud = Xcrud::get_instance();
$xcrud->route('order-summary');
$xcrud->query(
    'SELECT o.orderNumber, o.orderDate, o.status, c.customerName, c.country,
            COUNT(od.id) AS itemCount,
            SUM(od.quantityOrdered * od.priceEach) AS orderTotal
     FROM orders o
     JOIN customers c ON c.customerNumber = o.customerNumber
     JOIN orderdetails od ON od.orderNumber = o.orderNumber
     GROUP BY o.orderNumber, o.orderDate, o.status, c.customerName, c.country'
);
$xcrud->columns('orderNumber,orderDate,status,customerName,country,itemCount,orderTotal');
$xcrud->order_by('orderTotal', 'desc');
$xcrud->title('Order Summary Report');

echo $xcrud->render();

$sql's own column aliases and ->columns()'s names are never cross-checked against each other (no DB access happens at render time) - a mismatch surfaces as an error on the grid's first real request, not a render-time exception the way an unknown column name in order_by()/label()/where() would. Double-check the two lists by hand.

A trailing ORDER BY inside $sql with no LIMIT may be silently dropped once wrapped as a derived table - harmless, since the grid's own sort/->order_by() always applies an outer ORDER BY regardless, but don't rely on an inner one doing anything on its own.