Data Table

These are fully featured data tables that contain functionality to navigate pages, search, sort and delete rows via AJAX, and can be created using the create table CLI command, such as:

apex create table <PACKAGE> <ALIAS> [--dbtable <TABLE_NAME>]

apex create table my-shop orders --dbtable shop_orders

Using the above example, a new file has been created at /src/MyShop/Opus/DataTables/Orders.php. The command also accepts an optional --dbtable flag, and if defined will be used for the various occurrences of a table name within the PHP class, and otherwise the system will try to generate the table name itself.

The PHP class contains various properties which are described below.

Property	Type	Description
$columns	array	Associative array defining the columns of the table, keys being the alias, and values being the name displayed within the web browser.
$sortable	array	One dimensional array of column aliases that can be sorted in ascending / descending order. Must be capable of using the alias within the ORDER BY clause of the SQL statement.
$rows_per_page	int	Number of rows per-page to display within the table.
$has_search	bool	Whether or not an AJAX powered search box appears in the top-right corner of the table.
$form_field	string	The type of form field to place as the first, left-most column. Supported values are, "none", "radio", "checkbox".
$form_name	string	The name of the radio / checkbox form field.
$form_value	string	The column alias that will be the value of the form field.
$delete_button	string	Optional, and if defined a "Delete Checked Rows" button will appear bottom center of the table with this as the label.
$delete_dbtable	string	The table name to delete from.
$delete_dbcolumn	string	The column within the table to use during deletion. Must be the column where the $form_value is stored.

__construct( Method

Has the $attr property which is an associative array that contains all attributes within the HTML tag that triggered the data table. The instances of a few services are also injected as constructor property promotion.

Use this method to assign different properties that will be used in the below methods, such as the uuid, status, type, group_id, or whatever other criteria is being displayed. This method can also be used to show / hide columns as necessary, for example:

public function __construct(
    private array $attr, 
    private App $app, 
    private Db $db,
    private Convert $convert
) { 
    $this->uuid = $attr['uuid'] ?? '';
    $this->status = $attr['stats'] ?? 'approved';

    if ($this->uuid != '') {
        unset($columns['user']);
    }
}

Above sets the uuid and status properties based on the attributes within the HTML tag that displayed the data table. If a uuid is defined, the user column is hidden from display, as it means we're viewing items from one user only.

getTotal() Method

This method returns an integer of the total rows within the database, and is used to generate the pagination links. If the $has_search property is set to false, you can remove the if conditional altogether, for example:

public function getTotal(string $search_term = ''):int 
{

    // Get total
    $total = $this->db->getField("SELECT count(*) FROM -shop_orders");
    return (int) $total;
}

We can also get the total based on whether or not a uuid is defined within the HTML tag, meaning we're viewing orders for a single user, for example:

public function getTotal(string $search_term = ''):int 
{

    // Get total
    if ($this->uuid == '') {
        $total = $this->db->getField("SELECT count(*) FROM -shop_orders WHERE status = %s", $this->status);
    } else {
        $total = $this->db->getField("SELECT count(*) FROM -shop_orders WHERE status = %s AND uuid = %s", $this->status, $this->uuid);
        }

    // Return
    return (int) $total;
}

Alternatively, if the $has_search property is set to true, this method may look something like:

public function getTotal(string $search_term = ''):int 
{

    // Get total
    if ($search_term != '') { 
        $total = $this->db->getField("SELECT count(*) FROM shop_products WHERE name LIKE %ls OR description LIKE %ls", $search_term, $search_term);
    } else { 
        $total = $this->db->getField("SELECT count(*) FROM shop_products");
    }

    // Return
    return (int) $total;
}

getRows() Method

This method retrives the exact rows to display within the web browser, and while looping over them passes them through the formatRow() method to format them for display within the web browser. There are three parameters accepted, which are explained below.

Parameter	Type	Description
$start	int	Offset to start at, used for pagination.
$search_term	string	Only applicable if $has_search property is set to true, and user is searching the table.
$order_by string		Full order by clause, column and direction.

Generally, you will want to modify the default value of $order_by as desired (eg. 'created_at DESC'). The first section of this method retrives the rows in much the same way as the getTotal() method, except it gets the full row instead of just the count. For example:

public function getRows(int $start = 0, string $search_term = '', string $order_by = 'created_at desc'):array 
{

    // Get rows
    if ($this->uuid == '') {
        $rows = $this->db->query("SELECT * FROM shop_orders WHERE status = %s ORDER BY $order_by LIMIT $start,$this->rows_per_page", $this->status);
    } else { 
        $rows = $this->db->query("SELECT * FROM shop_orders WHERE status = %s AND uuid = %s ORDER BY $order_by LIMIT $start,$this->rows_per_page", $this->status, $this->uuid   );
    }

    // Go through rows
    $results = [];
    foreach ($rows as $row) { 
        $results[] = $this->formatRow($row);
    }

    // Return
    return $results;
}

The above method will retrive all necessary rows from the database epending on whether or not a uuid has been specified. It then loops through the raw rows and formats them as necessary for display within the web browser, then returns an array of associative arrays representing all rows to display.

formatRow() Method

This method takes in as associative array consisting of one raw row of data, and formats it as necessary to be displayed within the web browser. For example:

public function formatRow(array $row):array
{

    // Format
    $row['created_at'] = $this->convert->date($row['created_at'], true);
    $row['amount'] = $this->convert->money($row['amount']);
    $row['status'] = ucwords($row['status']);

    // Return
    return $row;
}

Display Tables on Web Pages

You can display a data table on any webpage rendered by Apex with a simple HTML tag, such as:

<s:function alias="display_table" table="<PACKAGE>.<ALIAS>">

You may also add additional attributes into the HTML tag which become the Attr property of the PHP class, for example:

<s:function alias="display_table" table="my-shop.orders" status="approved" uuid="u:883">