From 572debd0e820bfaa4d55b59eb19544fa245aa579 Mon Sep 17 00:00:00 2001 From: Dhruv Date: Sun, 1 Jun 2014 13:38:27 +0530 Subject: [ticket/11445] Optimize no of queries in get_global_subscriptions PHPBB3-11445 --- phpBB/phpbb/notification/manager.php | 31 ++++++++++++++++++------------- 1 file changed, 18 insertions(+), 13 deletions(-) (limited to 'phpBB/phpbb') diff --git a/phpBB/phpbb/notification/manager.php b/phpBB/phpbb/notification/manager.php index c3539e76df..8378af2d99 100644 --- a/phpBB/phpbb/notification/manager.php +++ b/phpBB/phpbb/notification/manager.php @@ -587,26 +587,34 @@ class manager $subscriptions = array(); + $sql = 'SELECT method, notify, item_type + FROM ' . $this->user_notifications_table . ' + WHERE user_id = ' . (int) $user_id . ' + AND item_id = 0'; + + $result = $this->db->sql_query($sql); + $rows = array(); + + while ($row = $this->db->sql_fetchrow($result)) + { + $rows[$row['item_type']][] = $row; + } + + $this->db->sql_freeresult($result); + foreach ($this->get_subscription_types() as $group_name => $types) { foreach ($types as $id => $type) { - $sql = 'SELECT method, notify - FROM ' . $this->user_notifications_table . ' - WHERE user_id = ' . (int) $user_id . " - AND item_type = '" . $this->db->sql_escape($id) . "' - AND item_id = 0"; - $result = $this->db->sql_query($sql); - - $row = $this->db->sql_fetchrow($result); - if (!$row) + + if (empty($rows[$id])) { // No rows at all, default to '' $subscriptions[$id] = array(''); } else { - do + foreach ($rows[$id] as $row) { if (!$row['notify']) { @@ -620,10 +628,7 @@ class manager $subscriptions[$id][] = $row['method']; } - while ($row = $this->db->sql_fetchrow($result)); } - - $this->db->sql_freeresult($result); } } -- cgit v1.2.1 From ac74dc876ce215b3cc2c13f497cdd6c574bf65bc Mon Sep 17 00:00:00 2001 From: Dhruv Date: Sat, 7 Jun 2014 01:00:09 +0530 Subject: [ticket/11445] Remove unused foreach key $group_name PHPBB3-11445 --- phpBB/phpbb/notification/manager.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'phpBB/phpbb') diff --git a/phpBB/phpbb/notification/manager.php b/phpBB/phpbb/notification/manager.php index 8378af2d99..ad9cd9a3ff 100644 --- a/phpBB/phpbb/notification/manager.php +++ b/phpBB/phpbb/notification/manager.php @@ -602,7 +602,7 @@ class manager $this->db->sql_freeresult($result); - foreach ($this->get_subscription_types() as $group_name => $types) + foreach ($this->get_subscription_types() as $types) { foreach ($types as $id => $type) { -- cgit v1.2.1 From f5415619ebc49e370a4ec06cd6b664fb2456ccfe Mon Sep 17 00:00:00 2001 From: Dhruv Date: Sat, 7 Jun 2014 01:34:20 +0530 Subject: [ticket/11445] Move get user's notification code into its own method PHPBB3-11445 --- phpBB/phpbb/notification/manager.php | 43 ++++++++++++++++++++++++------------ 1 file changed, 29 insertions(+), 14 deletions(-) (limited to 'phpBB/phpbb') diff --git a/phpBB/phpbb/notification/manager.php b/phpBB/phpbb/notification/manager.php index ad9cd9a3ff..b787b624f6 100644 --- a/phpBB/phpbb/notification/manager.php +++ b/phpBB/phpbb/notification/manager.php @@ -574,49 +574,64 @@ class manager return $subscription_methods; } + /** - * Get global subscriptions (item_id = 0) + * Get user's notification data * - * @param bool|int $user_id The user_id to add the subscription for (bool false for current user) + * @param int $user_id The user_id of the user to get the notifications for * - * @return array Subscriptions + * @return array User's notification */ - public function get_global_subscriptions($user_id = false) + protected function get_user_notifications($user_id) { - $user_id = ($user_id === false) ? $this->user->data['user_id'] : $user_id; - - $subscriptions = array(); - $sql = 'SELECT method, notify, item_type FROM ' . $this->user_notifications_table . ' WHERE user_id = ' . (int) $user_id . ' AND item_id = 0'; $result = $this->db->sql_query($sql); - $rows = array(); + $user_notifications = array(); while ($row = $this->db->sql_fetchrow($result)) { - $rows[$row['item_type']][] = $row; + $user_notifications[$row['item_type']][] = $row; } $this->db->sql_freeresult($result); + return $user_notifications; + } + + /** + * Get global subscriptions (item_id = 0) + * + * @param bool|int $user_id The user_id to add the subscription for (bool false for current user) + * + * @return array Subscriptions + */ + public function get_global_subscriptions($user_id = false) + { + $user_id = ($user_id === false) ? $this->user->data['user_id'] : $user_id; + + $subscriptions = array(); + + $user_notifications = $this->get_user_notifications($user_id); + foreach ($this->get_subscription_types() as $types) { foreach ($types as $id => $type) { - if (empty($rows[$id])) + if (empty($user_notifications[$id])) { // No rows at all, default to '' $subscriptions[$id] = array(''); } else { - foreach ($rows[$id] as $row) + foreach ($user_notifications[$id] as $user_notification) { - if (!$row['notify']) + if (!$user_notification['notify']) { continue; } @@ -626,7 +641,7 @@ class manager $subscriptions[$id] = array(); } - $subscriptions[$id][] = $row['method']; + $subscriptions[$id][] = $user_notification['method']; } } } -- cgit v1.2.1