Forum view permissions

Have you looked at something other than the Forum object? How about custom page/doc types? One could be "Question" another could be "Reply". A reply can only be nested within a question or another reply. You can set permissions on them a lot easier and allow users to create new ones using the Contribution webpart. Then you can display their messages based on the order in the content tree as well as who "created" them. I'm thinking you won't have a lot of code to write with this approach and can even setup workflow to send emails and such for creation, approval and denial of the pages.

Just a thought.

Brenden, that is an excellent suggestion. With that flow you could give them the appearance of being threaded as well. What are your thoughts if any on over head, since each Question and Reply would be in the Full Document structure, etc. ?

I'm sure there is overhead with that but you can set caching as needed to help. The Hierarchical Viewer would be my webpart of choice as well. Each new "topic" will have nested children ("replies") so overall I don't think it will be bad but never know until you do a POC on it.

Brenden, I really did like your suggestion, however, felt that it would be a fair bit of work and may put a greater overhead on the system. However, I will be keeping that suggestion in mind since it would be more flexible especially as that could be used with the Free license (except for the workflow to send emails).

However, for its worth I did manage to complete this using my original idea of creating a custom provider to get the posts. The trickiest part was getting the SQL query right and I will have to run some tests to see how badly that will have affected the performance in a large scale production environment (in this instance I'm not too bothered about it at the moment).

The query I came up with is:

SELECT ##TOPN## ##COLUMNS##, [CMS_User].UserName, [CMS_UserSettings].UserForumPosts, [CMS_UserSettings].UserActivityPoints,[CMS_UserSettings].UserGender, [CMS_UserSettings].UserPicture, [CMS_Badge].BadgeDisplayName, [CMS_Badge].BadgeImageURL, [CMS_Avatar].AvatarGUID
FROM [Forums_ForumPost] 
LEFT JOIN CMS_User ON [CMS_User].UserID = [Forums_ForumPost].PostUserID
LEFT JOIN CMS_UserSettings ON [CMS_UserSettings].UserSettingsUserID = [Forums_ForumPost].PostUserID
LEFT JOIN CMS_Badge ON [CMS_Badge].BadgeID = [CMS_UserSettings].UserBadgeID
LEFT JOIN CMS_Avatar ON [CMS_Avatar].AvatarID = [CMS_UserSettings].UserAvatarID
WHERE 
PostID IN (
      -- first get all the posts that are approved
      SELECT PostID From Forums_ForumPost WHERE PostForumID = @ForumID AND PostIDPath like @PostIDPath AND PostApproved = 1
  UNION -- union this to all posts by user
      SELECT PostID FROM Forums_ForumPost WHERE PostForumID = @ForumID AND PostIDPath like @PostIDPath AND PostUserID = @UserID
  UNION -- union this to all paths from the users post
      SELECT PostID FROM Forums_ForumPost as P
         WHERE EXISTS (SELECT PostId FROM Forums_ForumPost as FP WHERE P.PostIDPath like FP.PostIDPath +'%' AND FP.PostUserId = @UserID)
         AND P.PostForumID = @ForumID AND p.PostIDPath like @PostIDPath  
 ) AND (##WHERE##) ORDER BY ##ORDERBY##

I will be looking at this with a clearer head to see where it can be optimized, but at least it appears to provide the results I needed. Basically it returns all the posts from the Thread (more precisely a starting path) that are approved along with any posts that the user has made which aren't approved and the entire sub-path under their unapproved posts.

I do actually think I can get rid of the encapsulating SELECT which was taken from the default SelectPosts query.

@Suneel I'd be interested in seeing how your query performs against the suggestion I presented. I'm not sure how many queries or joins you're doing there but with using a Hierarchical Viewer and page types I'm guessing your performance will be much faster. Not to mention simply configuring page templates, types and adding content vs. writing or changing a bunch of code that will have to change when you upgrade.

From a performance standpoint, you might ask Charles about that, I want to say he's referenced their site using upwards of 600,000 pages in it without issue.

I have mocked up and tested Brenden's suggestion, actually quite easily and just had to knock out of couple transformations for the hierarchical viewer. I never really thought of doing it like that. Sometimes you need to think outside the box. The performance is fine and I don't have to write any elaborate sql. What I do like is that with document and page permissions and work flow I can completely control the questions and answers with the ability for the user via a roles/permission form control, decide on if they want others to see it and/or make it public. The performance is fine under load, I just simulated a unit test with 1,000 simultaneous users submitting.

I have a custom module for Q and A with cascading permissions that I will probably change. I guess the ONLY thing that I do not like about it, and mind you I can be a purest, is that it is in my content tree. But that is my personal opinion.

Nice POC Charles! Some things I actually work out as you did, some things I just think about; this was just a thought. Yes I'd say the Content tree issue could be an issue BUT if you place it in the second level (/Questions/question) then probably not too bad.

What would be an interesting concept would be to take pieces or modules, if you will, and create your own hierarchy utilizing the OOTB features and code. So essentially have multiple content trees and specify one for your site, one for forums, one for something else. This way your "website" could have all the crazy navigation and metadata items needed and still have some one off items like forums and such. The concept is already being used by allowing multiple sites, the unique part would be to segregate them even further.

@Brenden As I said I think the outer SELECT can be removed from the query and hence in essence we are doing 3 SELECT queries which are UNION'ed. As you rightly say I am sure this will be much faster than the using a Hierarchic page structure since for that there would need to be several additional pieces of data (although a lot of this would be cached).

Things that I do prefer are that I'm not actually doing any additional processing within the Web app and as Charles mentioned I don't have additional pages showing up in the content tree.

I still do like your way off thinking outside the box, and can see that your concept would be better in certain scenario's. I especially like the fact that with your concept we could easily create a custom workflow. I also like the fact that Permissions would be easier to set-up since this could be done with the UI (within my requirements I had to hard code a RoleName into the custom Layout and I am not sure we would be able to easily extend the Forums Permissions such that an additional item would be shown on the matrix UI).

I would like to add here, that as far as code goes, I didn't need to do much, adding a bit to Thread.ascx.cs for a check on the hard coded RoleName and to change the call to use my custom database query. I know that I have to watch this when upgrading, but I as I more or less just added retrieval code (nothing I added saves or updates data), I don't think upgrades will break this.