Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2004
    Posts
    7

    Unanswered: SQL thread/post sorting.

    I'm making a "simple" ASP forum to integrate into a webpage solution. I just recently ran into a problem with sorting threads.
    I have a page that displays all threads in a given forum (Threads.ThreadForumID=iForumID) this page will have all pinned threads on top of the list the threads should then be sorted according to when a message last was posted inside the thread (Posts.PostDate)

    Sort order:
    Threads.ThreadPinned
    Posts.PostDate or Posts.LastPostDate if it has been set.

    The tables are as follows:
    Threads table:
    ThreadID Number
    ThreadName Text
    ThreadViewCount Number
    ThreadOwnerID Number (Ref to thread owner id, user)
    ThreadLocked Boolean
    ThreadPinned Boolean
    ThreadForumID Number (Ref to ForumID, forum)

    Posts table:
    PostID Number
    PostText Text
    PostOwnerID Number (Ref to post owner, user)
    PostLastEditBy Number (Ref to last user that edited, user)
    PostThreadID Number (Ref to thread)
    PostIP Text
    PostNickname Text
    PostDate Date
    PostLastEditDate Date

    Current "sort":
    This is the current SQL statement it is not in any way correct. It's just temporary. I have tried different joins but I can't seem to get it right!

    SELECT ThreadID
    FROM Threads
    WHERE ThreadForumID=" & iForumID & "
    ORDER BY ThreadPinned DESC, ThreadID DESC

    Could someone please give me a hand here?

  2. #2
    Join Date
    Oct 2004
    Posts
    7
    Are there noone that can help me with this in this forum? Please tell me if more info is needed.

  3. #3
    Join Date
    May 2004
    Posts
    125
    Try this:


    SELECT ThreadID, ThreadPinned, Max(PostDate) as PostDate
    FROM Threads
    inner join Posts
    on ThreadID = PostThreadID
    WHERE ThreadForumID=" & iForumID & "
    group by ThreadID, ThreadPinned
    ORDER BY ThreadPinned DESC, Max(PostDate) Desc

    HTH

  4. #4
    Join Date
    Oct 2004
    Posts
    7
    Thanks, it looks much better now.

    It's only missing PostLastEditDate (say if one of the messages in the thread has been edited) But I'm not sure if this would be possible in a query.

    Example:
    Thread 1 :
    Last post : 05.10.04 12:00:00
    Last edited post : 01.10.04 11:00:00

    Thread 2 :
    Last post : 06.10.04 12:00:00
    Last edited post : 05.10.04 11:00:00

    Thread 3 :
    Last post : 04.10.04 12:00:00
    Last edited post : 07.10.04 11:00:00

    Current sort with the statment that DMWCincy wrote:
    Thread 2
    Thread 1
    Thread 3

    If one also take lastedit into consideration we sould get:
    Thread 3
    Thread 2
    Thread 1

  5. #5
    Join Date
    May 2004
    Posts
    125
    Off hand the only idea I can have would be to make a temp table then sort off of that table:

    Code:
    Create Procedure spGetThreadList
    @iForumID int
    as
    
    SELECT ThreadID, ThreadPinned, case when PostLastEditDate  is not null then PostLastEditDate  else PostDate end as PostDate into #tmpThreads
    FROM Threads
    inner join Posts
    on ThreadID = PostThreadID
    WHERE ThreadForumID= @iForumID 
    
    SELECT ThreadID, ThreadPinned, Max(PostDate) as PostDate
    FROM #tmpThreads
    inner join Posts
    on ThreadID = PostThreadID
    group by ThreadID, ThreadPinned
    ORDER BY ThreadPinned DESC, Max(PostDate) Desc
    I made it into a stored proc and you can simply call the stored proc from your code. You can replace your sqlstring to what is below or you can use the ADO command object to call the stored proc:

    sqlstring = "exec spGetThreadList " & iForumID

    If anything you will gain some performance by using stored procs instead of sql strings. If I come up with a better way of doing it without a temp table I'll post it.

    HTH
    DMW

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select ThreadID
         , ThreadPinned
         , max(coalesce(PostLastEditDate,PostDate))
    ...
    group 
        by ThreadID
         , ThreadPinned
    order 
        by ThreadPinned desc
         , max(coalesce(PostLastEditDate,PostDate)) desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2004
    Posts
    7
    With a few smal modifications due to threads without posts it's working now:

    This is how it looks now.
    CREATE Procedure spGetThreadList
    @iForumID int
    AS

    SELECT ThreadID, ThreadForumID, ThreadPinned, case when PostLastEditDate is not null then PostLastEditDate else PostDate end as PostDate1 INTO #tmpThreads
    FROM Threads
    INNER JOIN Posts
    ON ThreadID = PostThreadID
    WHERE ThreadForumID= @iForumID

    INSERT INTO #tmpThreads (ThreadID, ThreadForumID, ThreadPinned, PostDate1)
    SELECT ThreadID, ThreadForumID, ThreadPinned, ThreadCreated AS PostDate1
    FROM Threads
    WHERE Threads.ThreadForumID= @iForumID AND Threads.ThreadID NOT IN(SELECT ThreadID FROM #tmpThreads)

    SELECT ThreadID, ThreadPinned, Max(PostDate1) AS PostDate
    FROM #tmpThreads
    GROUP BY ThreadID, ThreadPinned
    ORDER BY ThreadPinned DESC, Max(PostDate1) DESC

    GO

    As you can see I removed the inner join in the last select since I can't see any reason for this to stay.

    I also added the INSERT INTO to get the threads without messages, for sorting I used ThreadCreated. As far as I can see now it works like a charm. So hopefully there sould be no more mistakes, thank you all.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    threads without messages? not in any forum i've ever seen

    however, if that's what your forum supports, then fine

    just use a LEFT OUTER JOIN and you won't need that temp table or the two inserts
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2004
    Posts
    125
    I have to agree with r937 about threads with no messages. You may want to rethink that logic if that is the case. The only way I can see that happening is

    a) on create thread you make a new thread then once they finish the message you add to the post. If that is true, I would create both the thread and post records at the same time.

    b)you have errors that generate a thread record with no post records. I would track that down and fix that issue.

    Either way, changing your query to what r937 recommended:

    SELECT ThreadID, ThreadForumID, ThreadPinned, case when PostLastEditDate is not null then PostLastEditDate else PostDate end as PostDate1 INTO #tmpThreads
    FROM Threads
    left outer JOIN Posts
    ON ThreadID = PostThreadID
    WHERE ThreadForumID= @iForumID

    Doing this will keep you from doing the second set of inserting which will give a performance boost as the forum gets bigger. I would also look into r937's idea about using coalence(thanks for pointing it out r937...learn something new everyday) and doing everything in one query instead of temp tables. You would have to run some tests since I'm not sure how efficent the function is but it has to be better then doing a select into a temp table.

  10. #10
    Join Date
    Oct 2004
    Posts
    7
    The reason why I get this is that you (moderator/administrator/superuser) can delete messages. There is nothing stopping you from deleting all messages in the thread. (I'll have to test OUTER JOIN when I get home).

    I'll most likely deny uses the ability to add a thread without adding the first message at the same time, but it's not certain that I will disable the possibility to delete all messages from a thread, (perhaps I'll delete the thread when all messages has been deleted, but I'm not sure that's correct either)

    And when it comes to coalence I haven't had time to dig into it yet, got way to late last night getting the temp table version up and running. Would be a good idea to get this part of the forum fast, since this is one of the more complex parts when it comes to sorting.

    I have to get the forum running soon, people in the martial arts club I'm creating this for are mistreating the poor guestbook in absence of the forum. (...well actually its running but there are no links from the main page yet)
    Last edited by BurZum; 11-02-04 at 08:34.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    coalesce is dead simple, it just finds the first non-null value in its list

    you shouldn't have spent all that time on the temp table, man, i keep telling you a LEFT OUTER JOIN gives you exactly what you want without a temp table

    as far as "the more complex parts when it comes to sorting" is concerned, have a look at post #6 again, that's not complex at all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Oct 2004
    Posts
    7
    It was actually post #6 I was thinking about when I wrote.

    And when it comes to coalence I haven't had time to dig into it yet, got way to late last night getting the ...
    And I hadn't seen the LEFT OUTER JOIN when I was experimenting with the extra temp table (When I say late last night it's acctually ~19.00 here on the forum) I'll do that when I get home.

    If I'm not mistaken I should have something like this:

    Code:
    SELECT ThreadID
         , ThreadPinned
         , max(coalesce(Posts.PostLastEditDate, Posts.PostDate, Threads.ThreadCreated))
    FROM Threads
    LEFT OUTER JOIN Posts
    GROUP
        BY ThreadID
         , ThreadPinned
    ORDER 
        BY ThreadPinned desc
         , max(coalesce(Posts.PostLastEditDate, Posts.PostDate, Threads.ThreadCreated)) DESC
    But since I can't change it from here I'll have to wait until later with the testing.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that looks great (nice job on the 3 columns in the COALESCE list!) except you need to specify the join conditions in an ON clause right after the JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Oct 2004
    Posts
    7
    Hehe, I see that I forgot that when I was writing the post.

    This should be closer to the actual code I'll test later.
    Code:
    SELECT ThreadID
         , ThreadPinned
         , max(coalesce(Posts.PostLastEditDate, Posts.PostDate, Threads.ThreadCreated))
    FROM Threads
    LEFT OUTER JOIN Posts
    ON ThreadID = PostThreadID
    WHERE ThreadForumID= iForumID 
    GROUP
        BY ThreadID
         , ThreadPinned
    ORDER 
        BY ThreadPinned desc
         , max(coalesce(Posts.PostLastEditDate, Posts.PostDate, Threads.ThreadCreated)) DESC

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •