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

7 files changed, 114 insertions, 11 deletions

diff --git a/tests/PHPUnit/Framework/Mock/Plugin/CustomUserLogTable.php b/tests/PHPUnit/Framework/Mock/Plugin/CustomUserLogTable.php
new file mode 100644
index 0000000000..e06e732d16
--- /dev/null
+++ b/tests/PHPUnit/Framework/Mock/Plugin/CustomUserLogTable.php
@@ -0,0 +1,28 @@
+<?php
+
+namespace Piwik\Tests\Framework\Mock\Plugin;
+
+use Piwik\Tracker\LogTable;
+
+class CustomUserLogTable extends LogTable
+{
+    public function getName()
+    {
+        return 'log_custom';
+    }
+
+    public function getIdColumn()
+    {
+        return 'user_id';
+    }
+
+    public function getPrimaryKey()
+    {
+        return ['user_id'];
+    }
+
+    public function getWaysToJoinToOtherLogTables()
+    {
+        return ['log_visit' => 'user_id'];
+    }
+}
diff --git a/tests/PHPUnit/Framework/Mock/Plugin/LogTablesProvider.php b/tests/PHPUnit/Framework/Mock/Plugin/LogTablesProvider.php
index c563e250cd..f4a643018a 100644
--- a/tests/PHPUnit/Framework/Mock/Plugin/LogTablesProvider.php
+++ b/tests/PHPUnit/Framework/Mock/Plugin/LogTablesProvider.php
@@ -21,7 +21,9 @@ class LogTablesProvider extends \Piwik\Plugin\LogTablesProvider
             new Action(),
             new LinkVisitAction(),
             new ConversionItem(),
-            new Conversion()
+            new Conversion(),
+            new CustomUserLogTable(),
+            new OtherCustomUserLogTable()
         );
     }
 
diff --git a/tests/PHPUnit/Framework/Mock/Plugin/OtherCustomUserLogTable.php b/tests/PHPUnit/Framework/Mock/Plugin/OtherCustomUserLogTable.php
new file mode 100644
index 0000000000..68a84855cd
--- /dev/null
+++ b/tests/PHPUnit/Framework/Mock/Plugin/OtherCustomUserLogTable.php
@@ -0,0 +1,28 @@
+<?php
+
+namespace Piwik\Tests\Framework\Mock\Plugin;
+
+use Piwik\Tracker\LogTable;
+
+class OtherCustomUserLogTable extends LogTable
+{
+    public function getName()
+    {
+        return 'log_custom_other';
+    }
+
+    public function getIdColumn()
+    {
+        return 'other_id';
+    }
+
+    public function getPrimaryKey()
+    {
+        return ['other_id'];
+    }
+
+    public function getWaysToJoinToOtherLogTables()
+    {
+        return ['log_custom' => 'other_id'];
+    }
+}
diff --git a/tests/PHPUnit/Unit/DataAccess/LogQueryBuilder/JoinGeneratorTest.php b/tests/PHPUnit/Unit/DataAccess/LogQueryBuilder/JoinGeneratorTest.php
index 1012e58ec3..6f77720372 100644
--- a/tests/PHPUnit/Unit/DataAccess/LogQueryBuilder/JoinGeneratorTest.php
+++ b/tests/PHPUnit/Unit/DataAccess/LogQueryBuilder/JoinGeneratorTest.php
@@ -67,12 +67,6 @@ class JoinGeneratorTest extends \PHPUnit_Framework_TestCase
         $this->assertEquals($expected, $generator->getJoinString());
     }
 
-    public function test_generate_getJoinString_OnlyOneTable()
-    {
-        $generator = $this->generate(array('log_visit'));
-        $this->assertEquals('log_visit AS log_visit', $generator->getJoinString());
-    }
-
     public function test_generate_getJoinString_OnlyOneActionTable()
     {
         $generator = $this->generate(array('log_action'));
@@ -87,6 +81,43 @@ class JoinGeneratorTest extends \PHPUnit_Framework_TestCase
         $this->assertEquals($expected, $generator->getJoinString());
     }
 
