Make it possible to define joins for log tables using `getWaysToJoinToOtherLogTables` (#14062)

* Make it possible to define joins for log tables using getWaysToJoinToOtherLogTables

* Adds some tests for custom log table joins

* add missing log tables joined using getWaysToJoinToOtherLogTables

* automatically add log tables up the hierarchy

* code improvements

* Adds new ExampleLogTables plugin giving a showcase for custom log tables

* specifiy table name in userid archiver to fix query if custom log table joins on user_id column

* fix tests

* Adds log table that does only indirectly join with log_visit

* Allow defining joins on visit and action

* update ui files

2 files changed, 148 insertions, 33 deletions

diff --git a/core/DataAccess/LogQueryBuilder/JoinGenerator.php b/core/DataAccess/LogQueryBuilder/JoinGenerator.php
index 506ee5e9d6..f964ed6594 100644
--- a/core/DataAccess/LogQueryBuilder/JoinGenerator.php
+++ b/core/DataAccess/LogQueryBuilder/JoinGenerator.php
@@ -53,6 +53,19 @@ class JoinGenerator
             if (!$logTable->getColumnToJoinOnIdVisit()) {
                 $tableNameToJoin = $logTable->getLinkTableToBeAbleToJoinOnVisit();
 
+                if (empty($tableNameToJoin) && $logTable->getWaysToJoinToOtherLogTables()) {
+                    foreach ($logTable->getWaysToJoinToOtherLogTables() as $otherLogTable => $column) {
+                        if ($this->tables->hasJoinedTable($otherLogTable)) {
+                            $this->tables->addTableDependency($table, $otherLogTable);
+                            continue;
+                        }
+                        if ($this->tables->isTableJoinableOnVisit($otherLogTable) || $this->tables->isTableJoinableOnAction($otherLogTable)) {
+                            $this->addMissingTablesForOtherTableJoin($otherLogTable, $table);
+                        }
+                    }
+                    continue;
+                }
+
                 if ($index > 0 && !$this->tables->hasJoinedTable($tableNameToJoin)) {
                     $this->tables->addTableToJoin($tableNameToJoin);
                 }
@@ -96,6 +109,30 @@ class JoinGenerator
         }
     }
 
+    private function addMissingTablesForOtherTableJoin($tableName, $dependentTable)
+    {
+        $this->tables->addTableDependency($dependentTable, $tableName);
+
+        if ($this->tables->hasJoinedTable($tableName)) {
+            return;
+        }
+
+        $table = $this->tables->getLogTable($tableName);
+
+        if ($table->getColumnToJoinOnIdAction() || $table->getColumnToJoinOnIdAction() || $table->getLinkTableToBeAbleToJoinOnVisit()) {
+            $this->tables->addTableToJoin($tableName);
+            return;
+        }
+
+        $otherTableJoins = $table->getWaysToJoinToOtherLogTables();
+
+        foreach ($otherTableJoins as $logTable => $column) {
+            $this->addMissingTablesForOtherTableJoin($logTable, $tableName);
+        }
+
+        $this->tables->addTableToJoin($tableName);
+    }
+
     /**
      * Generate the join sql based on the needed tables
      * @throws Exception if tables can't be joined
@@ -206,6 +243,15 @@ class JoinGenerator
 
                 break;
             }
+
+            $otherJoins = $logTable->getWaysToJoinToOtherLogTables();
+            foreach ($otherJoins as $joinTable => $column) {
+                if($availableLogTable->getName() == $joinTable) {
+                    $join = sprintf("`%s`.`%s` = `%s`.`%s`", $table, $column, $availableLogTable->getName(), $column);
+                    break;
+                }
+            }
+
         }
 
         if (!isset($join)) {
diff --git a/core/DataAccess/LogQueryBuilder/JoinTables.php b/core/DataAccess/LogQueryBuilder/JoinTables.php
index 70d4b75610..cf06382904 100644
--- a/core/DataAccess/LogQueryBuilder/JoinTables.php
+++ b/core/DataAccess/LogQueryBuilder/JoinTables.php
@@ -19,6 +19,40 @@ class JoinTables extends \ArrayObject
      */
     private $logTableProvider;
 
