diff options
Diffstat (limited to 'core/DataAccess/LogQueryBuilder.php')
| -rw-r--r-- | core/DataAccess/LogQueryBuilder.php | 211 |
1 files changed, 24 insertions, 187 deletions
diff --git a/core/DataAccess/LogQueryBuilder.php b/core/DataAccess/LogQueryBuilder.php index c65f36bf10..186ed0d96e 100644 --- a/core/DataAccess/LogQueryBuilder.php +++ b/core/DataAccess/LogQueryBuilder.php @@ -10,11 +10,23 @@ namespace Piwik\DataAccess; use Exception; -use Piwik\Common; +use Piwik\DataAccess\LogQueryBuilder\JoinGenerator; +use Piwik\DataAccess\LogQueryBuilder\JoinTables; +use Piwik\Plugin\LogTablesProvider; use Piwik\Segment\SegmentExpression; class LogQueryBuilder { + /** + * @var LogTablesProvider + */ + private $logTableProvider; + + public function __construct(LogTablesProvider $logTablesProvider) + { + $this->logTableProvider = $logTablesProvider; + } + public function getSelectQueryString(SegmentExpression $segmentExpression, $select, $from, $where, $bind, $groupBy, $orderBy, $limitAndOffset) { @@ -31,9 +43,11 @@ class LogQueryBuilder $bind = array_merge($bind, $segmentSql['bind']); } - $joins = $this->generateJoinsString($from); - $joinWithSubSelect = $joins['joinWithSubSelect']; - $from = $joins['sql']; + $tables = new JoinTables($this->logTableProvider, $from); + $join = new JoinGenerator($tables); + $join->generate(); + $from = $join->getJoinString(); + $joinWithSubSelect = $join->shouldJoinWithSelect(); // hack for https://github.com/piwik/piwik/issues/9194#issuecomment-164321612 $useSpecialConversionGroupBy = (!empty($segmentSql) @@ -55,192 +69,15 @@ class LogQueryBuilder ); } - private function hasJoinedTableAlreadyManually($tableToFind, $joinToFind, $tables) + private function getKnownTables() { - foreach ($tables as $index => $table) { - if (is_array($table) - && !empty($table['table']) - && $table['table'] === $tableToFind - && (!isset($table['tableAlias']) || $table['tableAlias'] === $tableToFind) - && isset($table['joinOn']) && $table['joinOn'] === $joinToFind) { - return true; - } + $names = array(); + foreach ($this->logTableProvider->getAllLogTables() as $logTable) { + $names[] = $logTable->getName(); } - - return false; + return $names; } - private function findIndexOfManuallyAddedTable($tableToFind, $tables) - { - foreach ($tables as $index => $table) { - if (is_array($table) - && !empty($table['table']) - && $table['table'] === $tableToFind - && (!isset($table['tableAlias']) || $table['tableAlias'] === $tableToFind)) { - return $index; - } - } - } - - private function hasTableAddedManually($tableToFind, $tables) - { - $table = $this->findIndexOfManuallyAddedTable($tableToFind, $tables); - - return isset($table); - } - - /** - * Generate the join sql based on the needed tables - * @param array $tables tables to join - * @throws Exception if tables can't be joined - * @return array - */ - private function generateJoinsString(&$tables) - { - $knownTables = array("log_action", "log_visit", "log_link_visit_action", "log_conversion", "log_conversion_item"); - $visitsAvailable = $linkVisitActionsTableAvailable = $conversionsAvailable = $conversionItemAvailable = $actionsTableAvailable = false; - $defaultLogActionJoin = "log_link_visit_action.idaction_url = log_action.idaction"; - - $joinWithSubSelect = false; - $sql = ''; - - // make sure the tables are joined in the right order - // base table first, then action before conversion - // this way, conversions can be left joined on idvisit - $actionIndex = array_search("log_link_visit_action", $tables); - $conversionIndex = array_search("log_conversion", $tables); - if ($actionIndex > 0 && $conversionIndex > 0 && $actionIndex > $conversionIndex) { - $tables[$actionIndex] = "log_conversion"; - $tables[$conversionIndex] = "log_link_visit_action"; - } - // same as above: action before visit - $actionIndex = array_search("log_link_visit_action", $tables); - $visitIndex = array_search("log_visit", $tables); - if ($actionIndex > 0 && $visitIndex > 0 && $actionIndex > $visitIndex) { - $tables[$actionIndex] = "log_visit"; - $tables[$visitIndex] = "log_link_visit_action"; - } - - // we need to add log_link_visit_action dynamically to join eg visit with action - $linkVisitAction = array_search("log_link_visit_action", $tables); - $actionIndex = array_search("log_action", $tables); - if ($linkVisitAction === false && $actionIndex > 0) { - $tables[] = "log_link_visit_action"; - } - - if ($actionIndex > 0 - && $this->hasTableAddedManually('log_action', $tables) - && !$this->hasJoinedTableAlreadyManually('log_action', $defaultLogActionJoin, $tables)) { - // we cannot join the same table with same alias twice, therefore we need to combine the join via AND - $tableIndex = $this->findIndexOfManuallyAddedTable('log_action', $tables); - $defaultLogActionJoin = '(' . $tables[$tableIndex]['joinOn'] . ' AND ' . $defaultLogActionJoin . ')'; - unset($tables[$tableIndex]); - } - - $linkVisitAction = array_search("log_link_visit_action", $tables); - $actionIndex = array_search("log_action", $tables); - if ($linkVisitAction > 0 && $actionIndex > 0 && $linkVisitAction > $actionIndex) { - $tables[$actionIndex] = "log_link_visit_action"; - $tables[$linkVisitAction] = "log_action"; - } - - foreach ($tables as $i => $table) { - if (is_array($table)) { - // join condition provided - $alias = isset($table['tableAlias']) ? $table['tableAlias'] : $table['table']; - $sql .= " - LEFT JOIN " . Common::prefixTable($table['table']) . " AS " . $alias - . " ON " . $table['joinOn']; - continue; - } - - if (!in_array($table, $knownTables)) { - throw new Exception("Table '$table' can't be used for segmentation"); - } - - $tableSql = Common::prefixTable($table) . " AS $table"; - - if ($i == 0) { - // first table - $sql .= $tableSql; - } else { - - if ($linkVisitActionsTableAvailable && $table === 'log_action') { - $join = $defaultLogActionJoin; - - if ($this->hasJoinedTableAlreadyManually($table, $join, $tables)) { - $actionsTableAvailable = true; - continue; - } - - } elseif ($linkVisitActionsTableAvailable && $table == "log_conversion") { - // have actions, need conversions => join on idvisit - $join = "log_conversion.idvisit = log_link_visit_action.idvisit"; - } elseif ($linkVisitActionsTableAvailable && $table == "log_visit") { - // have actions, need visits => join on idvisit - $join = "log_visit.idvisit = log_link_visit_action.idvisit"; - - if ($this->hasJoinedTableAlreadyManually($table, $join, $tables)) { - $visitsAvailable = true; - continue; - } - - } elseif ($visitsAvailable && $table == "log_link_visit_action") { - // have visits, need actions => we have to use a more complex join - // we don't hande this here, we just return joinWithSubSelect=true in this case - $joinWithSubSelect = true; - $join = "log_link_visit_action.idvisit = log_visit.idvisit"; - - if ($this->hasJoinedTableAlreadyManually($table, $join, $tables)) { - $linkVisitActionsTableAvailable = true; - continue; - } - - } elseif ($conversionsAvailable && $table == "log_link_visit_action") { - // have conversions, need actions => join on idvisit - $join = "log_conversion.idvisit = log_link_visit_action.idvisit"; - } elseif (($visitsAvailable && $table == "log_conversion") - || ($conversionsAvailable && $table == "log_visit") - ) { - // have visits, need conversion (or vice versa) => join on idvisit - // notice that joining conversions on visits has lower priority than joining it on actions - $join = "log_conversion.idvisit = log_visit.idvisit"; - - // if conversions are joined on visits, we need a complex join - if ($table == "log_conversion") { - $joinWithSubSelect = true; - } - } elseif ($conversionItemAvailable && $table === 'log_visit') { - $join = "log_conversion_item.idvisit = log_visit.idvisit"; - } elseif ($conversionItemAvailable && $table === 'log_link_visit_action') { - $join = "log_conversion_item.idvisit = log_link_visit_action.idvisit"; - } elseif ($conversionItemAvailable && $table === 'log_conversion') { - $join = "log_conversion_item.idvisit = log_conversion.idvisit"; - } else { - throw new Exception("Table '$table' can't be joined for segmentation"); - } - - // the join sql the default way - $sql .= " - LEFT JOIN $tableSql ON $join"; - } - - // remember which tables are available - $visitsAvailable = ($visitsAvailable || $table == "log_visit"); - $linkVisitActionsTableAvailable = ($linkVisitActionsTableAvailable || $table == "log_link_visit_action"); - $actionsTableAvailable = ($actionsTableAvailable || $table == "log_action"); - $conversionsAvailable = ($conversionsAvailable || $table == "log_conversion"); - $conversionItemAvailable = ($conversionItemAvailable || $table == "log_conversion_item"); - } - - $return = array( - 'sql' => $sql, - 'joinWithSubSelect' => $joinWithSubSelect - ); - return $return; - } - - /** * Build a select query where actions have to be joined on visits (or conversions) * In this case, the query gets wrapped in another query so that grouping by visit is possible @@ -256,7 +93,7 @@ class LogQueryBuilder */ private function buildWrappedSelectQuery($select, $from, $where, $groupBy, $orderBy, $limitAndOffset, $innerGroupBy = null) { - $matchTables = "(log_visit|log_conversion_item|log_conversion|log_action)"; + $matchTables = '(' . implode('|', $this->getKnownTables()) . ')'; preg_match_all("/". $matchTables ."\.[a-z0-9_\*]+/", $select, $matches); $neededFields = array_unique($matches[0]); |