Wrox Programmer Forums
|
BOOK: ASP.NET Website Programming Problem-Design-Solution
This is the forum to discuss the Wrox book ASP.NET Website Programming: Problem - Design - Solution, Visual Basic .NET Edition by Marco Bellinaso, Kevin Hoffman; ISBN: 9780764543869
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: ASP.NET Website Programming Problem-Design-Solution section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 28th, 2004, 08:28 AM
Registered User
 
Join Date: Jun 2004
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default Newest Topic/Post by: [MemberID]

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

 
Old August 29th, 2004, 10:14 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 917
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old August 29th, 2004, 10:19 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 917
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old August 29th, 2004, 10:25 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 917
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old September 1st, 2004, 08:52 AM
Registered User
 
Join Date: Jun 2004
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old September 3rd, 2004, 10:25 AM
Registered User
 
Join Date: Jun 2004
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!

 
Old September 3rd, 2004, 09:59 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 917
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Eric
 
Old September 5th, 2004, 02:26 AM
Registered User
 
Join Date: Jun 2004
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by englere
 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?)






Similar Threads
Thread Thread Starter Forum Replies Last Post
topic aparameshwar SQL Server 2000 6 July 11th, 2007 11:23 AM
Select Newest entry Lofa SQL Server 2000 1 February 22nd, 2006 07:56 AM
Is this topic a professional topic?? mega ASP.NET 1.0 and 1.1 Professional 0 December 3rd, 2004 09:37 PM
Returning the newest date in CR? johnyo101 Crystal Reports 0 November 18th, 2004 06:31 PM
Finding oldest to newest dates hcweb Classic ASP Basics 8 February 23rd, 2004 11:14 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.