Piwik\

DataTable

The primary data structure used to store analytics data in Matomo (formerly Piwik).

The Basics

DataTables consist of rows and each row consists of columns. A column value can be a numeric, a string or an array.

Every row has an ID. The ID is either the index of the row or ID_SUMMARY_ROW.

DataTables are hierarchical data structures. Each row can also contain an additional nested sub-DataTable (commonly referred to as a 'subtable').

Both DataTables and DataTable rows can hold metadata. DataTable metadata is information regarding all the data, such as the site or period that the data is for. Row metadata is information regarding that row, such as a browser logo or website URL.

Finally, all DataTables contain a special summary row. This row, if it exists, is always at the end of the DataTable.

Populating DataTables

Data can be added to DataTables in three different ways. You can either:

  1. create rows one by one and add them through addRow() then truncate if desired,
  2. create an array of DataTable\Row instances or an array of arrays and add them using addRowsFromArray() or addRowsFromSimpleArray() then truncate if desired,
  3. or set the maximum number of allowed rows (with setMaximumAllowedRows()) and add rows one by one.

If you want to eventually truncate your data (standard practice for all Matomo plugins), the third method is the most memory efficient. It is, unfortunately, not always possible to use since it requires that the data be sorted before adding.

Manipulating DataTables

There are two ways to manipulate a DataTable. You can either:

  1. manually iterate through each row and manipulate the data,
  2. or you can use predefined filters.

A filter is a class that has a 'filter' method which will manipulate a DataTable in some way. There are several predefined Filters that allow you to do common things, such as,

  • add a new column to each row,
  • add new metadata to each row,
  • modify an existing column value for each row,
  • sort an entire DataTable,
  • and more.

Using these filters instead of writing your own code will increase code clarity and reduce code redundancy. Additionally, filters have the advantage that they can be applied to DataTable\Map instances. So you can visit every DataTable in a Map without having to write a recursive visiting function.

All predefined filters exist in the Piwik\DataTable\BaseFilter namespace.

Note: For convenience, anonymous functions can be used as DataTable filters.

Applying Filters

Filters can be applied now (via filter()), or they can be applied later (via queueFilter()).

Filters that sort rows or manipulate the number of rows should be applied right away. Non-essential, presentation filters should be queued.

Learn more

Examples

Populating a DataTable

// adding one row at a time
$dataTable = new DataTable();
$dataTable->addRow(new Row(array(
    Row::COLUMNS => array('label' => 'thing1', 'nb_visits' => 1, 'nb_actions' => 1),
    Row::METADATA => array('url' => 'http://thing1.com')
)));
$dataTable->addRow(new Row(array(
    Row::COLUMNS => array('label' => 'thing2', 'nb_visits' => 2, 'nb_actions' => 2),
    Row::METADATA => array('url' => 'http://thing2.com')
)));

// using an array of rows
$dataTable = new DataTable();
$dataTable->addRowsFromArray(array(
    array(
        Row::COLUMNS => array('label' => 'thing1', 'nb_visits' => 1, 'nb_actions' => 1),
        Row::METADATA => array('url' => 'http://thing1.com')
    ),
    array(
        Row::COLUMNS => array('label' => 'thing2', 'nb_visits' => 2, 'nb_actions' => 2),
        Row::METADATA => array('url' => 'http://thing2.com')
    )
));

// using a "simple" array
$dataTable->addRowsFromSimpleArray(array(
    array('label' => 'thing1', 'nb_visits' => 1, 'nb_actions' => 1),
    array('label' => 'thing2', 'nb_visits' => 2, 'nb_actions' => 2)
));

Getting & setting metadata

$dataTable = \Piwik\Plugins\Referrers\API::getInstance()->getSearchEngines($idSite = 1, $period = 'day', $date = '2007-07-24');
$oldPeriod = $dataTable->metadata['period'];
$dataTable->metadata['period'] = Period\Factory::build('week', Date::factory('2013-10-18'));

Serializing & unserializing

$maxRowsInTable = Config::getInstance()->General['datatable_archiving_maximum_rows_standard'];j

$dataTable = // ... build by aggregating visits ...
$serializedData = $dataTable->getSerialized($maxRowsInTable, $maxRowsInSubtable = $maxRowsInTable,
                                            $columnToSortBy = Metrics::INDEX_NB_VISITS);

$serializedDataTable = $serializedData[0];
$serailizedSubTable = $serializedData[$idSubtable];

Filtering for an API method

public function getMyReport($idSite, $period, $date, $segment = false, $expanded = false)
{
    $dataTable = Archive::createDataTableFromArchive('MyPlugin_MyReport', $idSite, $period, $date, $segment, $expanded);
    $dataTable->filter('Sort', array(Metrics::INDEX_NB_VISITS, 'desc', $naturalSort = false, $expanded));
    $dataTable->queueFilter('ColumnCallbackAddMetadata', array('label', 'url', __NAMESPACE__ . '\getUrlFromLabelForMyReport'));
    return $dataTable;
}

Constants

This class defines the following constants:

COLUMN_AGGREGATION_OPS_METADATA_NAME

This metadata value must be an array that maps column names with valid operations. Valid aggregation operations are:

  • 'skip': do nothing
  • 'max': does max($column1, $column2)
  • 'min': does min($column1, $column2)
  • 'sum': does $column1 + $column2

See addDataTable() and Row::sumRow() for more information.

ID_ARCHIVED_METADATA_ROW

This allows us to save datatable metadata in archive data.

EXTRA_PROCESSED_METRICS_METADATA_NAME

These metrics will be added in addition to the ones specified in the table's associated Report class.

Methods

The class defines the following methods:

__construct()

Constructor. Creates an empty DataTable.

Signature

__destruct()

Destructor. Makes sure DataTable memory will be cleaned up.

Signature

  • It does not return anything or a mixed result.

__clone()

Clone. Called when cloning the datatable. We need to make sure to create a new datatableId.

If we do not increase tableId it can result in segmentation faults when destructing a datatable.

Signature

  • It does not return anything or a mixed result.

setLabelsHaveChanged()

Signature

  • It does not return anything or a mixed result.

sort()

Sorts the DataTable rows using the supplied callback function.

Signature

  • It accepts the following parameter(s):
    • $functionCallback (string) — A comparison callback compatible with usort.
    • $columnSortedBy (string) — The column name $functionCallback sorts by. This is stored so we can determine how the DataTable was sorted in the future.
  • It does not return anything or a mixed result.

setTotalsRow()

Signature

  • It accepts the following parameter(s):

    • $totalsRow (Row) —
  • It does not return anything or a mixed result.

getTotalsRow()

Signature

  • It does not return anything or a mixed result.

getSummaryRow()

Signature

  • It does not return anything or a mixed result.

getSortedByColumnName()

Returns the name of the column this table was sorted by (if any).

See sort().

Signature

  • Returns: false|string — The sorted column name or false if none.

enableRecursiveSort()

Enables recursive sorting. If this method is called sort() will also sort all subtables.

Signature

  • It does not return anything or a mixed result.

enableRecursiveFilters()

Enables recursive filtering. If this method is called then the filter() method will apply filters to every subtable in addition to this instance.

Signature

  • It does not return anything or a mixed result.

filter()

Applies a filter to this datatable.

If enableRecursiveFilters() was called, the filter will be applied to all subtables as well.

Signature

  • It accepts the following parameter(s):
    • $className (string|Closure) — Class name, eg. "Sort" or "Piwik\DataTable\Filters\Sort". If no namespace is supplied,Piwik\DataTable\BaseFilter` is assumed. This parameter can also be a closure that takes a DataTable as its first parameter.
    • $parameters (array) — Array of extra parameters to pass to the filter.
  • It does not return anything or a mixed result.

filterSubtables()

Applies a filter to all subtables but not to this datatable.

Signature

  • It accepts the following parameter(s):
    • $className (string|Closure) — Class name, eg. "Sort" or "Piwik\DataTable\Filters\Sort". If no namespace is supplied,Piwik\DataTable\BaseFilter` is assumed. This parameter can also be a closure that takes a DataTable as its first parameter.
    • $parameters (array) — Array of extra parameters to pass to the filter.
  • It does not return anything or a mixed result.

queueFilterSubtables()

