/**
* @group Core
*/
public function testBasic()
{
$query = new RankingQuery();
$query->addLabelColumn('label');
$query->addColumn('column');
$query->addColumn('columnSum', 'sum');
$query->setLimit(10);
$innerQuery = "SELECT label, column, columnSum FROM myTable";
$expected = "\n\t\t\tSELECT\n\t\t\t\tCASE\n\t\t\t\t\tWHEN counter = 11 THEN 'Others'\n\t\t\t\t\tELSE `label`\n\t\t\t\tEND AS `label`,\n\t\t\t\t`column`,\n\t\t\t\tsum(`columnSum`) AS `columnSum`\n\t\t\tFROM ( \n\t\t\t\tSELECT\n\t\t\t\t\t`label`,\n\t\t\t\t\tCASE \n\t\t\t\t\t\tWHEN @counter = 11 THEN 11\n\t\t\t\t\t\tELSE @counter:=@counter+1\n\t\t\t\t\tEND AS counter,\n\t\t\t\t\t`column`,\n\t\t\t\t\t`columnSum`\n\t\t\t\tFROM\n\t\t\t\t\t( SELECT @counter:=0 ) initCounter,\n\t\t\t\t\t( SELECT label, column, columnSum FROM myTable ) actualQuery\n\t\t\t ) AS withCounter\n\t\t\tGROUP BY counter\n\t\t";
$this->checkQuery($query, $innerQuery, $expected);
}
/**
* Time per action
*/
protected function archiveDayActionsTime($rankingQueryLimit)
{
$rankingQuery = false;
if ($rankingQueryLimit > 0) {
$rankingQuery = new RankingQuery($rankingQueryLimit);
$rankingQuery->setOthersLabel(DataTable::LABEL_SUMMARY_ROW);
$rankingQuery->addLabelColumn('idaction');
$rankingQuery->addColumn(Metrics::INDEX_PAGE_SUM_TIME_SPENT, 'sum');
$rankingQuery->partitionResultIntoMultipleGroups('type', array_keys($this->actionsTablesByType));
$extraSelects = "log_action.type, log_action.name, count(*) as `" . Metrics::INDEX_PAGE_NB_HITS . "`,";
$from = array("log_link_visit_action", array("table" => "log_action", "joinOn" => "log_link_visit_action.%s = log_action.idaction"));
$orderBy = "`" . Metrics::INDEX_PAGE_NB_HITS . "` DESC, log_action.name ASC";
} else {
$extraSelects = false;
$from = "log_link_visit_action";
$orderBy = false;
}
$select = "log_link_visit_action.%s as idaction, {$extraSelects}\n\t\t\t\tsum(log_link_visit_action.time_spent_ref_action) as `" . Metrics::INDEX_PAGE_SUM_TIME_SPENT . "`";
$where = "log_link_visit_action.server_time >= ?\n\t\t\t\tAND log_link_visit_action.server_time <= ?\n\t\t \t\tAND log_link_visit_action.idsite = ?\n\t\t \t\tAND log_link_visit_action.time_spent_ref_action > 0\n\t\t \t\tAND log_link_visit_action.%s > 0" . $this->getWhereClauseActionIsNotEvent();
$groupBy = "log_link_visit_action.%s, idaction";
$this->archiveDayQueryProcess($select, $from, $where, $orderBy, $groupBy, "idaction_url_ref", $rankingQuery);
$this->archiveDayQueryProcess($select, $from, $where, $orderBy, $groupBy, "idaction_name_ref", $rankingQuery);
}
/**
* Executes and returns a query aggregating action data (everything in the log_action table) and returns
* a DB statement that can be used to iterate over the result
*
* <a name="queryActionsByDimension-result-set"></a>
* **Result Set**
*
* Each row of the result set represents an aggregated group of actions. The following columns
* are in each aggregate row:
*
* - **{@link Piwik\Metrics::INDEX_NB_UNIQ_VISITORS}**: The total number of unique visitors that performed
* the actions in this group.
* - **{@link Piwik\Metrics::INDEX_NB_VISITS}**: The total number of visits these actions belong to.
* - **{@link Piwik\Metrics::INDEX_NB_ACTIONS}**: The total number of actions in this aggregate group.
*
* Additional data can be selected through the `$additionalSelects` parameter.
*
* _Note: The metrics calculated by this query can be customized by the `$metrics` parameter._
*
* @param array|string $dimensions One or more SELECT fields that will be used to group the log_action
* rows by. This parameter determines which log_action rows will be
* aggregated together.
* @param bool|string $where Additional condition for the WHERE clause. Can be used to filter
* the set of visits that are considered for aggregation.
* @param array $additionalSelects Additional SELECT fields that are not included in the group by
* clause. These can be aggregate expressions, eg, `SUM(somecol)`.
* @param bool|array $metrics The set of metrics to calculate and return. If `false`, the query will select
* all of them. The following values can be used:
*
* - {@link Piwik\Metrics::INDEX_NB_UNIQ_VISITORS}
* - {@link Piwik\Metrics::INDEX_NB_VISITS}
* - {@link Piwik\Metrics::INDEX_NB_ACTIONS}
* @param bool|\Piwik\RankingQuery $rankingQuery
* A pre-configured ranking query instance that will be used to limit the result.
* If set, the return value is the array returned by {@link Piwik\RankingQuery::execute()}.
* @param bool|string $joinLogActionOnColumn One or more columns from the **log_link_visit_action** table that
* log_action should be joined on. The table alias used for each join
* is `"log_action$i"` where `$i` is the index of the column in this
* array.
*
* If a string is used for this parameter, the table alias is not
* suffixed (since there is only one column).
* @return mixed A Zend_Db_Statement if `$rankingQuery` isn't supplied, otherwise the result of
* {@link Piwik\RankingQuery::execute()}. Read [this](#queryEcommerceItems-result-set)
* to see what aggregate data is calculated by the query.
* @api
*/
public function queryActionsByDimension($dimensions, $where = '', $additionalSelects = array(), $metrics = false, $rankingQuery = null, $joinLogActionOnColumn = false)
{
$tableName = self::LOG_ACTIONS_TABLE;
$availableMetrics = $this->getActionsMetricFields();
$select = $this->getSelectStatement($dimensions, $tableName, $additionalSelects, $availableMetrics, $metrics);
$from = array($tableName);
$where = $this->getWhereStatement($tableName, self::ACTION_DATETIME_FIELD, $where);
$groupBy = $this->getGroupByStatement($dimensions, $tableName);
$orderBy = false;
if ($joinLogActionOnColumn !== false) {
$multiJoin = is_array($joinLogActionOnColumn);
if (!$multiJoin) {
$joinLogActionOnColumn = array($joinLogActionOnColumn);
}
foreach ($joinLogActionOnColumn as $i => $joinColumn) {
$tableAlias = 'log_action' . ($multiJoin ? $i + 1 : '');
if (strpos($joinColumn, ' ') === false) {
$joinOn = $tableAlias . '.idaction = ' . $tableName . '.' . $joinColumn;
} else {
// more complex join column like if (...)
$joinOn = $tableAlias . '.idaction = ' . $joinColumn;
}
$from[] = array('table' => 'log_action', 'tableAlias' => $tableAlias, 'joinOn' => $joinOn);
}
}
if ($rankingQuery) {
$orderBy = '`' . Metrics::INDEX_NB_ACTIONS . '` DESC';
}
$query = $this->generateQuery($select, $from, $where, $groupBy, $orderBy);
if ($rankingQuery !== null) {
$sumColumns = array_keys($availableMetrics);
if ($metrics) {
$sumColumns = array_intersect($sumColumns, $metrics);
}
$rankingQuery->addColumn($sumColumns, 'sum');
return $rankingQuery->execute($query['sql'], $query['bind']);
}
return $this->getDb()->query($query['sql'], $query['bind']);
}
private function aggregateDayInteractions()
{
$select = "\n log_action_content_name.name as contentName,\n log_action_content_interaction.name as contentInteraction,\n log_action_content_piece.name as contentPiece,\n\n\t\t\t\tcount(*) as `" . Metrics::INDEX_CONTENT_NB_INTERACTIONS . "`\n ";
$from = array("log_link_visit_action", array("table" => "log_action", "tableAlias" => "log_action_content_piece", "joinOn" => "log_link_visit_action.idaction_content_piece = log_action_content_piece.idaction"), array("table" => "log_action", "tableAlias" => "log_action_content_interaction", "joinOn" => "log_link_visit_action.idaction_content_interaction = log_action_content_interaction.idaction"), array("table" => "log_action", "tableAlias" => "log_action_content_name", "joinOn" => "log_link_visit_action.idaction_content_name = log_action_content_name.idaction"));
$where = "log_link_visit_action.server_time >= ?\n AND log_link_visit_action.server_time <= ?\n AND log_link_visit_action.idsite = ?\n AND log_link_visit_action.idaction_content_name IS NOT NULL\n AND log_link_visit_action.idaction_content_interaction IS NOT NULL";
$groupBy = "log_action_content_piece.idaction,\n log_action_content_interaction.idaction,\n log_action_content_name.idaction";
$orderBy = "`" . Metrics::INDEX_CONTENT_NB_INTERACTIONS . "` DESC";
$rankingQueryLimit = ArchivingHelper::getRankingQueryLimit();
$rankingQuery = null;
if ($rankingQueryLimit > 0) {
$rankingQuery = new RankingQuery($rankingQueryLimit);
$rankingQuery->setOthersLabel(DataTable::LABEL_SUMMARY_ROW);
$rankingQuery->addLabelColumn(array('contentPiece', 'contentInteraction', 'contentName'));
$rankingQuery->addColumn(array(Metrics::INDEX_CONTENT_NB_INTERACTIONS), 'sum');
}
$resultSet = $this->archiveDayQueryProcess($select, $from, $where, $groupBy, $orderBy, $rankingQuery);
while ($row = $resultSet->fetch()) {
$this->aggregateInteractionRow($row);
}
}
protected function aggregateDayEvents()
{
$select = "\n log_action_event_category.name as eventCategory,\n log_action_event_action.name as eventAction,\n log_action_event_name.name as eventName,\n\n\t\t\t\tcount(distinct log_link_visit_action.idvisit) as `" . Metrics::INDEX_NB_VISITS . "`,\n\t\t\t\tcount(distinct log_link_visit_action.idvisitor) as `" . Metrics::INDEX_NB_UNIQ_VISITORS . "`,\n\t\t\t\tcount(*) as `" . Metrics::INDEX_EVENT_NB_HITS . "`,\n\n\t\t\t\tsum(\n\t\t\t\t\tcase when " . Action::DB_COLUMN_CUSTOM_FLOAT . " is null\n\t\t\t\t\t\tthen 0\n\t\t\t\t\t\telse " . Action::DB_COLUMN_CUSTOM_FLOAT . "\n\t\t\t\t\tend\n\t\t\t\t) as `" . Metrics::INDEX_EVENT_SUM_EVENT_VALUE . "`,\n\t\t\t\tsum( case when " . Action::DB_COLUMN_CUSTOM_FLOAT . " is null then 0 else 1 end )\n\t\t\t\t as `" . Metrics::INDEX_EVENT_NB_HITS_WITH_VALUE . "`,\n\t\t\t\tmin(" . Action::DB_COLUMN_CUSTOM_FLOAT . ") as `" . Metrics::INDEX_EVENT_MIN_EVENT_VALUE . "`,\n\t\t\t\tmax(" . Action::DB_COLUMN_CUSTOM_FLOAT . ") as `" . Metrics::INDEX_EVENT_MAX_EVENT_VALUE . "`\n ";
$from = array("log_link_visit_action", array("table" => "log_action", "tableAlias" => "log_action_event_category", "joinOn" => "log_link_visit_action.idaction_event_category = log_action_event_category.idaction"), array("table" => "log_action", "tableAlias" => "log_action_event_action", "joinOn" => "log_link_visit_action.idaction_event_action = log_action_event_action.idaction"), array("table" => "log_action", "tableAlias" => "log_action_event_name", "joinOn" => "log_link_visit_action.idaction_name = log_action_event_name.idaction"));
$where = "log_link_visit_action.server_time >= ?\n AND log_link_visit_action.server_time <= ?\n AND log_link_visit_action.idsite = ?\n AND log_link_visit_action.idaction_event_category IS NOT NULL";
$groupBy = "log_action_event_category.idaction,\n log_action_event_action.idaction,\n log_action_event_name.idaction";
$orderBy = "`" . Metrics::INDEX_NB_VISITS . "` DESC";
$rankingQueryLimit = ArchivingHelper::getRankingQueryLimit();
$rankingQuery = null;
if ($rankingQueryLimit > 0) {
$rankingQuery = new RankingQuery($rankingQueryLimit);
$rankingQuery->setOthersLabel(DataTable::LABEL_SUMMARY_ROW);
$rankingQuery->addLabelColumn(array('eventCategory', 'eventAction', 'eventName'));
$rankingQuery->addColumn(array(Metrics::INDEX_NB_UNIQ_VISITORS));
$rankingQuery->addColumn(array(Metrics::INDEX_EVENT_NB_HITS, Metrics::INDEX_NB_VISITS, Metrics::INDEX_EVENT_NB_HITS_WITH_VALUE), 'sum');
$rankingQuery->addColumn(Metrics::INDEX_EVENT_SUM_EVENT_VALUE, 'sum');
$rankingQuery->addColumn(Metrics::INDEX_EVENT_MIN_EVENT_VALUE, 'min');
$rankingQuery->addColumn(Metrics::INDEX_EVENT_MAX_EVENT_VALUE, 'max');
}
$this->archiveDayQueryProcess($select, $from, $where, $groupBy, $orderBy, $rankingQuery);
}
