DB_Stats_Query: performing a query on a stats table

In order to perform a query on a stats table generated by DB_Stats, we can use the DB_Stats_Query.

The theory behind queries on stats tables

There are essentially 3 types of actions we can take on each column of a stats table:

• Filter on that column (for instance, display only the year 2008)
• Do not filter and display everything (display all the years)
• Sum up the content of the column (let's display the sum for all the years).

Please note that it is the responsibility of the developer to query according to the dimensions. For instance, if one dimension is "year->month->day", the developer CANNOT sum up years but filter on the month: You must first apply filters, then sum the latest columns of the dimension if you want.

+--------------------------------------------------------+
| DB_StatsQuery                                          |
+--------------------------------------------------------+
| + setDbStats(DB_Stats dbStats)                         |
| + addFilter(DB_StatFilter filter)                      |
| + query()                                              |
+--------------------------------------------------------+

In order to perform a query, we must first create an instance of the DB_StatsQuery object. This object must be connected directly to the DB_Stats object we will perform requests on. Once this is done, we just need to add filters. There are 3 classes implementing the DB_StatFilter interface:

• DB_ValueFilter: Filter on a column
• DB_SumFilter: Sum up the content of the column
• DB_AllValuesFilter: Do not filter and display everything

Filter on a column

We can filter on a column, for a specific value:

+--------------------------------------------------------+
| DB_ValueFilter                                         |
+--------------------------------------------------------+
| + setColumnName(string name)                           |
| + setValue(string value)                               |
+--------------------------------------------------------+

For instance, if we want to get results only for the year 2009, we will use:

$query = new DB_Stats_Query();
$query->setDbStats($dbStats);

$yearFilter = new DB_ValueFilter();
$yearFilter->setColumnName("year");
$yearFilter->setValue("2009");

$query->addFilter($yearFilter);

Displays all the values of a column

In order to get all the possible values (and subtotals) for a column, we use the DB_AllValuesFilter

+--------------------------------------------------------+
| DB_AllValuesFilter                                     |
+--------------------------------------------------------+
| + setColumnName(string name)                           |
+--------------------------------------------------------+

For instance:

$monthFilter = new DB_AllValuesFilter();
$monthFilter->setColumnName("month");

$query->addFilter($monthFilter);

Default filter

A third type of Filter allow to sum up the values of a column and return the total : the DB_SumFilter.

+--------------------------------------------------------+
| DB_SumFilter                                           |
+--------------------------------------------------------+
| + setColumnName(string name)                           |
+--------------------------------------------------------+

Please note that creating DB_SumFilter is optional. Indeed, if a column is not stated in any filter, by default, a DB_SumFilter will be applied on that column.

Running the query

Running a query is straightforward once everything is configured. You just need to run the query method.

$results = $query->query();

Results are returned as an array of rows. Each row is an associative array containing the name of the column as a key, and its value as the value.

For instance, with the previoussly defined year and month filter, the result will be aggregated values for each different month of the year 2009:

The following code :

 foreach ($results as $row) {
    $i++;
    echo $i.' - '.'For patients created on Month : '.$row["month"].', aggregated weight is: '.$row["sumweight"]
        .', aggredated size is : '.$row["sumsize"].', while average age is : '.$row["sumage"]/$row["cnt"];
    echo "<br/>";
 }

Will display :

1 - For patients created on Month : 6, aggredated wieght is: 50, aggredated size : 170, while average age is : 25
2 - For patients created on Month : 7, aggredated wieght is: 145, aggredated size : 355, while average age is : 29.25
3 - For patients created on Month : 8, aggredated wieght is: 120, aggredated size : 360, while average age is : 47,5

Found a typo? Something is wrong in this documentation? Just fork and edit it!