Adds a filter and a list of parameters to the list of queued filters of all subtables. These filters will be executed when applyQueuedFilters() is called.

Filters that prettify the column values or don't need the full set of rows should be queued. This way they will be run after the table is truncated which will result in better performance.

Signature

  • It accepts the following parameter(s):
    • $className (string|Closure) — The class name of the filter, eg. 'Limit'.
    • $parameters (array) — The parameters to give to the filter, eg. array($offset, $limit) for the Limit filter.
  • It does not return anything or a mixed result.

queueFilter()

Adds a filter and a list of parameters to the list of queued filters. These filters will be executed when applyQueuedFilters() is called.

Filters that prettify the column values or don't need the full set of rows should be queued. This way they will be run after the table is truncated which will result in better performance.

Signature

  • It accepts the following parameter(s):
    • $className (string|Closure) — The class name of the filter, eg. 'Limit'.
    • $parameters (array) — The parameters to give to the filter, eg. array($offset, $limit) for the Limit filter.
  • It does not return anything or a mixed result.

applyQueuedFilters()

Applies all filters that were previously queued to the table. See queueFilter() for more information.

Signature

  • It does not return anything or a mixed result.

addDataTable()

Sums a DataTable to this one.

This method will sum rows that have the same label. If a row is found in $tableToSum whose label is not found in $this, the row will be added to $this.

If the subtables for this table are loaded, they will be summed as well.

Rows are summed together by summing individual columns. By default columns are summed by adding one column value to another. Some columns cannot be aggregated this way. In these cases, the COLUMN_AGGREGATION_OPS_METADATA_NAME metadata can be used to specify a different type of operation.

Signature

  • It accepts the following parameter(s):

  • It does not return anything or a mixed result.

  • It throws one of the following exceptions:

getRowFromLabel()

Returns the Row whose 'label' column is equal to $label.

This method executes in constant time except for the first call which caches row label => row ID mappings.

Signature

  • It accepts the following parameter(s):

    • $label (string) — 'label' column value to look for.
  • Returns: Row|false — The row if found, false if otherwise.

getRowIdFromLabel()

Returns the row id for the row whose 'label' column is equal to $label.

This method executes in constant time except for the first call which caches row label => row ID mappings.

Signature

  • It accepts the following parameter(s):

    • $label (string) — 'label' column value to look for.
  • Returns: int — The row ID.

getEmptyClone()

Returns an empty DataTable with the same metadata and queued filters as $this one.

Signature

  • It accepts the following parameter(s):
    • $keepFilters (bool) — Whether to pass the queued filter list to the new DataTable or not.
  • It returns a DataTable value.

getRowFromId()

Returns a row by ID. The ID is either the index of the row or ID_SUMMARY_ROW.

Signature

  • It accepts the following parameter(s):

    • $id (int) — The row ID.
  • Returns: Row|false — The Row or false if not found.

getRowFromIdSubDataTable()

Returns the row that has a subtable with ID matching $idSubtable.

Signature

  • It accepts the following parameter(s):

    • $idSubTable (int) — The subtable ID.
  • Returns: Row|false — The row or false if not found

addRow()

Adds a row to this table.

If setMaximumAllowedRows() was called and the current row count is at the maximum, the new row will be summed to the summary row. If there is no summary row, this row is set as the summary row.

Signature

  • It accepts the following parameter(s):

    • $row (Row) —
  • Returns: Row$row or the summary row if we're at the maximum number of rows.

addSummaryRow()

Sets the summary row.

Note: A DataTable can have only one summary row.

Signature

  • It accepts the following parameter(s):

    • $row (Row) —
  • Returns: Row — Returns $row.

getId()

Returns the DataTable ID.

Signature

  • It returns a int value.

addRowFromArray()

Adds a new row from an array.

You can add row metadata with this method.

Signature

  • It accepts the following parameter(s):
    • $row (array) — eg. array(Row::COLUMNS => array('visits' => 13, 'test' => 'toto'), Row::METADATA => array('mymetadata' => 'myvalue'))
  • It does not return anything or a mixed result.

addRowFromSimpleArray()

Adds a new row a from an array of column values.

Row metadata cannot be added with this method.

