Support Forum

Morning BOZ

OK - here is the full story.

The Target:

Links to posts in moderation (from here on referred to as PIM) in such locations as Group View(i.e., front forum page)/Last Post or Forum View(i.e., Topic Listing)/First and Last Post columns, should only ever be shown to admins/moderators and to the user who made the post. This was the target.

This was achieved in Group View with very small overhead.

In Topic View (i.e., the posts themselves) there is no issue. If the current user can access the topic view then a PIM is shown. If they do not qualify to see the content then it simply says 'awaiting moderation'.

The problem is with the Forum View. If you were a visitor to this site over the last couple of of weeks you will have noticed it kept dropping out. This was partly due to the main Forum View query.

Our Queries:

It has been a major goal of V5 to dramatically reduce the number of select queries being run and in that aim we like to think we have been somewhat successful - reducing some views by as much as 75% or more. So we wanted to try and achieve this target development with as little impact as possible on number of queries needed to satisfy the requirements.

The Problem:

The problem lies with selecting the post to satisfy the last post column of forum view. And, if a new topic with a single PIM, then the first post column as well. Under normal circumstances we can find the first post in a topic by the post_index column (in the sfposts table) where it equals 1. This column, you will realise, is a denormalised and 'manually' maintained column simply there to allow us to make shortcuts. The last post column can be satisfied by the post_id held in the topic record itself which always records the last post id made in that topic.

But we also need to test the post records in question to see if that post is a PIM - which is held in the column post_status of the sfpost record. If it equals >0 then it is a PIM. (1 is a standard PIM, 2 is a PIM also been marked as spam by AKismet).

Then it gets complicated! If a PIM then we also need to test it against the current user viewing the forum view page. If they are a forum admin or moderator then there is nothing to do. We use the PIM. If the current user is a member and their user_id matches the user who made the post - then we use the PIM record. If the current user is a guest and their guest_email matches the email address of the guest who made the post then again - we use the PIM record.

But if all of those tests fail then we need to select an earlier post in the topic and basically run the tests again. If the topic has just the one post then it will be excluded from the forum view topic listing.

I hope that all makes some sort of sense!

The Result:

On a system with a large number of topics - like ours here - then chaos. Basically it chewed up SQL resources and ground the whole site to a halt. Run locally on my test systems using something like MySQL Workbench it appeared to be OK but get it on a live site and....

SQL Solutions:

It could be done by breaking the main query down into more discreet queries but I am trying to avoid that where possible - or at least to minimise this approach. Maybe a SQL guru could solve the problem and I welcome anyone willing to take a look at it.

Before reading the next bit - assuming you have got this far! - remember that simply either showing PIMs to everyone or not showing them at all are far simpler SQL solutions.

Operational Solutions:

There is another approach to this whole situation that we are considering and this involves bringing forward a development we were planning for the future. This entails a simple user notification system. In this scenario a PIM is never shown to a member or to a guest. Nice and simple. The problem, of course, is the member who made the post can not now find it while in PIM status.

Enter the notification system. Basically we would instead display a simple notice somewhere on the screen to say that their post was still a PIM and also give a link to the topic.

This approach has several advantages:

1. It removes the stress from the underlying Group and Forum view queries altogether.
2. It actually makes it easier for the end user to determine their post is still a PIM without them having to hunt for it.
3. It allows us to put in place a sub-system we have been wanting to do (just not originally planned for V5.0) that can be used for other notifications such as deleted posts and moved posts/topics.
4. It provides an api for plugins that could also make use of the sub-system for other notifications.

In some respects this could be seen as ducking the issue but the advantages are worth consideration.

Finally:

If you want to take a look at the query in question then you will see it in the /forum/content/sp-forum-view-class.php file. The commented out code is the offending query. Please do take a look, ask questions etc., as I would love to solve if of course. I can give you a quick, easy and 'dirty' method to run the query and get a copy of the actual sql being used. Just remember that if run when logged in as an admin then the query is simple. It is as a member or guest that it gets complex. And yes - the main problem is that sub-query!

Helping Out or Joining the Team:

I can't stress enough how much we would welcome extra team members. If you are seriously interested send us a PM and we can discuss it off line.

And I hope the above makes sense!

Andy

Let's take this off-line as I would like to discuss this in more depth if that is OK with you... I will email you