Moderator only notes & Number of queries

[Delphy]

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:

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


Replace with:

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


Find:

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


Replace with:

$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:

$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:

$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:

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


Replace with:

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


In the same file, find:

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


BELOW this, add:

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

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:

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

global $warn_notes;


Just below, find:

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


Replace with:

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:

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:

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

[beansbaxter]

I'll have to try these...I am having the same problem you stated in #1. THanks for the feedback, it is appreciated!

[sv1cec]

I'll check your changes for (2), but for issue (1), are you sure you have made the changes suggested in functions_showthread.php?

If you have, then the parameter $pnotes should have a value only if $showviewwarnlink is set to 1. The $showviewwarnlink parameter is set to one only for admins or for admins/smods/mods depending on the usage of the hierarchical schema or not.

It should not be necessary to use the code below:


<if condition="$bbuserinfo[usergroupid] == 7 || $bbuserinfo[usergroupid] == 6 || $bbuserinfo[usergroupid] == 5">

which is actually wrong, since in the latest versions of AWS, the Admins/Smods/Mods groups are defined in the program, so you can have custom admin groups or smod or mod groups.

Check your edits in functions_showthread.php, that's where the check is made. Also check that you have defined your Warning Options about which are your admin, mod, smod groups.

[Delphy]

Hiya,

I made all the changes to the showthread.php, but left the admin/smod/mod fields empty in the settings page since I use the defaults. From my understanding of what you said, this would suggest if I specifically set the fields, then it should work?

I'll go through and double check everything again just to make sure.

Thanks,
Delphy

[sv1cec]

Yes, if you leave them empty the program will enter the proper groups (6,5,7) for the admins/smods/mods. From there on, the code in functions_showthread.php decides if the $pnotes will be filled up, depending on the $showviewwarnlink parameter.

If your plain users can see the notes, they should also be able to see the "View XYZ Warnings".

DARN!!! Now, I know, you have the "Who can view warning points" set to "All", right?

OK, try this. In functions_showthread.php find:


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


Replace that with:


if ($post['warn_notes']==1 and ($useradmin==1 OR $usersmod==1 OR $usermod==1));


That should fix it. Let me know if it works.

[Delphy]

sv1,

I had "Who can view warning points?" set to Mgmt, but when the moderators where entering notes they set "private note?" to No - becuase the wording seems to indicate the only the moderator who entered the note in the first place could see the note (and not all moderators which is what I wanted). Maybe I'm just trying to use it in a way it's not supposed to be used :D

Regards
Delphy

[sv1cec]

Notes can be either public (for all the management team to see) or private (only the person who entered it can see it). If you had set the Who can view Warning Points" to Mgm, there is no reason why the rest of your members could see the notes.

Try the change suggested above and let me know if it works OK.

[Delphy]

Hiya,

Nope - it didn't. Members and guests can still see the notes entered.

Regards,
Delphy

[sv1cec]

I am not sure if this is due to your modifications, or not. Look at the code below:


if ($post['warn_notes']==1 and ($useradmin==1 OR $usersmod==1 OR $usermod==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");
$pnotes='';
$d='';
$t='';
while($notes=$DB_site->fetch_array($get_notes))


That says:

If the post has a note, and the user is administrator, super moderator or moderator, then do whatever follows.

If the post has no notes or if the user is nothing of the above, then obviously the parameter $pnotes never gets a value, so it should not show. I do not know if your changes have affected this procedure or not.

[Delphy]

With regards to the number of SQL queries changes I made, they dont affect that line - I was simply referencing it in my changelog becuase I needed to alter the lines below it.

I just saw you released the latest version so I'll try that out and get back to you.

Thanks for the replies. Have you managed to have a look at the single query code yet?

Regards,
Delphy

[sv1cec]

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]

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]

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