Signature

  • It accepts the following parameter(s):
    • $row (array) — eg. array('name' => 'google analytics', 'license' => 'commercial')
  • It does not return anything or a mixed result.

getRows()

Returns the array of Rows.

Internal logic in Matomo core should avoid using this method as it is time and memory consuming when being executed thousands of times. The alternative is to use getRowsWithoutSummaryRow() + get the summary row manually.

Signature

  • It returns a Row[] value.

getColumn()

Returns an array containing all column values for the requested column.

Signature

  • It accepts the following parameter(s):

    • $name (string) — The column name.
  • Returns: array — The array of column values.

getColumnsStartingWith()

Returns an array containing all column values of columns whose name starts with $name.

Signature

  • It accepts the following parameter(s):

    • $namePrefix (string) — The column name prefix.
  • Returns: array — The array of column values.

getColumns()

Returns the names of every column this DataTable contains. This method will return the columns of the first row with data and will assume they occur in every other row as well.

_ Note: If column names still use their in-database INDEX values (@see Metrics), they will be converted to their string name in the array result._

Signature

  • Returns: array — Array of string column names.

getRowsMetadata()

Returns an array containing the requested metadata value of each row.

Signature

  • It accepts the following parameter(s):
    • $name (string) — The metadata column to return.
  • It returns a array value.

deleteRowsMetadata()

Delete row metadata by name in every row.

Signature

  • It accepts the following parameter(s):

    • $name

    • $deleteRecursiveInSubtables (bool) —

  • It does not return anything or a mixed result.

getRowsCount()

Returns the number of rows in the table including the summary row.

Signature

  • It returns a int value.

getFirstRow()

Returns the first row of the DataTable.

Signature

  • Returns: Row|false — The first row or false if it cannot be found.

getLastRow()

Returns the last row of the DataTable. If there is a summary row, it will always be considered the last row.

Signature

  • Returns: Row|false — The last row or false if it cannot be found.

getRowsCountRecursive()

Returns the number of rows in the entire DataTable hierarchy. This is the number of rows in this DataTable summed with the row count of each descendant subtable.

Signature

  • It returns a int value.

deleteColumn()

Delete a column by name in every row. This change is NOT applied recursively to all subtables.

Signature

  • It accepts the following parameter(s):
    • $name (string) — Column name to delete.
  • It does not return anything or a mixed result.

__sleep()

Signature

  • It does not return anything or a mixed result.

renameColumn()

Rename a column in every row. This change is applied recursively to all subtables.

Signature

  • It accepts the following parameter(s):
    • $oldName (string) — Old column name.
    • $newName (string) — New column name.
  • It does not return anything or a mixed result.

deleteColumns()

Deletes several columns by name in every row.

Signature

  • It accepts the following parameter(s):
    • $names (array) — List of column names to delete.
    • $deleteRecursiveInSubtables (bool) — Whether to apply this change to all subtables or not.
  • It does not return anything or a mixed result.

deleteRow()

Deletes a row by ID.

Signature

  • It accepts the following parameter(s):
    • $id (int) — The row ID.
  • It does not return anything or a mixed result.
  • It throws one of the following exceptions:
    • Exception — If the row $id cannot be found.

deleteRowsOffset()

Deletes rows from $offset to $offset + $limit.

Signature

  • It accepts the following parameter(s):

    • $offset (int) — The offset to start deleting rows from.
    • $limit (int|null) — The number of rows to delete. If null all rows after the offset will be removed.
  • Returns: int — The number of rows deleted.

deleteRows()

Deletes a set of rows by ID.

Signature

  • It accepts the following parameter(s):
    • $rowIds (array) — The list of row IDs to delete.
  • It does not return anything or a mixed result.
  • It throws one of the following exceptions:

__toString()

Returns a string representation of this DataTable for convenient viewing.

Note: This uses the html DataTable renderer.

Signature

  • It returns a string value.

isEqual()

Returns true if both DataTable instances are exactly the same.

DataTables are equal if they have the same number of rows, if each row has a label that exists in the other table, and if each row is equal to the row in the other table with the same label. The order of rows is not important.

Signature

  • It accepts the following parameter(s):

  • It returns a bool value.

