avoid using group by and select min for tracker actions query to avoid tmp tables refs #14535 (#14584)

2 files changed, 35 insertions, 13 deletions

diff --git a/core/Tracker/Model.php b/core/Tracker/Model.php
index 16fc04fd92..b5bb57edaa 100644
--- a/core/Tracker/Model.php
+++ b/core/Tracker/Model.php
@@ -215,8 +215,7 @@ class Model
      */
     public function getIdsAction($actionsNameAndType)
     {
-        $sql = "SELECT MIN(idaction) as idaction, type, name FROM " . Common::prefixTable('log_action')
-             . " WHERE";
+        $sql = "SELECT `idaction`, `type`, `name` FROM " . Common::prefixTable('log_action') . " WHERE";
         $bind = array();
 
         $i = 0;
@@ -239,16 +238,39 @@ class Model
             $i++;
         }
 
-        $sql .= " GROUP BY type, hash, name";
-
         // Case URL & Title are empty
         if (empty($bind)) {
             return false;
         }
 
-        $actionIds = $this->getDb()->fetchAll($sql, $bind);
+        $rows = $this->getDb()->fetchAll($sql, $bind);
+
+        $actionsPerType = array();
+
+        foreach ($rows as $row) {
+            $name = $row['name'];
+            $type = $row['type'];
+
+            if (!isset($actionsPerType[$type])) {
+                $actionsPerType[$type] = array();
+            }
+
+            if (!isset($actionsPerType[$type][$name])) {
+                $actionsPerType[$type][$name] = $row;
+            } elseif ($row['idaction'] < $actionsPerType[$type][$name]['idaction']) {
+                // keep the lowest idaction for this type, name
+                $actionsPerType[$type][$name] = $row;
+            }
+        }
+
+        $actionsToReturn = array();
+        foreach ($actionsPerType as $type => $actionsPerName) {
+            foreach ($actionsPerName as $actionPerName) {
+                $actionsToReturn[] = $actionPerName;
+            }
+        }
 
-        return $actionIds;
+        return $actionsToReturn;
     }
 
     public function updateEcommerceItem($originalIdOrder, $newItem)
diff --git a/tests/PHPUnit/Integration/Tracker/ModelTest.php b/tests/PHPUnit/Integration/Tracker/ModelTest.php
index 27f8f0c7ed..f8f2e73a13 100644
--- a/tests/PHPUnit/Integration/Tracker/ModelTest.php
+++ b/tests/PHPUnit/Integration/Tracker/ModelTest.php
@@ -65,13 +65,18 @@ class ModelTest extends IntegrationTestCase
 
         $expectedResult = array(
             array(
+                'idaction' => '2',
+                'type' => '1',
+                'name' => 'action1'
+            ),
+            array(
                 'idaction' => '3',
                 'type' => '1',
                 'name' => 'ACTION1'
             ),
             array(
-                'idaction' => '2',
-                'type' => '1',
+                'idaction' => '4',
+                'type' => '2',
                 'name' => 'action1'
             ),
             array(
@@ -79,11 +84,6 @@ class ModelTest extends IntegrationTestCase
                 'type' => '2',
                 'name' => 'action2'
             ),
-            array(
-                'idaction' => '4',
-                'type' => '2',
-                'name' => 'action1'
-            )
         );
         $this->assertEquals($expectedResult, $result);
     }