diff options
author | Matthieu Aubry <mattab@users.noreply.github.com> | 2016-12-02 07:30:25 +0300 |
---|---|---|
committer | GitHub <noreply@github.com> | 2016-12-02 07:30:25 +0300 |
commit | 59ba07cba11cd51a49ba5a4946abee0d34c4c7a9 (patch) | |
tree | 3c49147c490360c0d436dba0e1c9690d9978bd0b | |
parent | bd095961c13249dcafbb283e5da9e028e31bd428 (diff) | |
parent | c7d39db582a82dc0e6c7236a53e34308bf6dd3d3 (diff) |
Fix a bug in query builder where tables are sorted randomly (#10942)
* fix a bug in query builder where tables are sorted randomly
* added more complex example to show it better
-rw-r--r-- | core/DataAccess/LogQueryBuilder/JoinGenerator.php | 1 | ||||
-rw-r--r-- | core/DataAccess/LogQueryBuilder/JoinTables.php | 12 | ||||
-rw-r--r-- | tests/PHPUnit/Integration/SegmentTest.php | 86 |
3 files changed, 98 insertions, 1 deletions
diff --git a/core/DataAccess/LogQueryBuilder/JoinGenerator.php b/core/DataAccess/LogQueryBuilder/JoinGenerator.php index c05325502c..7b69184f0b 100644 --- a/core/DataAccess/LogQueryBuilder/JoinGenerator.php +++ b/core/DataAccess/LogQueryBuilder/JoinGenerator.php @@ -234,6 +234,7 @@ class JoinGenerator if (isset($tA['tableAlias']) && isset($tB['joinOn']) && strpos($tB['joinOn'], $tA['tableAlias']) !== false) { return -1; } + return 0; } diff --git a/core/DataAccess/LogQueryBuilder/JoinTables.php b/core/DataAccess/LogQueryBuilder/JoinTables.php index d840cfba3f..1faad323a7 100644 --- a/core/DataAccess/LogQueryBuilder/JoinTables.php +++ b/core/DataAccess/LogQueryBuilder/JoinTables.php @@ -97,7 +97,17 @@ class JoinTables extends \ArrayObject // we need to make sure first table always comes first, only sort tables after the first table $firstTable = array_shift($tables); - usort($tables, $cmpFunction); + usort($tables, function ($ta, $tb) use ($tables, $cmpFunction) { + $return = call_user_func($cmpFunction, $ta, $tb); + if ($return === 0) { + $indexA = array_search($ta, $tables); + $indexB = array_search($tb, $tables); + + return $indexA - $indexB; + } + + return $return; + }); array_unshift($tables, $firstTable); $this->exchangeArray($tables); diff --git a/tests/PHPUnit/Integration/SegmentTest.php b/tests/PHPUnit/Integration/SegmentTest.php index 9558cc0929..20b23065db 100644 --- a/tests/PHPUnit/Integration/SegmentTest.php +++ b/tests/PHPUnit/Integration/SegmentTest.php @@ -426,6 +426,92 @@ class SegmentTest extends IntegrationTestCase $this->assertEquals($this->removeExtraWhiteSpaces($expected), $this->removeExtraWhiteSpaces($query)); } + public function test_getSelectQuery_whenJoiningManyCustomTablesItShouldKeepTheOrderAsDefined() + { + $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_link_visit_action', + 'tableAlias' => 'log_link_visit_action_foo', + 'joinOn' => 'log_link_visit_action.idvisit = log_link_visit_action_foo.idvisit', + ), + array( + 'table' => 'log_action', + 'tableAlias' => 'log_action_foo', + 'joinOn' => 'log_link_visit_action_foo.idaction_url = log_action_foo.idaction', + ), + array( + 'table' => 'log_link_visit_action', + 'tableAlias' => 'log_link_visit_action_bar', + 'joinOn' => "log_link_visit_action.idvisit = log_link_visit_action_bar.idvisit" + ), + array( + 'table' => 'log_action', + 'tableAlias' => 'log_action_bar', + 'joinOn' => "log_link_visit_action_bar.idaction_url = log_action_bar.idaction" + ), + array( + 'table' => 'log_link_visit_action', + 'tableAlias' => 'log_link_visit_action_baz', + 'joinOn' => "log_link_visit_action.idvisit = log_link_visit_action_baz.idvisit" + ), + array( + 'table' => 'log_action', + 'tableAlias' => 'log_action_baz', + 'joinOn' => "log_link_visit_action_baz.idaction_url = log_action_baz.idaction" + ), + 'log_action', + ); + + $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); + + $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 $logLinkVisitActionTable AS log_link_visit_action_foo + ON log_link_visit_action.idvisit = log_link_visit_action_foo.idvisit + LEFT JOIN $logActionTable AS log_action_foo + ON log_link_visit_action_foo.idaction_url = log_action_foo.idaction + LEFT JOIN $logLinkVisitActionTable AS log_link_visit_action_bar + ON log_link_visit_action.idvisit = log_link_visit_action_bar.idvisit + LEFT JOIN $logActionTable AS log_action_bar + ON log_link_visit_action_bar.idaction_url = log_action_bar.idaction + LEFT JOIN $logLinkVisitActionTable AS log_link_visit_action_baz + ON log_link_visit_action.idvisit = log_link_visit_action_baz.idvisit + LEFT JOIN $logActionTable AS log_action_baz + ON log_link_visit_action_baz.idaction_url = log_action_baz.idaction + LEFT JOIN $logActionTable AS log_action + ON 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)); + } + public function test_getSelectQuery_whenJoinLogLinkVisitActionOnActionOnVisit_WithSameTableAliasButDifferentJoin() { $actionType = 3; |