getSerialized()

Serializes an entire DataTable hierarchy and returns the array of serialized DataTables.

The first element in the returned array will be the serialized representation of this DataTable. Every subsequent element will be a serialized subtable.

This DataTable and subtables can optionally be truncated before being serialized. In most cases where DataTables can become quite large, they should be truncated before being persisted in an archive.

The result of this method is intended for use with the ArchiveProcessor::insertBlobRecord() method.

Signature

  • It accepts the following parameter(s):

    • $maximumRowsInDataTable (int) — If not null, defines the maximum number of rows allowed in the serialized DataTable.
    • $maximumRowsInSubDataTable (int) — If not null, defines the maximum number of rows allowed in serialized subtables.
    • $columnToSortByBeforeTruncation (string) — The column to sort by before truncating, eg, Metrics::INDEX_NB_VISITS.
    • $aSerializedDataTable (array) — Will contain all the output arrays
  • Returns: array — The array of serialized DataTables:

              array(
                  // this DataTable (the root)
                  0 => 'eghuighahgaueytae78yaet7yaetae',
    
                  // a subtable
                  1 => 'gaegae gh gwrh guiwh uigwhuige',
    
                  // another subtable
                  2 => 'gqegJHUIGHEQjkgneqjgnqeugUGEQHGUHQE',
    
                  // etc.
              );
    
  • It throws one of the following exceptions:
    • Exception — If infinite recursion detected. This will occur if a table's subtable is one of its parent tables.

addRowsFromSerializedArray()

Adds a set of rows from a serialized DataTable string.

See serialize().

Note: This function will successfully load DataTables serialized by Matomo 1.X.

Signature

  • It accepts the following parameter(s):
    • $serialized (string) — A string with the format of a string in the array returned by serialize().
  • It does not return anything or a mixed result.
  • It throws one of the following exceptions:

addRowsFromArray()

Adds multiple rows from an array.

You can add row metadata with this method.

