diff options
author | Thomas Steur <thomas.steur@gmail.com> | 2015-12-04 03:36:10 +0300 |
---|---|---|
committer | Thomas Steur <thomas.steur@gmail.com> | 2015-12-04 03:36:10 +0300 |
commit | dc55de6d759bd436f3d920b164435b1c7796cdbf (patch) | |
tree | 3e16108645004fd6c3f4f57fbdf61f8a7f76b2a9 | |
parent | 8d1502aa42dfc288705a4a002292139bcae07516 (diff) |
fix possible mysql error "Not unique table/alias"
-rw-r--r-- | core/DataAccess/LogQueryBuilder.php | 33 | ||||
-rw-r--r-- | tests/PHPUnit/Integration/SegmentTest.php | 47 |
2 files changed, 78 insertions, 2 deletions
diff --git a/core/DataAccess/LogQueryBuilder.php b/core/DataAccess/LogQueryBuilder.php index 7dc56b0b41..f55fd96a56 100644 --- a/core/DataAccess/LogQueryBuilder.php +++ b/core/DataAccess/LogQueryBuilder.php @@ -44,7 +44,6 @@ class LogQueryBuilder ); } - private function hasJoinedTableAlreadyManually($tableToFind, $joinToFind, $tables) { foreach ($tables as $index => $table) { @@ -60,6 +59,25 @@ class LogQueryBuilder return false; } + 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 @@ -70,6 +88,8 @@ class LogQueryBuilder { $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 = ''; @@ -98,6 +118,15 @@ class LogQueryBuilder $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) { @@ -127,7 +156,7 @@ class LogQueryBuilder } else { if ($linkVisitActionsTableAvailable && $table === 'log_action') { - $join = "log_link_visit_action.idaction_url = log_action.idaction"; + $join = $defaultLogActionJoin; if ($this->hasJoinedTableAlreadyManually($table, $join, $tables)) { $actionsTableAvailable = true; diff --git a/tests/PHPUnit/Integration/SegmentTest.php b/tests/PHPUnit/Integration/SegmentTest.php index d5a6ff7b0e..d5fce5f7e4 100644 --- a/tests/PHPUnit/Integration/SegmentTest.php +++ b/tests/PHPUnit/Integration/SegmentTest.php @@ -418,6 +418,53 @@ class SegmentTest extends IntegrationTestCase $this->assertEquals($this->removeExtraWhiteSpaces($expected), $this->removeExtraWhiteSpaces($query)); } + public function test_getSelectQuery_whenJoinLogLinkVisitActionOnActionOnVisit_WithSameTableAliasButDifferentJoin() + { + $actionType = 3; + $idSite = 1; + $select = 'log_link_visit_action.custom_dimension_1, + log_action.name as url, + sum(log_link_visit_action.time_spent) as `13`, + sum(case log_visit.visit_total_actions when 1 then 1 when 0 then 1 else 0 end) as `6`'; + $from = array( + 'log_link_visit_action', + array('table' => 'log_visit', 'joinOn' => 'log_visit.idvisit = log_link_visit_action.idvisit'), + array('table' => 'log_action', 'joinOn' => 'log_link_visit_action.idaction_name = log_action.idaction') + ); + $where = 'log_link_visit_action.server_time >= ? + AND log_link_visit_action.server_time <= ? + AND log_link_visit_action.idsite = ?'; + $bind = array('2015-11-30 11:00:00', '2015-12-01 10:59:59', $idSite); + + $segment = 'actionType==' . $actionType; + $segment = new Segment($segment, $idSites = array()); + + $query = $segment->getSelectQuery($select, $from, $where, $bind); + + $logVisitTable = Common::prefixTable('log_visit'); + $logActionTable = Common::prefixTable('log_action'); + $logLinkVisitActionTable = Common::prefixTable('log_link_visit_action'); + + $expected = array( + "sql" => " + SELECT log_link_visit_action.custom_dimension_1, + log_action.name as url, + sum(log_link_visit_action.time_spent) as `13`, + sum(case log_visit.visit_total_actions when 1 then 1 when 0 then 1 else 0 end) as `6` + FROM $logLinkVisitActionTable AS log_link_visit_action + LEFT JOIN $logVisitTable AS log_visit + ON log_visit.idvisit = log_link_visit_action.idvisit + LEFT JOIN $logActionTable AS log_action + ON (log_link_visit_action.idaction_name = log_action.idaction AND log_link_visit_action.idaction_url = log_action.idaction) + WHERE ( log_link_visit_action.server_time >= ? + AND log_link_visit_action.server_time <= ? + AND log_link_visit_action.idsite = ? ) + AND ( log_action.type = ? )", + "bind" => array('2015-11-30 11:00:00', '2015-12-01 10:59:59', $idSite, $actionType)); + + $this->assertEquals($this->removeExtraWhiteSpaces($expected), $this->removeExtraWhiteSpaces($query)); + } + /** * visit is joined on action, then conversion is joined * make sure that conversion is joined on action not visit |