fix possible mysql error "Not unique table/alias"

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