I am using SMF 2.0 RC1.2.
[Description]
It should count unread topics and replies and put it into $context array.
Mod that did that trick was written for SMF 1.x and is not working in SMF 2.0 - http://custom.simplemachines.org/mods/index.php?mod=804
[Permissions]
None needed.
[Feature Set]
None.
As per the mod's page, this would have a performance impact on larger forums.
It shouldn't be that hard to port the mod to 2.0 - mostly it's just the DB query.
When I modified it all I get are errors
Notice: Undefined index: is_guest in /home/informatyka/www/Sources/Load.php on line 553
Notice: Undefined variable: smcFunc in /home/informatyka/www/Sources/Load.php on line 555
Fatal error: Function name must be a string in /home/informatyka/www/Sources/Load.php on line 555
Here is code
//Count unread replies on index MOD- Start
$user_info['unread_topics'] = 0;
$user_info['unread_replies'] = 0;
//Only count for members!
if (!$user_info['is_guest'])
{
$request = $smcFunc['db_query']('', '
SELECT MIN(lmr.id_msg)
FROM {db_prefix}boards AS b
LEFT JOIN {db_prefix}log_mark_read AS lmr ON (lmr.id_board = b.id_board AND lmr.id_member = {int:id_member})
WHERE {query_wanna_see_board}',
array(
'id_member' => $id_member
)
);
list ($earliest_msg) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request);
// This is needed in case of topics marked unread.
if (empty($earliest_msg))
$earliest_msg = 0;
else
{
// This query is pretty slow, but it's needed to ensure nothing crucial is ignored.
$request = $smcFunc['db_query']('', '
SELECT MIN(id_msg)
FROM {db_prefix}log_topics
WHERE id_member = $id_member');
list ($earliest_msg2) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request);
if ($earliest_msg2 == 0)
$earliest_msg2 = -1;
$earliest_msg = min($earliest_msg2, $earliest_msg);
}
//Select the boards to choose from... all
$request = $smcFunc['db_query']('', '
SELECT b.id_board
FROM {db_prefix}boards AS b
WHERE {query_wanna_see_board}' . (!empty($modSettings['recycle_enable']) && $modSettings['recycle_board'] > 0 ? '
AND b.id_board != ' . (int) $modSettings['recycle_board'] : ''));
$boards = array();
while ($row = $smcFunc['db_fetch_assoc']($request))
$boards[] = $row['id_board'];
$smcFunc['db_free_result']($request);
if (empty($boards))
fatal_lang_error('error_no_boards_selected');
$query_this_board = 'id_board IN (' . implode(', ', $boards) . ')';
//Count unread topics
$request = $smcFunc['db_query']('', '
SELECT COUNT(*)
FROM {db_prefix}topics AS t
LEFT JOIN {db_prefix}log_topics AS lt ON (lt.id_topic = t.id_topic AND lt.id_member = {int:id_member})
LEFT JOIN {db_prefix}log_mark_read AS lmr ON (lmr.id_board = t.id_board AND lmr.id_member = {int:id_member})
WHERE {query_see_board}
AND t.id_last_msg > $earliest_msg
AND IFNULL(lt.id_msg, IFNULL(lmr.id_msg, 0)) < t.id_last_msg',
array(
'id_member' => $id_member,
'query_see_board' => $query_this_board,
)
);
list ($user_info['unread_topics']) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request);
//Count unread replies
$request = $smcFunc['db_query']('', '
SELECT COUNT(DISTINCT t.id_topic)
FROM ({db_prefix}topics AS t, {db_prefix}messages AS m)
LEFT JOIN {db_prefix}log_topics AS lt ON (lt.id_topic = t.id_topic AND lt.id_member = {int:id_member})
LEFT JOIN {db_prefix}log_mark_read AS lmr ON (lmr.id_board = t.id_board AND lmr.id_member = {int:id_member})
WHERE {query_see_board}
AND m.id_topic = t.id_topic
AND m.id_member = $id_member
AND IFNULL(lt.id_msg, IFNULL(lmr.id_msg, 0)) < t.id_last_msg',
array(
'id_member' => $id_member,
'query_see_board' => $query_this_board,
)
);
list ($user_info['unread_replies']) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request);
}
//Count unread replies on index MOD- End
Curious. Why is the is_guest check commented out since it wouldn't ever apply to guests (and you NEED that in there for performance reasons)?
Where exactly did you add this in Load.php?
Quote from: Arantor on September 23, 2009, 07:30:57 AM
Curious. Why is the is_guest check commented out since it wouldn't ever apply to guests (and you NEED that in there for performance reasons)?
Where exactly did you add this in Load.php?
I forgot to remove comments - i tried to see if after that I will get rid of this error :) Now it's like it should be.
Awww.... you got me. I put it just behind function loadUserSettings() instead of on very end of it :)
I am still getting some errors, but they related with mysql query. I will try to fix them and if I fail I will post here :)
EDIT:
Now I get strange error, beacuse it seems unrelated.
Unknown column 'b.member_groups' in 'where clause'It is from query, that is like 200 lines beneath modifcation.
EDIT2:
Administration logs indicate error on line 618, so IN the mod.
602: $query_this_board = 'id_board IN (' . implode(', ', $boards) . ')';
603:
604: //Count unread topics
605: $request = $smcFunc['db_query']('', '
606: SELECT COUNT(*)
607: FROM {db_prefix}topics AS t
608: LEFT JOIN {db_prefix}log_topics AS lt ON (lt.id_topic = t.id_topic AND lt.id_member = {int:id_member})
609: LEFT JOIN {db_prefix}log_mark_read AS lmr ON (lmr.id_board = t.id_board AND lmr.id_member = {int:id_member})
610: WHERE {query_see_board}
611: AND t.id_last_msg > {int:earliest_msg}
612: AND IFNULL(lt.id_msg, IFNULL(lmr.id_msg, 0)) < t.id_last_msg',
613: array(
614: 'earliest_msg' => $earliest_msg,
615: 'id_member' => $id_member,
616: 'query_see_board' => $query_this_board,
617: )
==>618: );
The reason it fails is because the {query_see_board} depends on the boards table being JOINed, so you need to rewrite the relevant WHERE clause as:
FROM {db_prefix}boards AS b
LEFT JOIN {db_prefix}topics AS t ON (b.id_board = t.id_board)
LEFT JOIN {db_prefix}log_topics AS lt ON (lt.id_topic = t.id_topic AND lt.id_member = {int:id_member})
LEFT JOIN {db_prefix}log_mark_read AS lmr ON (lmr.id_board = t.id_board AND lmr.id_member = {int:id_member})
I guess now it should work as expected. I converted also query for replies. Thanks Arantor.
Here is final code if someone needs it:
//Count unread replies on index MOD- Start
$user_info['unread_topics'] = 0;
$user_info['unread_replies'] = 0;
//Only count for members, and those who can see at least one board
if (!$user_info['is_guest'] && !empty($user_info['query_wanna_see_board']))
$request = $smcFunc['db_query']('', '
SELECT MIN(lmr.id_msg), member_groups
FROM {db_prefix}boards AS b
LEFT JOIN {db_prefix}log_mark_read AS lmr ON (lmr.id_board = b.id_board AND lmr.id_member = {int:id_member})
WHERE {query_wanna_see_board}',
array(
'id_member' => $id_member,
)
);
list ($earliest_msg) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request);
// This is needed in case of topics marked unread.
if (empty($earliest_msg))
$earliest_msg = 0;
else
{
// This query is pretty slow, but it's needed to ensure nothing crucial is ignored.
$request = $smcFunc['db_query']('', '
SELECT MIN(id_msg)
FROM {db_prefix}log_topics
WHERE id_member = {int:id_member}',
array(
'id_member' => $id_member,
)
);
list ($earliest_msg2) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request);
if ($earliest_msg2 == 0)
$earliest_msg2 = -1;
$earliest_msg = min($earliest_msg2, $earliest_msg);
}
//Select the boards to choose from... all
$request = $smcFunc['db_query']('', '
SELECT b.id_board
FROM {db_prefix}boards AS b
WHERE {query_wanna_see_board}' . (!empty($modSettings['recycle_enable']) && $modSettings['recycle_board'] > 0 ? '
AND b.id_board != ' . (int) $modSettings['recycle_board'] : ''));
$boards = array();
while ($row = $smcFunc['db_fetch_assoc']($request))
$boards[] = $row['id_board'];
$smcFunc['db_free_result']($request);
if (empty($boards))
fatal_lang_error('error_no_boards_selected');
$query_this_board = 'id_board IN (' . implode(', ', $boards) . ')';
//Count unread topics
$request = $smcFunc['db_query']('', '
SELECT COUNT(*), b.member_groups, t.*
FROM {db_prefix}boards AS b
LEFT JOIN {db_prefix}topics AS t ON (b.id_board = t.id_board)
LEFT JOIN {db_prefix}log_topics AS lt ON (lt.id_topic = t.id_topic AND lt.id_member = {int:id_member})
LEFT JOIN {db_prefix}log_mark_read AS lmr ON (lmr.id_board = t.id_board AND lmr.id_member = {int:id_member})
WHERE {query_see_board}
AND t.id_last_msg > {int:earliest_msg}
AND IFNULL(lt.id_msg, IFNULL(lmr.id_msg, 0)) < t.id_last_msg',
array(
'earliest_msg' => $earliest_msg,
'id_member' => $id_member,
'query_see_board' => $query_this_board,
)
);
list ($user_info['unread_topics']) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request);
//Count unread replies
$request = $smcFunc['db_query']('', '
SELECT COUNT(DISTINCT t.id_topic)
FROM ({db_prefix}boards AS b, {db_prefix}messages AS m)
LEFT JOIN {db_prefix}topics AS t ON (b.id_board = t.id_board)
LEFT JOIN {db_prefix}log_topics AS lt ON (lt.id_topic = t.id_topic AND lt.id_member = {int:id_member})
LEFT JOIN {db_prefix}log_mark_read AS lmr ON (lmr.id_board = t.id_board AND lmr.id_member = {int:id_member})
WHERE {query_see_board}
AND m.id_topic = t.id_topic
AND m.id_member = {int:id_member}
AND IFNULL(lt.id_msg, IFNULL(lmr.id_msg, 0)) < t.id_last_msg',
array(
'id_member' => $id_member,
'query_see_board' => $query_this_board,
)
);
list ($user_info['unread_replies']) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request);
}
//Count unread replies on index MOD- End
Thanks for posting - it might be worth crossposting this to the mod's original topic for people who are interested.
Quote from: Arantor on September 24, 2009, 10:45:19 AM
Thanks for posting - it might be worth crossposting this to the mod's original topic for people who are interested.
I did it already :)