/** * @group Core * @group RankingQuery */ public function testExcludeRows() { $query = new Piwik_RankingQuery(20); $query->addLabelColumn('label'); $query->setColumnToMarkExcludedRows('exclude_marker'); $innerQuery = "SELECT label, 1 AS exclude_marker FROM myTable"; $expected = "\n\t\t\tSELECT\n\t\t\t\tCASE\n\t\t\t\t\tWHEN counter = 21 THEN \"Others\" \n\t\t\t\t\tELSE `label`\n\t\t\t\tEND AS `label`,\n\t\t\t\t`exclude_marker`\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 exclude_marker != 0 THEN -1 * exclude_marker\n\t\t\t\t\t\tWHEN @counter = 21 THEN 21\n\t\t\t\t\t\tELSE @counter:=@counter+1\n\t\t\t\t\tEND AS counter,\n\t\t\t\t\t`exclude_marker`\n\t\t\t\tFROM\n\t\t\t\t\t( SELECT @counter:=0 ) initCounter,\n\t\t\t\t\t( SELECT label, 1 AS exclude_marker FROM myTable ) actualQuery\n\t\t\t) AS withCounter\n\t\t\tGROUP BY counter\n\t\t"; $this->checkQuery($query, $innerQuery, $expected); }
/** * Get information about internal referrers (previous pages & loops, i.e. page refreshes) * * @param $idaction * @param $actionType * @param Piwik_ArchiveProcessing_Day $archiveProcessing * @param $limitBeforeGrouping * @return array(previousPages:Piwik_DataTable, loops:integer) */ public function queryInternalReferrers($idaction, $actionType, $archiveProcessing, $limitBeforeGrouping = false) { $rankingQuery = new Piwik_RankingQuery($limitBeforeGrouping ? $limitBeforeGrouping : $this->limitBeforeGrouping); $rankingQuery->addLabelColumn(array('name', 'url_prefix')); $rankingQuery->setColumnToMarkExcludedRows('is_self'); $rankingQuery->partitionResultIntoMultipleGroups('action_partition', array(0, 1, 2)); $type = $this->getColumnTypeSuffix($actionType); $mainActionType = Piwik_Tracker_Action::TYPE_ACTION_URL; $dimension = 'idaction_url_ref'; $isTitle = $actionType == 'title'; if ($isTitle) { $mainActionType = Piwik_Tracker_Action::TYPE_ACTION_NAME; $dimension = 'idaction_name_ref'; } $addSelect = ' log_action.name, log_action.url_prefix, CASE WHEN log_link_visit_action.idaction_' . $type . '_ref = ' . intval($idaction) . ' THEN 1 ELSE 0 END AS is_self, CASE WHEN log_action.type = ' . $mainActionType . ' THEN 1 WHEN log_action.type = ' . Piwik_Tracker_Action::TYPE_SITE_SEARCH . ' THEN 2 ELSE 0 END AS action_partition'; $where = ' log_link_visit_action.idaction_' . $type . ' = ' . intval($idaction); if ($dimension == 'idaction_url_ref') { // site search referrers are logged with url_ref=NULL // when we find one, we have to join on name_ref $dimension = 'IF( idaction_url_ref IS NULL, idaction_name_ref, idaction_url_ref )'; $joinLogActionOn = $dimension; } else { $joinLogActionOn = $dimension; $dimension = array($dimension); } $orderBy = '`' . Piwik_Archive::INDEX_NB_ACTIONS . '` DESC'; $metrics = array(Piwik_Archive::INDEX_NB_ACTIONS); $data = $archiveProcessing->queryActionsByDimension($dimension, $where, $metrics, $orderBy, $rankingQuery, $joinLogActionOn, $addSelect); $loops = 0; $nbPageviews = 0; $previousPagesDataTable = new Piwik_DataTable(); if (isset($data['result'][1])) { foreach ($data['result'][1] as &$page) { $nbActions = intval($page[Piwik_Archive::INDEX_NB_ACTIONS]); $previousPagesDataTable->addRow(new Piwik_DataTable_Row(array(Piwik_DataTable_Row::COLUMNS => array('label' => $this->getPageLabel($page, $isTitle), Piwik_Archive::INDEX_NB_ACTIONS => $nbActions)))); $nbPageviews += $nbActions; } } $previousSearchesDataTable = new Piwik_DataTable(); if (isset($data['result'][2])) { foreach ($data['result'][2] as &$search) { $nbActions = intval($search[Piwik_Archive::INDEX_NB_ACTIONS]); $previousSearchesDataTable->addRow(new Piwik_DataTable_Row(array(Piwik_DataTable_Row::COLUMNS => array('label' => $search['name'], Piwik_Archive::INDEX_NB_ACTIONS => $nbActions)))); $nbPageviews += $nbActions; } } if (isset($data['result'][0])) { foreach ($data['result'][0] as &$referrer) { $nbPageviews += intval($referrer[Piwik_Archive::INDEX_NB_ACTIONS]); } } if (count($data['excludedFromLimit'])) { $loops += intval($data['excludedFromLimit'][0][Piwik_Archive::INDEX_NB_ACTIONS]); $nbPageviews += $loops; } return array('pageviews' => $nbPageviews, 'previousPages' => $previousPagesDataTable, 'previousSiteSearches' => $previousSearchesDataTable, 'loops' => $loops); }
/** * Get information about internal referrers (previous pages & loops, i.e. page refreshes) * * @param $idaction * @param Piwik_ArchiveProcessing_Day $archiveProcessing * @return array(previousPages:Piwik_DataTable, loops:integer) */ public function queryInternalReferrers($idaction, Piwik_ArchiveProcessing_Day $archiveProcessing, $limitBeforeGrouping = false) { $dimension = 'idaction_url_ref'; $rankingQuery = new Piwik_RankingQuery($limitBeforeGrouping ? $limitBeforeGrouping : $this->limitBeforeGrouping); $rankingQuery->addLabelColumn(array('name', 'url_prefix')); $rankingQuery->setColumnToMarkExcludedRows('is_self'); $addSelect = ' log_action.name, log_action.url_prefix, CASE WHEN log_link_visit_action.idaction_url_ref = ' . intval($idaction) . ' THEN 1 ELSE 0 END AS is_self'; $where = ' log_link_visit_action.idaction_url = ' . intval($idaction) . ' AND log_action.type = ' . Piwik_Tracker_Action::TYPE_ACTION_URL; $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); $previousPagesDataTable = new Piwik_DataTable(); foreach ($data['result'] as &$page) { $previousPagesDataTable->addRow(new Piwik_DataTable_Row(array(Piwik_DataTable_Row::COLUMNS => array('label' => Piwik_Tracker_Action::reconstructNormalizedUrl($page['name'], $page['url_prefix']), Piwik_Archive::INDEX_NB_ACTIONS => intval($page[Piwik_Archive::INDEX_NB_ACTIONS]))))); } $loops = 0; if (count($data['excludedFromLimit'])) { $loops = intval($data['excludedFromLimit'][0][Piwik_Archive::INDEX_NB_ACTIONS]); } return array('previousPages' => $previousPagesDataTable, 'loops' => $loops); }