[FREE] Unread topic and replies count on index [SOLVED]

[WilK]

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.

[Arantor]

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.

[WilK]

When I modified it all I get are errors

Code Select Expand

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

Code Select Expand

//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

[Arantor]

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?

[WilK]

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.

Code Select Expand

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.

Code Select Expand

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:              );

[Arantor]

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:

Code Select Expand

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})

[WilK]

I guess now it should work as expected. I converted also query for replies. Thanks Arantor.

Here is final code if someone needs it:

Code Select Expand

//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

[Arantor]

Thanks for posting - it might be worth crossposting this to the mod's original topic for people who are interested.

[WilK]

Thanks for posting - it might be worth crossposting this to the mod's original topic for people who are interested.

I did it already