Signature

  • It accepts the following parameter(s):
    • $array (array) — Array with the following structure array( // row1 array( Row::COLUMNS => array( col1_name => value1, col2_name => value2, ...), Row::METADATA => array( metadata1_name => value1, ...), // see Row ), // row2 array( ... ), )
  • It does not return anything or a mixed result.

addRowsFromSimpleArray()

Adds multiple rows from an array containing arrays of column values.

Row metadata cannot be added with this method.

Signature

  • It accepts the following parameter(s):
    • $array (array) — Array with the following structure: array( array( col1_name => valueA, col2_name => valueC, ...), array( col1_name => valueB, col2_name => valueD, ...), )
  • It does not return anything or a mixed result.
  • It throws one of the following exceptions:
    • Exception — if $array is in an incorrect format.

makeFromIndexedArray()

Rewrites the input $array

array ( LABEL => array(col1 => X, col2 => Y), LABEL2 => array(col1 => X, col2 => Y), )

to a DataTable with rows that look like:

array (
    array( Row::COLUMNS => array('label' => LABEL, col1 => X, col2 => Y)),
    array( Row::COLUMNS => array('label' => LABEL2, col1 => X, col2 => Y)),
)

Will also convert arrays like:

array (
    LABEL => X,
    LABEL2 => Y,
)

to:

array (
    array( Row::COLUMNS => array('label' => LABEL, 'value' => X)),
    array( Row::COLUMNS => array('label' => LABEL2, 'value' => Y)),
)

Signature

  • It accepts the following parameter(s):
    • $array (array) — Indexed array, two formats supported, see above.
    • $subtablePerLabel (array|null) — An array mapping label values with DataTable instances to associate as a subtable.
  • It returns a DataTable value.

setMaximumDepthLevelAllowedAtLeast()

Sets the maximum depth level to at least a certain value. If the current value is greater than $atLeastLevel, the maximum nesting level is not changed.

The maximum depth level determines the maximum number of subtable levels in the DataTable tree. For example, if it is set to 2, this DataTable is allowed to have subtables, but the subtables are not.

Signature

  • It accepts the following parameter(s):

    • $atLeastLevel (int) —
  • It does not return anything or a mixed result.

getMetadata()

Returns metadata by name.

Signature

  • It accepts the following parameter(s):

    • $name (string) — The metadata name.
  • Returns: mixed|false — The metadata value or false if it cannot be found.

setMetadata()

Sets a metadata value by name.

Signature

  • It accepts the following parameter(s):

    • $name (string) — The metadata name.
    • $value (mixed) —
  • It does not return anything or a mixed result.

deleteMetadata()

Deletes a metadata property by name.

Signature

  • It accepts the following parameter(s):

    • $name (bool|string) — The metadata name (omit to delete all metadata)
  • Returns: bool — True if the requested metadata was deleted

getAllTableMetadata()

Returns all table metadata.

Signature

  • It returns a array value.

setMetadataValues()

Sets several metadata values by name.

Signature

  • It accepts the following parameter(s):
    • $values (array) — Array mapping metadata names with metadata values.
  • It does not return anything or a mixed result.

setAllTableMetadata()

Sets metadata, erasing existing values.

Signature

  • It accepts the following parameter(s):

    • $metadata
  • It does not return anything or a mixed result.

setMaximumAllowedRows()

Sets the maximum number of rows allowed in this datatable (including the summary row). If adding more then the allowed number of rows is attempted, the extra rows are summed to the summary row.

Signature

  • It accepts the following parameter(s):
    • $maximumAllowedRows (int) — If 0, the maximum number of rows is unset.
  • It does not return anything or a mixed result.

walkPath()

Traverses a DataTable tree using an array of labels and returns the row it finds or false if it cannot find one. The number of path segments that were successfully walked is also returned.

If $missingRowColumns is supplied, the specified path is created. When a subtable is encountered w/o the required label, a new row is created with the label, and a new subtable is added to the row.

Read http://en.wikipedia.org/wiki/Tree_(data_structure)#Traversal_methods for more information about tree walking.

Signature

  • It accepts the following parameter(s):

    • $path (array) — The path to walk. An array of label values. The first element refers to a row in this DataTable, the second in a subtable of the first row, the third a subtable of the second row, etc.
    • $missingRowColumns (array|bool) — The default columns to use when creating new rows. If this parameter is supplied, new rows will be created for path labels that cannot be found.
    • $maxSubtableRows (int) — The maximum number of allowed rows in new subtables. New subtables are only created if $missingRowColumns is provided.
  • Returns: array — First element is the found row or false. Second element is the number of path segments walked. If a row is found, this will be == to count($path). Otherwise, it will be the index of the path segment that we could not find.

mergeSubtables()

Returns a new DataTable in which the rows of this table are replaced with the aggregatated rows of all its subtables.

Signature

  • It accepts the following parameter(s):
    • $labelColumn (string|bool) — If supplied the label of the parent row will be added to a new column in each subtable row. If set to, 'label' each subtable row's label will be prepended w/ the parent row's label. So 'child_label' becomes 'parent_label - child_label'.
    • $useMetadataColumn (bool) — If true and if $labelColumn is supplied, the parent row's label will be added as metadata and not a new column.
  • It returns a DataTable value.

makeFromSimpleArray()

Returns a new DataTable created with data from a 'simple' array.

See addRowsFromSimpleArray().

Signature

  • It accepts the following parameter(s):

    • $array (array) —
  • It returns a DataTable value.

fromSerializedArray()

Creates a new DataTable instance from a serialized DataTable string.

See getSerialized() and addRowsFromSerializedArray() for more information on DataTable serialization.

Signature

  • It accepts the following parameter(s):

    • $data (string) —
  • It returns a DataTable value.

clearQueuedFilters()

Unsets all queued filters.

Signature

  • It does not return anything or a mixed result.

getQueuedFilters()

Signature

  • It does not return anything or a mixed result.

getIterator()

Signature

offsetExists()

Signature

  • It accepts the following parameter(s):

    • $offset
  • It returns a bool value.

offsetGet()

Signature

  • It accepts the following parameter(s):

    • $offset
  • It returns a Row value.

offsetSet()

Signature

  • It accepts the following parameter(s):

    • $offset

    • $value

  • It returns a void value.

offsetUnset()

Signature

  • It accepts the following parameter(s):

    • $offset
  • It returns a void value.