+    public function test_generate_getJoinString_JoinCustomVisitTable()
+    {
+        $generator = $this->generate(array('log_visit', 'log_custom'));
+        $this->assertEquals('log_visit AS log_visit LEFT JOIN log_custom AS log_custom ON `log_custom`.`user_id` = `log_visit`.`user_id`', $generator->getJoinString());
+    }
+
+    public function test_generate_getJoinString_JoinMultipleCustomVisitTable()
+    {
+        $generator = $this->generate(array('log_visit', 'log_custom_other', 'log_custom'));
+        $this->assertEquals('log_visit AS log_visit LEFT JOIN log_custom AS log_custom ON `log_custom`.`user_id` = `log_visit`.`user_id` LEFT JOIN log_custom_other AS log_custom_other ON `log_custom_other`.`other_id` = `log_custom`.`other_id`', $generator->getJoinString());
+    }
+
+    public function test_generate_getJoinString_JoinMultipleCustomVisitTableWithMissingOne()
+    {
+        $generator = $this->generate(array('log_visit', 'log_custom_other'));
+        $this->assertEquals('log_visit AS log_visit LEFT JOIN log_custom AS log_custom ON `log_custom`.`user_id` = `log_visit`.`user_id` LEFT JOIN log_custom_other AS log_custom_other ON `log_custom_other`.`other_id` = `log_custom`.`other_id`', $generator->getJoinString());
+    }
+
+    /**
+     * @expectedException \Exception
+     * @expectedExceptionMessage Table 'log_visit' can't be joined for segmentation
+     *
+     * Note: the exception reports `log_visit` and not `log_custom` as it resolves the dependencies as so resolves
+     * from `log_custom` to `log_visit` but is then not able to find a way to join `log_visit` with `log_action`
+     */
+    public function test_generate_getJoinString_CustomVisitTableCantBeJoinedWithAction()
+    {
+        $generator = $this->generate(array('log_action', 'log_custom'));
+        $generator->getJoinString();
+    }
+
+    public function test_generate_getJoinString_JoinCustomVisitTableMultiple()
+    {
+        $generator = $this->generate(array('log_visit', 'log_action', 'log_custom'));
+        $this->assertEquals('log_visit AS log_visit LEFT JOIN log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = log_visit.idvisit LEFT JOIN log_action AS log_action ON log_link_visit_action.idaction_url = log_action.idaction LEFT JOIN log_custom AS log_custom ON `log_custom`.`user_id` = `log_visit`.`user_id`', $generator->getJoinString());
+    }
+
     public function test_generate_getJoinString_manuallyJoinedAlready()
     {
         $generator = $this->generate(array(
diff --git a/tests/PHPUnit/Unit/DataAccess/LogQueryBuilder/JoinTablesTest.php b/tests/PHPUnit/Unit/DataAccess/LogQueryBuilder/JoinTablesTest.php
index 33f5c4cdd0..6bf7395549 100644
--- a/tests/PHPUnit/Unit/DataAccess/LogQueryBuilder/JoinTablesTest.php
+++ b/tests/PHPUnit/Unit/DataAccess/LogQueryBuilder/JoinTablesTest.php
@@ -39,6 +39,20 @@ class JoinTablesTest extends \PHPUnit_Framework_TestCase
         $this->makeTables(array('log_visit', 'log_foo_bar_baz'));
     }
 
+    public function test_hasJoinedTable_custom()
+    {
+        $tables = $this->makeTables(array('log_visit', 'log_custom'));
+        $this->assertTrue($tables->hasJoinedTable('log_visit'));
+        $this->assertTrue($tables->hasJoinedTable('log_custom'));
+    }
+
+    public function test_hasJoinedTable_custom2()
+    {
+        $tables = $this->makeTables(array('log_visit', 'log_custom_other'));
+        $this->assertTrue($tables->hasJoinedTable('log_visit'));
+        $this->assertTrue($tables->hasJoinedTable('log_custom_other'));
+    }
+
     public function test_hasJoinedTable_shouldDetectIfTableIsAlreadyAdded()
     {
         $this->assertTrue($this->tables->hasJoinedTable('log_visit'));
diff --git a/tests/UI/expected-screenshots/UIIntegrationTest_admin_plugins.png b/tests/UI/expected-screenshots/UIIntegrationTest_admin_plugins.png
index 97f91fba8e..75088cdb4a 100644
--- a/tests/UI/expected-screenshots/UIIntegrationTest_admin_plugins.png
+++ b/tests/UI/expected-screenshots/UIIntegrationTest_admin_plugins.png
@@ -1,3 +1,3 @@
 version https://git-lfs.github.com/spec/v1
-oid sha256:e25803802e946ac8d73770defc818e29ef83265cad9d4692dc5d3bdeeb197013
-size 1058541
+oid sha256:3dcf01d06e413501b56bd58e24738864406092435974b3dac45650d46dee4dfa
+size 1068670
diff --git a/tests/UI/expected-screenshots/UIIntegrationTest_admin_plugins_no_internet.png b/tests/UI/expected-screenshots/UIIntegrationTest_admin_plugins_no_internet.png
index 8371292920..fe3156ddae 100644
--- a/tests/UI/expected-screenshots/UIIntegrationTest_admin_plugins_no_internet.png
+++ b/tests/UI/expected-screenshots/UIIntegrationTest_admin_plugins_no_internet.png
@@ -1,3 +1,3 @@
 version https://git-lfs.github.com/spec/v1
-oid sha256:62a82dd8080ba626cb4350493e55173a5008a34b2e3036ed8631b37e70146571
-size 1059908
+oid sha256:e0869595907cda432fd31b52977c3c055df0a9969b4bd69fd9eb86b17e25e4d5
+size 1069291