/**
* @group Core
* @group RankingQuery
*/
public function testBasic()
{
$query = new Piwik_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);
}
/**
* Query visits by dimension
*
* @param array|string $label Can be a string, eg. "referer_name", will be aliased as 'label' in the returned rows
* Can also be an array of strings, when the dimension spans multiple fields,
* eg. array("referer_name", "referer_keyword")
* @param string $where Additional condition for WHERE clause
* @param bool|array $metrics Set this if you want to limit the columns that are returned.
* The possible values in the array are Piwik_Archive::INDEX_*.
* @param bool|string $orderBy ORDER BY clause. This is needed in combination with $rankingQuery.
* @param Piwik_RankingQuery $rankingQuery
* A pre-configured ranking query instance that is used to limit the result.
* If set, the return value is the array returned by Piwik_RankingQuery::execute().
* @param string $addSelect Additional SELECT clause
* @param bool $addSelectGeneratesLabelColumn
* Set to true if the $label column is generated in $addSelect.
* @return mixed
*/
public function queryVisitsByDimension($label, $where = '', $metrics = false, $orderBy = false, $rankingQuery = null, $addSelect = false, $addSelectGeneratesLabelColumn = false)
{
if (is_array($label)) {
$groupBy = "log_visit." . implode(", log_visit.", $label);
foreach ($label as &$field) {
$field = 'log_visit.' . $field . ' AS ' . $field;
}
$select = implode(", ", $label);
} else {
if ($addSelectGeneratesLabelColumn) {
$select = $addSelect;
$groupBy = $label;
} else {
$select = $label . " AS label ";
$groupBy = 'label';
}
}
if (!empty($where)) {
$where = sprintf($where, "log_visit", "log_visit");
$where = ' AND ' . $where;
}
$pre = ", \n\t\t\t";
if (!$metrics || in_array(Piwik_Archive::INDEX_NB_UNIQ_VISITORS, $metrics)) {
$select .= $pre . "count(distinct log_visit.idvisitor) as `" . Piwik_Archive::INDEX_NB_UNIQ_VISITORS . "`";
}
if (!$metrics || in_array(Piwik_Archive::INDEX_NB_VISITS, $metrics)) {
$select .= $pre . "count(*) as `" . Piwik_Archive::INDEX_NB_VISITS . "`";
}
if (!$metrics || in_array(Piwik_Archive::INDEX_NB_ACTIONS, $metrics)) {
$select .= $pre . "sum(log_visit.visit_total_actions) as `" . Piwik_Archive::INDEX_NB_ACTIONS . "`";
}
if (!$metrics || in_array(Piwik_Archive::INDEX_MAX_ACTIONS, $metrics)) {
$select .= $pre . "max(log_visit.visit_total_actions) as `" . Piwik_Archive::INDEX_MAX_ACTIONS . "`";
}
if (!$metrics || in_array(Piwik_Archive::INDEX_SUM_VISIT_LENGTH, $metrics)) {
$select .= $pre . "sum(log_visit.visit_total_time) as `" . Piwik_Archive::INDEX_SUM_VISIT_LENGTH . "`";
}
if (!$metrics || in_array(Piwik_Archive::INDEX_BOUNCE_COUNT, $metrics)) {
$select .= $pre . "sum(case log_visit.visit_total_actions when 1 then 1 when 0 then 1 else 0 end) as `" . Piwik_Archive::INDEX_BOUNCE_COUNT . "`";
}
if (!$metrics || in_array(Piwik_Archive::INDEX_NB_VISITS_CONVERTED, $metrics)) {
$select .= $pre . "sum(case log_visit.visit_goal_converted when 1 then 1 else 0 end) as `" . Piwik_Archive::INDEX_NB_VISITS_CONVERTED . "`";
}
if ($addSelect && !$addSelectGeneratesLabelColumn) {
$select .= ', ' . $addSelect;
}
$from = "log_visit";
$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\t{$where}";
$bind = array($this->getStartDatetimeUTC(), $this->getEndDatetimeUTC(), $this->idsite);
$query = $this->getSegment()->getSelectQuery($select, $from, $where, $bind, $orderBy, $groupBy);
if ($rankingQuery !== null) {
$sumColumns = array(Piwik_Archive::INDEX_NB_UNIQ_VISITORS, Piwik_Archive::INDEX_NB_VISITS, Piwik_Archive::INDEX_NB_ACTIONS, Piwik_Archive::INDEX_SUM_VISIT_LENGTH, Piwik_Archive::INDEX_BOUNCE_COUNT, Piwik_Archive::INDEX_NB_VISITS_CONVERTED);
if ($metrics) {
foreach ($sumColumns as $i => $column) {
if (!in_array($column, $metrics)) {
unset($sumColumns[$i]);
}
}
$sumColumns = array_values($sumColumns);
}
$rankingQuery->addColumn($sumColumns, 'sum');
if (!$metrics || in_array(Piwik_Archive::INDEX_MAX_ACTIONS, $metrics)) {
$rankingQuery->addColumn(Piwik_Archive::INDEX_MAX_ACTIONS, 'max');
}
return $rankingQuery->execute($query['sql'], $query['bind']);
}
return $this->db->query($query['sql'], $query['bind']);
}
/**
* 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);
}