/** * @param RankingQuery $rankingQuery * @param string $innerQuerySql * @param string $expected */ private function checkQuery($rankingQuery, $innerQuerySql, $expected) { $query = $rankingQuery->generateQuery($innerQuerySql); $queryNoWhitespace = preg_replace("/\\s+/", "", $query); $expectedNoWhitespace = preg_replace("/\\s+/", "", $expected); $message = 'Unexpected query: ' . $query; $this->assertEquals($queryNoWhitespace, $expectedNoWhitespace, $message); }
/** * 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']); }
/** * Get information about internal referrers (previous pages & loops, i.e. page refreshes) * * @param $idaction * @param $actionType * @param LogAggregator $logAggregator * @param $limitBeforeGrouping * @return array(previousPages:DataTable, loops:integer) */ protected function queryInternalReferrers($idaction, $actionType, $logAggregator, $limitBeforeGrouping = false) { $keyIsOther = 0; $keyIsPageUrlAction = 1; $keyIsSiteSearchAction = 2; $rankingQuery = new RankingQuery($limitBeforeGrouping ? $limitBeforeGrouping : $this->limitBeforeGrouping); $rankingQuery->addLabelColumn(array('name', 'url_prefix')); $rankingQuery->setColumnToMarkExcludedRows('is_self'); $rankingQuery->partitionResultIntoMultipleGroups('action_partition', array($keyIsOther, $keyIsPageUrlAction, $keyIsSiteSearchAction)); $type = $this->getColumnTypeSuffix($actionType); $mainActionType = Action::TYPE_PAGE_URL; $dimension = 'idaction_url_ref'; if ($actionType == 'title') { $mainActionType = Action::TYPE_PAGE_TITLE; $dimension = 'idaction_name_ref'; } $selects = array('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 ' . $keyIsPageUrlAction . ' WHEN log_action.type = ' . Action::TYPE_SITE_SEARCH . ' THEN ' . $keyIsSiteSearchAction . ' ELSE ' . $keyIsOther . ' 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; } $metrics = array(Metrics::INDEX_NB_ACTIONS); $data = $logAggregator->queryActionsByDimension(array($dimension), $where, $selects, $metrics, $rankingQuery, $joinLogActionOn); $loops = 0; $nbPageviews = 0; $previousPagesDataTable = new DataTable(); if (isset($data['result'][$keyIsPageUrlAction])) { foreach ($data['result'][$keyIsPageUrlAction] as &$page) { $nbActions = intval($page[Metrics::INDEX_NB_ACTIONS]); $previousPagesDataTable->addRow(new Row(array(Row::COLUMNS => array('label' => $this->getPageLabel($page, Action::TYPE_PAGE_URL), Metrics::INDEX_NB_ACTIONS => $nbActions)))); $nbPageviews += $nbActions; } } $previousSearchesDataTable = new DataTable(); if (isset($data['result'][$keyIsSiteSearchAction])) { foreach ($data['result'][$keyIsSiteSearchAction] as &$search) { $nbActions = intval($search[Metrics::INDEX_NB_ACTIONS]); $previousSearchesDataTable->addRow(new Row(array(Row::COLUMNS => array('label' => $search['name'], Metrics::INDEX_NB_ACTIONS => $nbActions)))); $nbPageviews += $nbActions; } } if (isset($data['result'][0])) { foreach ($data['result'][0] as &$referrer) { $nbPageviews += intval($referrer[Metrics::INDEX_NB_ACTIONS]); } } if (count($data['excludedFromLimit'])) { $loops += intval($data['excludedFromLimit'][0][Metrics::INDEX_NB_ACTIONS]); $nbPageviews += $loops; } return array('pageviews' => $nbPageviews, 'previousPages' => $previousPagesDataTable, 'previousSiteSearches' => $previousSearchesDataTable, 'loops' => $loops); }
private function archiveDayQueryProcess($select, $from, $where, $groupBy, $orderBy, RankingQuery $rankingQuery) { // get query with segmentation $query = $this->getLogAggregator()->generateQuery($select, $from, $where, $groupBy, $orderBy); // apply ranking query if ($rankingQuery) { $query['sql'] = $rankingQuery->generateRankingQuery($query['sql']); } // get result $resultSet = $this->getLogAggregator()->getDb()->query($query['sql'], $query['bind']); if ($resultSet === false) { return; } return $resultSet; }
protected function archiveDayQueryProcess($select, $from, $where, $orderBy, $groupBy, RankingQuery $rankingQuery) { // get query with segmentation $query = $this->getLogAggregator()->generateQuery($select, $from, $where, $groupBy, $orderBy); // apply ranking query if ($rankingQuery) { $query['sql'] = $rankingQuery->generateQuery($query['sql']); } // get result $resultSet = $this->getLogAggregator()->getDb()->query($query['sql'], $query['bind']); if ($resultSet === false) { return; } while ($row = $resultSet->fetch()) { $this->aggregateEventRow($row); } }