+    // NOTE: joins can be specified explicitly as arrays w/ 'joinOn' keys or implicitly as table names. when
+    // table names are used, the joins dependencies are assumed based on how we want to order those joins.
+    // the below table list the possible dependencies of each table, and is specifically designed to enforce
+    // the following order:
+    // log_link_visit_action, log_action, log_visit, log_conversion, log_conversion_item
+    // which means if an array is supplied where log_visit comes before log_link_visitAction, it will
+    // be moved to after it.
+    private $implicitTableDependencies = [
+        'log_link_visit_action' => [
+            // empty
+        ],
+        'log_action' => [
+            'log_link_visit_action',
+            'log_conversion',
+            'log_conversion_item',
+            'log_visit',
+        ],
+        'log_visit' => [
+            'log_link_visit_action',
+            'log_action',
+        ],
+        'log_conversion' => [
+            'log_link_visit_action',
+            'log_action',
+            'log_visit',
+        ],
+        'log_conversion_item' => [
+            'log_link_visit_action',
+            'log_action',
+            'log_visit',
+            'log_conversion',
+        ],
+    ];
+
     /**
      * Tables constructor.
      * @param LogTablesProvider $logTablesProvider
@@ -125,6 +159,73 @@ class JoinTables extends \ArrayObject
         $this->exchangeArray($sorted);
     }
 
+    public function isTableJoinableOnVisit($tableToCheck)
+    {
+        $table = $this->getLogTable($tableToCheck);
+
+        if (empty($table)) {
+            return false;
+        }
+
+        if ($table->getColumnToJoinOnIdVisit()) {
+            return true;
+        }
+
+        if ($table->getLinkTableToBeAbleToJoinOnVisit()) {
+            return true;
+        }
+
+        $otherWays = $table->getWaysToJoinToOtherLogTables();
+
+        if (empty($otherWays)) {
+            return false;
+        }
+
+        foreach ($otherWays as $logTable => $column) {
+            if ($logTable == 'log_visit' || $this->isTableJoinableOnVisit($logTable)) {
+                return true;
+            }
+        }
+
+        return false;
+    }
+
+    public function isTableJoinableOnAction($tableToCheck)
+    {
+        $table = $this->getLogTable($tableToCheck);
+
+        if (empty($table)) {
+            return false;
+        }
+
+        if ($table->getColumnToJoinOnIdAction()) {
+            return true;
+        }
+
+        $otherWays = $table->getWaysToJoinToOtherLogTables();
+
+        if (empty($otherWays)) {
+            return false;
+        }
+
+        foreach ($otherWays as $logTable => $column) {
+            if ($logTable == 'log_action' || $this->isTableJoinableOnAction($logTable)) {
+                return true;
+            }
+        }
+
+        return false;
+    }
+
+    public function addTableDependency($table, $dependentTable)
+    {
+        if (!empty($this->implicitTableDependencies[$table])) {
+            return;
+        }
+
+        $this->implicitTableDependencies[$table] = [$dependentTable];
+    }
+
     private function checkTableCanBeUsedForSegmentation($tableName)
     {
         if (!is_array($tableName) && !$this->getLogTable($tableName)) {
@@ -154,39 +255,7 @@ class JoinTables extends \ArrayObject
 
     private function assumeImplicitJoinDependencies($allTablesToQuery, $table)
     {
-        // NOTE: joins can be specified explicitly as arrays w/ 'joinOn' keys or implicitly as table names. when
-        // table names are used, the joins dependencies are assumed based on how we want to order those joins.
-        // the below table list the possible dependencies of each table, and is specifically designed to enforce
-        // the following order:
-        // log_link_visit_action, log_action, log_visit, log_conversion, log_conversion_item
-        // which means if an array is supplied where log_visit comes before log_link_visitAction, it will
-        // be moved to after it.
-        $implicitTableDependencies = [
-            'log_link_visit_action' => [
-                // empty
-            ],
-            'log_action' => [
-                'log_link_visit_action',
-                'log_conversion',
-                'log_conversion_item',
-                'log_visit',
-            ],
-            'log_visit' => [
-                'log_link_visit_action',
-                'log_action',
-            ],
-            'log_conversion' => [
-                'log_link_visit_action',
-                'log_action',
-                'log_visit',
-            ],
-            'log_conversion_item' => [
-                'log_link_visit_action',
-                'log_action',
-                'log_visit',
-                'log_conversion',
-            ],
-        ];
+        $implicitTableDependencies = $this->implicitTableDependencies;
 
         $result = [];
         if (isset($implicitTableDependencies[$table])) {