The Arcive of Official vBulletin Modifications Site.

Moderator only notes & Number of queries · **Released**: 08-22-2005

Hiya,

I installed the latest version a couple of days ago after having used the older one a while back (but lost it in an upgrade). Two things I've noticed:

1. All members can see the warning notes in the default install
2. 1 query is performed per post to get the warning notes

I have 3.0.7 so the fixes below work ONLY on that version. The template fix could work on others, and the logic behind the query too.

To fix #1, do the following:

Edit the postbit / postbit_legacy template.

Find:

Code:

<if condition="$pnotes!='' AND $vboptions['warn_allownotes']==1">

Replace with:

Code:

<if condition="$bbuserinfo[usergroupid] == 7 || $bbuserinfo[usergroupid] == 6 || $bbuserinfo[usergroupid] == 5">
<if condition="$pnotes!='' AND $vboptions['warn_allownotes']==1">

Find:

Code:

$pnotes
</table>
<br>
</if>

Replace with:

Code:

$pnotes
</table>
<br>
</if>		
</if>

To fix #2 requires editing of 2 files, but caches all warning notes in 1 query instead of 1 query per post.

First, edit showthread.php

Find:

Code:

                $getpostids = $DB_site->query("
                        SELECT postid NOT ISNULL(deletionlog.primaryid) AS isdeleted
                        FROM " . TABLE_PREFIX . "post AS post
                        LEFT JOIN " . TABLE_PREFIX . "deletionlog AS deletionlog ON(post.postid = deletionlog.primaryid AND type = 'post')
                        WHERE threadid = $threadid AND visible = 1
                        ORDER BY postid $postorder
                ");

Replace with:

Code:

                $getpostids = $DB_site->query("
                        SELECT postid, post.userid, NOT ISNULL(deletionlog.primaryid) AS isdeleted
                        FROM " . TABLE_PREFIX . "post AS post
                        LEFT JOIN " . TABLE_PREFIX . "deletionlog AS deletionlog ON(post.postid = deletionlog.primaryid AND type = 'post')
                        WHERE threadid = $threadid AND visible = 1
                        ORDER BY postid $postorder
                ");

Just below this, find:

Code:

                        $ids .= ',' . $post['postid'];
                        $lastpostid = $post['postid'];

Replace with:

Code:

                        $ids .= ',' . $post['postid'];
                        $userids .= ',' . $post['userid'];
                        $lastpostid = $post['postid'];

In the same file, find:

Code:

        $postids = "post.postid IN (0" . $ids . ")";

BELOW this, add:

Code:

        // Start Cached Warning Notes
        global $warn_notes;
        $warn_query = $DB_site->query("SELECT w.*, u.username as warner from  " . TABLE_PREFIX. "warn_notes w LEFT JOIN " . TABLE_PREFIX . "user u on (u.userid=w.warned_by) WHERE warned_user IN (0". $userids. ") ORDER BY warned_time DESC");
        while ($warn_note = $DB_site->fetch_array($warn_query))
        {
                $warn_notes[$warn_note[warned_user]] = $warn_note;
        }
        $DB_site->free_result($warn_query);
        // End Cached Warning Notes

Save the file, and upload.

Next, edit includes/functions_showthread.php

Find:

Code:

                                if ($post['warn_notes']==1 and $showviewwarnlink==1);
                {


                        $get_notes=$DB_site->query("select w.*, u.username as warner from ".TABLE_PREFIX."warn_notes w
                        LEFT JOIN ".TABLE_PREFIX."user u on(u.userid=w.warned_by)
                        where warned_user='{$post['userid']}' order by warned_time desc");

Replace with:

Code:

                                if ($post['warn_notes']==1 and $showviewwarnlink==1);
                {

                        global $warn_notes;

Just below, find:

Code:

                        while($notes=$DB_site->fetch_array($get_notes))
                        {
                                $d=vbdate($vboptions['dateformat'], $notes['warned_time']);
                                $t=vbdate($vboptions['timeformat'], $notes['warned_time']);

Replace with:

Code:

                        foreach ($warn_notes as $notes)
                        {
                                if ($notes[warned_user] == $post[userid]) {
                                $d=vbdate($vboptions['dateformat'], $notes['warned_time']);
                                $t=vbdate($vboptions['timeformat'], $notes['warned_time']);

Find:

Code:

                                        elseif ($notes['warned_type']==0)
                                        {
                                                $pnotes = $pnotes . "<tr><td width='15%' class='smallfont'>$notes[warner]</td><td width='15%' class='smallfont'>$d, $t</td><td class='smallfont'>$notes[warned_note]</td><td class='smallfont' align='center'>$r</td></tr>";
                                        }
                                }

Replace with:

Code:

                                        elseif ($notes['warned_type']==0)
                                        {
                                                $pnotes = $pnotes . "<tr><td width='15%' class='smallfont'>$notes[warner]</td><td width='15%' class='smallfont'>$d, $t</td><td class='smallfont'>$notes[warned_note]</td><td class='smallfont' align='center'>$r</td></tr>";
                                        }
                                }
                                }

Save and upload.

This cuts the number of queries from 1 per post to 1 on the entire page.

Hope this helps.

Regards,
Delphy

sv1cec · #12 08-26-2005, 10:41 AM

The new release contains the code I suggested above. That should prevent simple users from seeing the notes. If that does not work for you, I do not know what to suggest, since I haven't checked your code yet.

I have one question about the way you modified the code for reducing the number of queries for the notes. How do you handle a post-specific note? I went through your replacements, but I am not sure I noticed how a post-specific note is handled. Admitedly, I haven't done the changes in the files, but from looking in your code, I didn't see that.

Delphy · #13 09-07-2005, 11:03 AM

Post specific notes are handled by your original code - all I'm doing is, instead of doing a query per note, just cache everything first in an array with 1 query, and then do a foreach through the loop to get post specific notes. It's all still 1 query for an entire thread as opposed to 1 per post.

Regards,
Delphy

sv1cec · #14 09-07-2005, 12:04 PM

I did your changes, as suggested, and for some reason, post-specific notes do not appear.

I added a Private, non-post-specific note and it appeared OK.
Then I added a Private, post-specific one too, and only the first appears.

I am sure it is something trivial, and your idea to reduce the queries is a great one, but I do not have the time to look further into it. If you can solve the problem, let me know.

Rgds

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.04536 seconds Memory Usage 2,246KB Queries Executed 18 (?)
More Information
Template Usage: (1)SHOWTHREAD (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)ad_showthread_beforeqr (16)bbcode_code (1)footer (1)forumjump (1)forumrules (1)gobutton (1)header (1)headinclude (1)modsystem_post (1)navbar (6)navbar_link (120)option (1)pagenav (1)pagenav_curpage (1)pagenav_pagelink (4)post_thanks_box (4)post_thanks_button (1)post_thanks_javascript (1)post_thanks_navbar_search (4)post_thanks_postbit_info (3)postbit (4)postbit_onlinestatus (4)postbit_wrapper (1)spacer_close (1)spacer_open (1)tagbit_wrapper Phrase Groups Available: global inlinemod postbit posting reputationlevel showthread	Included Files: ./showthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/functions_bigthree.php ./includes/class_postbit.php ./includes/class_bbcode.php ./includes/functions_reputation.php ./includes/functions_post_thanks.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete showthread_start showthread_getinfo forumjump showthread_post_start showthread_query_postids showthread_query bbcode_fetch_tags bbcode_create showthread_postbit_create postbit_factory postbit_display_start post_thanks_function_post_thanks_off_start post_thanks_function_post_thanks_off_end post_thanks_function_fetch_thanks_start post_thanks_function_fetch_thanks_end post_thanks_function_thanked_already_start post_thanks_function_thanked_already_end fetch_musername postbit_imicons bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete postbit_display_complete post_thanks_function_can_thank_this_post_start pagenav_page pagenav_complete tag_fetchbit_complete forumrules navbits navbits_complete showthread_complete
Messages:

The Arcive of Official vBulletin Modifications Site.

It is not a VB3 engine, just a parsed copy!