/**
* @group Core
* @group RankingQuery
*/
public function testPartitionResult()
{
$query = new Piwik_RankingQuery(1000);
$query->addLabelColumn('label');
$query->partitionResultIntoMultipleGroups('partition', array(1, 2, 3));
$innerQuery = "SELECT label, partition FROM myTable";
$expected = "\n\t\t\tSELECT\n\t\t\t\tCASE\n\t\t\t\t\tWHEN counter = 1001 THEN \"Others\" \n\t\t\t\t\tELSE `label`\n\t\t\t\tEND AS `label`,\n\t\t\t\t`partition`\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 `partition` = 1 AND @counter1 = 1001 THEN 1001\n\t\t\t\t\t\tWHEN `partition` = 1 THEN @counter1:=@counter1+1\n\t\t\t\t\t\tWHEN `partition` = 2 AND @counter2 = 1001 THEN 1001\n\t\t\t\t\t\tWHEN `partition` = 2 THEN @counter2:=@counter2+1\n\t\t\t\t\t\tWHEN `partition` = 3 AND @counter3 = 1001 THEN 1001\n\t\t\t\t\t\tWHEN `partition` = 3 THEN @counter3:=@counter3+1\n\t\t\t\t\t\tELSE 0\n\t\t\t\t\tEND AS counter,\n\t\t\t\t\t`partition`\n\t\t\t\tFROM\n\t\t\t\t\t( SELECT @counter1:=0 ) initCounter1,\n\t\t\t\t\t( SELECT @counter2:=0 ) initCounter2,\n\t\t\t\t\t( SELECT @counter3:=0 ) initCounter3, \n\t\t\t\t\t( SELECT label, partition FROM myTable ) actualQuery\n\t\t\t) AS withCounter\n\t\t\tGROUP BY counter, `partition`\n\t\t";
$this->checkQuery($query, $innerQuery, $expected);
}
/**
* Exit actions
*/
public function archiveDayExitActions($archiveProcessing, $rankingQueryLimit)
{
$rankingQuery = false;
if ($rankingQueryLimit > 0) {
$rankingQuery = new Piwik_RankingQuery($rankingQueryLimit);
$rankingQuery->setOthersLabel(Piwik_DataTable::LABEL_SUMMARY_ROW);
$rankingQuery->addLabelColumn('idaction');
$rankingQuery->addColumn(Piwik_Archive::INDEX_PAGE_EXIT_NB_UNIQ_VISITORS);
$rankingQuery->addColumn(Piwik_Archive::INDEX_PAGE_EXIT_NB_VISITS, 'sum');
$rankingQuery->partitionResultIntoMultipleGroups('type', array_keys($this->actionsTablesByType));
$extraSelects = 'log_action.type, log_action.name,';
$from = array("log_visit", array("table" => "log_action", "joinOn" => "log_visit.%s = log_action.idaction"));
$orderBy = "`" . Piwik_Archive::INDEX_PAGE_EXIT_NB_VISITS . "` DESC, log_action.name ASC";
} else {
$extraSelects = false;
$from = "log_visit";
$orderBy = false;
}
$select = "log_visit.%s as idaction, {$extraSelects}\n\t\t\t\tcount(distinct log_visit.idvisitor) as `" . Piwik_Archive::INDEX_PAGE_EXIT_NB_UNIQ_VISITORS . "`,\n\t\t\t\tcount(*) as `" . Piwik_Archive::INDEX_PAGE_EXIT_NB_VISITS . "`";
$where = "log_visit.visit_last_action_time >= ?\n\t\t\t\tAND log_visit.visit_last_action_time <= ?\n\t\t \t\tAND log_visit.idsite = ?\n\t\t \t\tAND log_visit.%s > 0";
$groupBy = "log_visit.%s, idaction";
$this->archiveDayQueryProcess($select, $from, $where, $orderBy, $groupBy, "visit_exit_idaction_url", $archiveProcessing, $rankingQuery);
$this->archiveDayQueryProcess($select, $from, $where, $orderBy, $groupBy, "visit_exit_idaction_name", $archiveProcessing, $rankingQuery);
return array($rankingQuery, $extraSelects, $from, $orderBy, $select, $where, $groupBy);
}
/**
* Get information about the following actions (following pages, site searches, outlinks, downloads)
*
* @param $idaction
* @param $actionType
* @param Piwik_ArchiveProcessing_Day $archiveProcessing
* @param $limitBeforeGrouping
* @param $includeLoops
* @return array(followingPages:Piwik_DataTable, outlinks:Piwik_DataTable, downloads:Piwik_DataTable)
*/
public function queryFollowingActions($idaction, $actionType, Piwik_ArchiveProcessing_Day $archiveProcessing, $limitBeforeGrouping = false, $includeLoops = false)
{
$types = array();
$isTitle = $actionType == 'title';
if (!$isTitle) {
// specific setup for page urls
$types[Piwik_Tracker_Action::TYPE_ACTION_URL] = 'followingPages';
$dimension = 'IF( idaction_url IS NULL, idaction_name, idaction_url )';
// site search referrers are logged with url=NULL
// when we find one, we have to join on name
$joinLogActionColumn = $dimension;
$addSelect = 'log_action.name, log_action.url_prefix, log_action.type';
} else {
// specific setup for page titles:
$types[Piwik_Tracker_Action::TYPE_ACTION_NAME] = 'followingPages';
// join log_action on name and url and pick depending on url type
// the table joined on url is log_action1
$joinLogActionColumn = array('idaction_url', 'idaction_name');
$dimension = '
CASE
' . '
WHEN log_link_visit_action.idaction_url IS NULL THEN log_action2.idaction
' . '
WHEN log_action1.type = ' . Piwik_Tracker_Action::TYPE_ACTION_URL . ' THEN log_action2.idaction
' . '
ELSE log_action1.idaction
END
';
$addSelect = '
CASE
' . '
WHEN log_link_visit_action.idaction_url IS NULL THEN log_action2.name
' . '
WHEN log_action1.type = ' . Piwik_Tracker_Action::TYPE_ACTION_URL . ' THEN log_action2.name
' . '
ELSE log_action1.name
END AS name,
CASE
' . '
WHEN log_link_visit_action.idaction_url IS NULL THEN log_action2.type
' . '
WHEN log_action1.type = ' . Piwik_Tracker_Action::TYPE_ACTION_URL . ' THEN log_action2.type
' . '
ELSE log_action1.type
END AS type,
NULL AS url_prefix
';
}
// these types are available for both titles and urls
$types[Piwik_Tracker_Action::TYPE_SITE_SEARCH] = 'followingSiteSearches';
$types[Piwik_Tracker_Action::TYPE_OUTLINK] = 'outlinks';
$types[Piwik_Tracker_Action::TYPE_DOWNLOAD] = 'downloads';
$rankingQuery = new Piwik_RankingQuery($limitBeforeGrouping ? $limitBeforeGrouping : $this->limitBeforeGrouping);
$rankingQuery->addLabelColumn(array('name', 'url_prefix'));
$rankingQuery->partitionResultIntoMultipleGroups('type', array_keys($types));
$type = $this->getColumnTypeSuffix($actionType);
$where = 'log_link_visit_action.idaction_' . $type . '_ref = ' . intval($idaction);
if (!$includeLoops) {
$where .= ' AND (log_link_visit_action.idaction_' . $type . ' IS NULL OR ' . 'log_link_visit_action.idaction_' . $type . ' != ' . intval($idaction) . ')';
}
$orderBy = '`' . Piwik_Archive::INDEX_NB_ACTIONS . '` DESC';
$metrics = array(Piwik_Archive::INDEX_NB_ACTIONS);
$data = $archiveProcessing->queryActionsByDimension($dimension, $where, $metrics, $orderBy, $rankingQuery, $joinLogActionColumn, $addSelect);
$this->totalTransitionsToFollowingActions = 0;
$dataTables = array();
foreach ($types as $type => $recordName) {
$dataTable = new Piwik_DataTable();
if (isset($data[$type])) {
foreach ($data[$type] as &$record) {
$actions = intval($record[Piwik_Archive::INDEX_NB_ACTIONS]);
$dataTable->addRow(new Piwik_DataTable_Row(array(Piwik_DataTable_Row::COLUMNS => array('label' => $this->getPageLabel($record, $isTitle), Piwik_Archive::INDEX_NB_ACTIONS => $actions))));
$this->totalTransitionsToFollowingActions += $actions;
}
}
$dataTables[$recordName] = $dataTable;
}
return $dataTables;
}
/**
* Get information about the following actions (following pages, outlinks, downloads)
*
* @param $idaction
* @param Piwik_ArchiveProcessing_Day $archiveProcessing
* @return array(followingPages:Piwik_DataTable, outlinks:Piwik_DataTable, downloads:Piwik_DataTable)
*/
public function queryFollowingActions($idaction, Piwik_ArchiveProcessing_Day $archiveProcessing, $limitBeforeGrouping = false)
{
static $types = array(Piwik_Tracker_Action::TYPE_ACTION_URL => 'followingPages', Piwik_Tracker_Action::TYPE_OUTLINK => 'outlinks', Piwik_Tracker_Action::TYPE_DOWNLOAD => 'downloads');
$dimension = 'idaction_url';
$rankingQuery = new Piwik_RankingQuery($limitBeforeGrouping ? $limitBeforeGrouping : $this->limitBeforeGrouping);
$rankingQuery->addLabelColumn(array('name', 'url_prefix'));
$rankingQuery->partitionResultIntoMultipleGroups('type', array_keys($types));
$addSelect = 'log_action.name, log_action.url_prefix, log_action.type';
$where = '
log_link_visit_action.idaction_url_ref = ' . intval($idaction) . ' AND
log_link_visit_action.idaction_url != ' . intval($idaction);
$orderBy = '`' . Piwik_Archive::INDEX_NB_ACTIONS . '` DESC';
$metrics = array(Piwik_Archive::INDEX_NB_ACTIONS);
$data = $archiveProcessing->queryActionsByDimension(array($dimension), $where, $metrics, $orderBy, $rankingQuery, $dimension, $addSelect);
$dataTables = array();
foreach ($types as $type => $recordName) {
$dataTable = new Piwik_DataTable();
if (isset($data[$type])) {
foreach ($data[$type] as &$record) {
$dataTable->addRow(new Piwik_DataTable_Row(array(Piwik_DataTable_Row::COLUMNS => array('label' => $type == Piwik_Tracker_Action::TYPE_ACTION_URL ? Piwik_Tracker_Action::reconstructNormalizedUrl($record['name'], $record['url_prefix']) : $record['name'], Piwik_Archive::INDEX_NB_ACTIONS => intval($record[Piwik_Archive::INDEX_NB_ACTIONS])))));
}
}
$dataTables[$recordName] = $dataTable;
}
return $dataTables;
}
