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