Reduce number of SQL queries for actions

2 files changed, 68 insertions, 18 deletions

diff --git a/app/Model/Action.php b/app/Model/Action.php
index c368b494..5fcfbaa7 100644
--- a/app/Model/Action.php
+++ b/app/Model/Action.php
@@ -34,10 +34,8 @@ class Action extends Base
 
         if (! empty($project_ids)) {
             $actions = $this->db->table(self::TABLE)->in('project_id', $project_ids)->findAll();
-
-            foreach ($actions as &$action) {
-                $action['params'] = $this->actionParameter->getAll($action['id']);
-            }
+            $params = $this->actionParameter->getAllByActions(array_column($actions, 'id'));
+            $this->attachParamsToActions($actions, $params);
         }
 
         return $actions;
@@ -53,12 +51,8 @@ class Action extends Base
     public function getAllByProject($project_id)
     {
         $actions = $this->db->table(self::TABLE)->eq('project_id', $project_id)->findAll();
-
-        foreach ($actions as &$action) {
-            $action['params'] = $this->actionParameter->getAll($action['id']);
-        }
-
-        return $actions;
+        $params = $this->actionParameter->getAllByActions(array_column($actions, 'id'));
+        return $this->attachParamsToActions($actions, $params);
     }
 
     /**
@@ -70,12 +64,8 @@ class Action extends Base
     public function getAll()
     {
         $actions = $this->db->table(self::TABLE)->findAll();
-
-        foreach ($actions as &$action) {
-            $action['params'] = $this->actionParameter->getAll($action['id']);
-        }
-
-        return $actions;
+        $params = $this->actionParameter->getAll();
+        return $this->attachParamsToActions($actions, $params);
     }
 
     /**
@@ -90,13 +80,30 @@ class Action extends Base
         $action = $this->db->table(self::TABLE)->eq('id', $action_id)->findOne();
 
         if (! empty($action)) {
-            $action['params'] = $this->actionParameter->getAll($action_id);
+            $action['params'] = $this->actionParameter->getAllByAction($action_id);
         }
 
         return $action;
     }
 
     /**
+     * Attach parameters to actions
+     *
+     * @access private
+     * @param  array  &$actions
+     * @param  array  &$params
+     * @return array
+     */
+    private function attachParamsToActions(array &$actions, array &$params)
+    {
+        foreach ($actions as &$action) {
+            $action['params'] = isset($params[$action['id']]) ? $params[$action['id']] : array();
+        }
+
+        return $actions;
+    }
+
+    /**
      * Remove an action
      *
      * @access public
diff --git a/app/Model/ActionParameter.php b/app/Model/ActionParameter.php
index f170ef66..1e4d7544 100644
--- a/app/Model/ActionParameter.php
+++ b/app/Model/ActionParameter.php
@@ -24,10 +24,53 @@ class ActionParameter extends Base
      * Get all action params
      *
      * @access public
+     * @return array
+     */
+    public function getAll()
+    {
+        $params = $this->db->table(self::TABLE)->findAll();
+        return $this->toDictionary($params);
+    }
+
+    /**
+     * Get all params for a list of actions
+     *
+     * @access public
+     * @param  array $action_ids
+     * @return array
+     */
+    public function getAllByActions(array $action_ids)
+    {
+        $params = $this->db->table(self::TABLE)->in('action_id', $action_ids)->findAll();
+        return $this->toDictionary($params);
+    }
+
+    /**
+     * Build params dictionary
+     *
+     * @access private
+     * @param  array  $params
+     * @return array
+     */
+    private function toDictionary(array $params)
+    {
+        $result = array();
+
+        foreach ($params as $param) {
+            $result[$param['action_id']][$param['name']] = $param['value'];
+        }
+
+        return $result;
+    }
+
+    /**
+     * Get all action params for a given action
+     *
+     * @access public
      * @param  integer $action_id
      * @return array
      */
-    public function getAll($action_id)
+    public function getAllByAction($action_id)
     {
         return $this->db->hashtable(self::TABLE)->eq('action_id', $action_id)->getAll('name', 'value');
     }