How would I have to modify the v_Forums_Topics view, to also include the MemberID of the last post in a topic?

I thought I could just add it in 'TopicLastPostDate',

(SELECT MAX(AddedDate) FROM (SELECT TopicID ; AddedDate FROM Forums_Topics UNION ALL SELECT TopicID ; AddedDate FROM Forums_Replies) AS dates WHERE dates.TopicID = Forums_Topics.TopicID)

but that didn't work - Or shouldn't I do that in there? I just want to display the memberID of the newest Topic in Forum / newest Post in Topic

You've got a real interesting problem here. The function that finds the "Max" date is an aggregate function, and that "select" can't return the MemberID of the record with the highest date.

We want a view of the 2 message tables (Forums_Topics and Forums_Replies) that gives us the info on the newest post, regardless of which table it's in. This could be done with a view, but it would get ugly fast. It almost seems like we want a stored procedure because we want to do some more processing than you can normally do with a single SQL "select" statement (as I said, it could be done in a "select, but it would be nasty).

Since ThePhile doesn't have any User Defined Functions (UDF), I thought this would be a good chance to create one. We can have it return a table with the info about the newest post for each topic. We can then use this table in the "v_Forums_Topics" View.

/* UDF NewestMessage () */
/* This returns a table of topics along with the newest date of a
   posting, and related info. The Type is 'T' if the Topic entry
   is the newest (it has no replies yet), and 'R' if a
   reply is the newest entry for that topic. The RecordID is
   either the TopicID or the ReplyID, depending on the type. */
print 'NewestMessage ()'
go
create function dbo.NewestMessage ()
returns @NewestMessageTab Table
(TopicID int, Subject Varchar(100), NewestDate datetime,
 Type varchar(1), RecordID int, MemberID int)
as
begin
  /* first we'll insert the easy stuff, then we'll go back and
     fill in the rest */
  insert into @NewestMessageTab (TopicID, Subject, NewestDate)
  select ft1.TopicID, ft1.Subject,
  ( select max(Dates.AddedDate)
    from
    ( SELECT AddedDate, TopicID, MemberID
      FROM Forums_Topics
      union all
      SELECT AddedDate, TopicID, MemberID
      FROM Forums_Replies ) as Dates
    where Dates.TopicID = ft1.TopicID
  ) as NewestDate
  from Forums_Topics ft1;


  /* fill-in the Topic info */
  update umt
    set umt.Type = 'T',
        umt.RecordID = ft.TopicID,
        umt.MemberID = ft.MemberID
  from @NewestMessageTab umt,
       Forums_Topics ft
  where umt.TopicID = ft.TopicID
    and umt.NewestDate = (select max (ft2.AddedDate)
                          from Forums_Topics ft2
                          where ft2.TopicId = umt.TopicID);

  /* fill-in the Reply info */
  update umt
    set umt.Type = 'R',
        umt.RecordID = fr.ReplyID,
        umt.MemberID = fr.MemberID
  from @NewestMessageTab umt,
       Forums_Replies fr
  where umt.TopicID = fr.TopicID
    and umt.NewestDate = (select max (fr2.AddedDate)
                          from Forums_Replies fr2
                          where fr2.TopicId = umt.TopicID);

  return
end

go

See my next message for more info on this subject.

Eric

Now it's time to test the new UDF to see what it returns. This is not part of a View or Stored Proc - this is just a SQL statement to see what the UDF returns.

select ft.TopicID, ft.Subject, nm.NewestDate, nm.Type, nm.MemberID, fm.MemberName
from Forums_Topics ft,
     NewestMessage() nm,
     Forums_Members fm
where ft.TopicID = nm.TopicID
  and fm.MemberID = nm.MemberID

That seems to work - it shows all the topics, and the related info about the newest posting for each topic.

The Type tells us if the newest posting is from the Topic table (no relies were given yet), or from the Reply table.

My next message will show how to plug this into the view you asked about.

Eric

Here's the new definition for the View. If the view already exists (it probably does), then change the CREATE below to an ALTER.

CREATE VIEW v_Forums_Topics
AS
SELECT v_Forums_Forums.CategoryID, v_Forums_Forums.CategoryName,
       v_Forums_Forums.CategoryPosition, v_Forums_Forums.ForumID,
       v_Forums_Forums.ForumName, v_Forums_Forums.ForumDescription,
       v_Forums_Forums.ForumPosition, v_Forums_Forums.ForumTopics,
       v_Forums_Forums.ForumPosts, Forums_Topics.TopicID,
       Forums_Topics.TopicKey, Forums_Topics.Subject,
       Forums_Topics.Message, Forums_Topics.AddedDate,
       ( SELECT COUNT(*)
         FROM Forums_Replies
         WHERE Forums_Replies.TopicID = Forums_Topics.TopicID ) AS TopicReplies,
       ( SELECT MAX(AddedDate)
         FROM Forums_Replies
         WHERE Forums_Replies.TopicID = Forums_Topics.TopicID ) AS TopicLastReplyDate,

       nm.NewestDate as TopicLastPostDate,
       ( select fm.MemberName as LastPostMemberName
         from Forums_Members fm
         where fm.MemberID = nm.MemberID ) as LastPostMemberName,

       Forums_Topics.MemberID, v_Forums_Members.UserID,
       v_Forums_Members.MemberName, v_Forums_Members.ShowEmail,
       Forums_Topics.MemberIP, v_Forums_Members.Signature,
       v_Forums_Members.AvatarUrl, v_Forums_Members.Homepage,
       v_Forums_Members.Email, v_Forums_Forums.ForumLastPostDate
FROM v_Forums_Forums
       INNER JOIN Forums_Topics
          ON v_Forums_Forums.ForumID = Forums_Topics.ForumID
       INNER JOIN v_Forums_Members
          ON Forums_Topics.MemberID = v_Forums_Members.MemberID
       INNER JOIN NewestMessage() as nm
          ON nm.TopicID = Forums_Topics.TopicID
go

WARNING: it's getting late and I didn't test this much. You should do your own testing before using this UDF and View.

Eric

englere, thank you very much! Looks really good. I will test it now and get back to you later.

I'm sorry it was my mistake, but I think the v_Forums_Topics view is the wrong one, we actually need the v_Forums_Forums view, to display new posts in Forums on the Forum start page (default.aspx)

This is done in Business.Category(categoryId).GetForums()

I tried to insert your code for the UDF into the view but then of course I got an error, because TopicID isn't present in either Forums_Categories nor Forums_Forums

Then I tried to edit your UDF but didn't succeed :o
Or should we just add some more tables to the v_Forums_Forums view?
It's gotten me a bit confused, hope this is making sense...
would be great if you could have another look at it!

Give it a try and see how far you get. You only learn by doing!

Eric

I did LOL

No seriously, could you at least give me a hint on what the best way to go would be? (Add Tables or modify UDF? Or both?)