[Phpbb-checkins] r10045 - /branches/phpBB-3_0_0/phpBB/includes/functions.php

[Ruslan U. <rx...@ph...>]

Author: rxu
Date: Sun Aug 23 12:12:22 2009
New Revision: 10045

Log:
More unread posts search adjustment.
Authorised by: AcydBurn

Modified:
    branches/phpBB-3_0_0/phpBB/includes/functions.php

Modified: branches/phpBB-3_0_0/phpBB/includes/functions.php
==============================================================================
*** branches/phpBB-3_0_0/phpBB/includes/functions.php (original)
--- branches/phpBB-3_0_0/phpBB/includes/functions.php Sun Aug 23 12:12:22 2009
***************
*** 1649,1659 ****
  *
  * @param int $user_id			User ID (or false for current user)
  * @param string $sql_extra		Extra WHERE SQL statement
  *
  * @return array[int][int]		Topic ids as keys, mark_time of topic as value
  * @author rxu
  */
! function get_unread_topics_list($user_id = false, $sql_extra = '')
  {
  	global $config, $db, $user;
  
--- 1649,1660 ----
  *
  * @param int $user_id			User ID (or false for current user)
  * @param string $sql_extra		Extra WHERE SQL statement
+ * @param string $sql_limit		Limits the size of unread topics list
  *
  * @return array[int][int]		Topic ids as keys, mark_time of topic as value
  * @author rxu
  */
! function get_unread_topics_list($user_id = false, $sql_extra = '', $sql_limit = 1001)
  {
  	global $config, $db, $user;
  
***************
*** 1667,1698 ****
  
  	if ($config['load_db_lastread'] && $user->data['is_registered'])
  	{
- 		// List of the tracked forums (not ideal, hope the better way will be found)
- 		// This list is to fetch later the forums user never read (fully) before
- 		$sql = 'SELECT forum_id
- 			FROM ' . FORUMS_TRACK_TABLE . "
- 			WHERE user_id = $user_id";
- 		$result = $db->sql_query($sql);
- 
- 		while ($row = $db->sql_fetchrow($result))
- 		{
- 			$tracked_forums_list[] = (int) $row['forum_id'];
- 		}
- 		$db->sql_freeresult($result);
- 
  		// Get list of the unread topics - on topics tracking as the first step
  		$sql = 'SELECT t.topic_id, t.topic_last_post_time, tt.mark_time
  			FROM ' . TOPICS_TABLE . ' t, ' . TOPICS_TRACK_TABLE . " tt
  			WHERE t.topic_id = tt.topic_id
- 				AND t.topic_last_post_time >= tt.mark_time
  				AND tt.user_id = $user_id
  			$sql_extra
  			ORDER BY t.topic_last_post_time DESC";
! 		$result = $db->sql_query($sql);
  
  		while ($row = $db->sql_fetchrow($result))
  		{
! 			if ($row['topic_last_post_time'] == $row['mark_time'])
  			{
  				// Check if there're read topics for the forums having unread ones
  				$read_topics_list[$row['topic_id']] = (int) $row['mark_time'];
--- 1668,1685 ----
  
  	if ($config['load_db_lastread'] && $user->data['is_registered'])
  	{
  		// Get list of the unread topics - on topics tracking as the first step
  		$sql = 'SELECT t.topic_id, t.topic_last_post_time, tt.mark_time
  			FROM ' . TOPICS_TABLE . ' t, ' . TOPICS_TRACK_TABLE . " tt
  			WHERE t.topic_id = tt.topic_id
  				AND tt.user_id = $user_id
  			$sql_extra
  			ORDER BY t.topic_last_post_time DESC";
! 		$result = $db->sql_query_limit($sql, $sql_limit);
  
  		while ($row = $db->sql_fetchrow($result))
  		{
! 			if ($row['topic_last_post_time'] <= $row['mark_time'])
  			{
  				// Check if there're read topics for the forums having unread ones
  				$read_topics_list[$row['topic_id']] = (int) $row['mark_time'];
***************
*** 1704,1749 ****
  		}
  		$db->sql_freeresult($result);
  
! 		// Get the full list of the tracked topics
  		$tracked_topics_list = array_merge(array_keys($unread_topics_list), array_keys($read_topics_list));
  
! 		// Get list of the unread topics - on forums tracking as the second step
! 		// We don't take in account topics tracked before
! 		$sql = 'SELECT t.topic_id, ft.mark_time
! 			FROM ' . TOPICS_TABLE . ' t, ' . FORUMS_TRACK_TABLE . ' ft
! 			WHERE t.forum_id = ft.forum_id
! 				AND t.topic_last_post_time > ft.mark_time
! 				AND ' . $db->sql_in_set('t.topic_id', $tracked_topics_list, true, true) . "
! 				AND ft.user_id = $user_id
! 			$sql_extra
! 			ORDER BY t.topic_last_post_time DESC";
! 		$result = $db->sql_query($sql);
! 
! 		while ($row = $db->sql_fetchrow($result))
  		{
! 			$unread_topics_list[$row['topic_id']] = (int) $row['mark_time'];
! 		}
! 		$db->sql_freeresult($result);
  
! 		// Refresh the full list of the tracked topics
! 		unset($tracked_topics_list);
! 		$tracked_topics_list = array_merge(array_keys($unread_topics_list), array_keys($read_topics_list));
  
! 		// And the last step - find unread topics were not found before (that can mean a user has never read some forums)
! 		$sql = 'SELECT t.topic_id
! 			FROM ' . TOPICS_TABLE . ' t
! 			WHERE t.topic_last_post_time > ' . (int) $user->data['user_lastmark'] . '
! 				AND ' . $db->sql_in_set('t.topic_id', $tracked_topics_list, true, true) . '
! 				AND ' . $db->sql_in_set('t.forum_id', $tracked_forums_list, true, true) . "
! 			$sql_extra
! 			ORDER BY t.topic_last_post_time DESC";
! 		$result = $db->sql_query_limit($sql, 1000);
  
! 		while ($row = $db->sql_fetchrow($result))
! 		{
! 			$unread_topics_list[$row['topic_id']] = (int) $user->data['user_lastmark'];
  		}
- 		$db->sql_freeresult($result);
  	}
  	else if ($config['load_anon_lastread'] || $user->data['is_registered'])
  	{
--- 1691,1757 ----
  		}
  		$db->sql_freeresult($result);
  
! 		// Get the full list of the tracked topics and unread topics count
  		$tracked_topics_list = array_merge(array_keys($unread_topics_list), array_keys($read_topics_list));
+ 		$unread_list_count = sizeof($unread_topics_list);
  
! 		if ($unread_list_count < $sql_limit)
  		{
! 			// Get list of the unread topics - on forums tracking as the second step
! 			// We don't take in account topics tracked before
! 			$sql = 'SELECT t.topic_id, ft.mark_time
! 				FROM ' . TOPICS_TABLE . ' t, ' . FORUMS_TRACK_TABLE . ' ft
! 				WHERE t.forum_id = ft.forum_id
! 					AND t.topic_last_post_time > ft.mark_time
! 					AND ' . $db->sql_in_set('t.topic_id', $tracked_topics_list, true, true) . "
! 					AND ft.user_id = $user_id
! 				$sql_extra
! 				ORDER BY t.topic_last_post_time DESC";
! 			$result = $db->sql_query_limit($sql, ($sql_limit - $unread_list_count));
  
! 			while ($row = $db->sql_fetchrow($result))
! 			{
! 				$unread_topics_list[$row['topic_id']] = (int) $row['mark_time'];
! 			}
! 			$db->sql_freeresult($result);
  
! 			// Refresh the full list of the tracked topics and unread topics count
! 			unset($tracked_topics_list);
! 			$tracked_topics_list = array_merge(array_keys($unread_topics_list), array_keys($read_topics_list));
! 			$unread_list_count = sizeof($unread_topics_list);
  
! 			if ($unread_list_count < $sql_limit)
! 			{
! 				// List of the tracked forums (not ideal, hope the better way will be found)
! 				// This list is to fetch later the forums user never read (fully) before
! 				$sql = 'SELECT forum_id
! 					FROM ' . FORUMS_TRACK_TABLE . "
! 					WHERE user_id = $user_id";
! 				$result = $db->sql_query($sql);
! 
! 				while ($row = $db->sql_fetchrow($result))
! 				{
! 					$tracked_forums_list[] = (int) $row['forum_id'];
! 				}
! 				$db->sql_freeresult($result);
! 
! 				// And the last step - find unread topics were not found before (that can mean a user has never read some forums)
! 				$sql = 'SELECT t.topic_id
! 					FROM ' . TOPICS_TABLE . ' t
! 					WHERE t.topic_last_post_time > ' . (int) $user->data['user_lastmark'] . '
! 						AND ' . $db->sql_in_set('t.topic_id', $tracked_topics_list, true, true) . '
! 						AND ' . $db->sql_in_set('t.forum_id', $tracked_forums_list, true, true) . "
! 					$sql_extra
! 					ORDER BY t.topic_last_post_time DESC";
! 				$result = $db->sql_query_limit($sql, ($sql_limit - $unread_list_count));
! 
! 				while ($row = $db->sql_fetchrow($result))
! 				{
! 					$unread_topics_list[$row['topic_id']] = (int) $user->data['user_lastmark'];
! 				}
! 				$db->sql_freeresult($result);
! 			}
  		}
  	}
  	else if ($config['load_anon_lastread'] || $user->data['is_registered'])
  	{