How to get tag search result list to order by last post date?

[TWood]

Tag search results are ordered by the date of the original post in a thread, and ignores any more recent reply dates. How can I get it to order them by the last post date of any replies that might be in a thread? In the example below, the more recent post date of the reply in the circled post should move it to the top of the list.

[Lynne]

Take a look at vb/search/searchcontroller/tag.php and you'll see the query there to edit. It currently sorts by dateline and you seem to want it by lastpost.

[TWood]

Thanks Lynne!

--------------- Added [DATE]1306365601[/DATE] at [TIME]1306365601[/TIME] ---------------

Error message:

Database error in vBulletin 4.1.3:

Invalid SQL:

                                SELECT contenttypeid, tagcontent.contentid, tagcontent.contentid as threadid
                                FROM tagcontent as tagcontent
                                WHERE tagid = 1 
                                ORDER BY lastpost DESC
                                LIMIT 20000;

MySQL Error   : Unknown column 'lastpost' in 'order clause'
Error Number  : 1054
Request Date  : Wednesday, May 25th 2011 @ 06:18:12 PM
Error Date    : Wednesday, May 25th 2011 @ 06:18:12 PM
Script        : http://shoutstreet.com/tags.php?tag=78759
Referrer      : http://shoutstreet.com/
IP Address    : 66.68.130.22
Username      : tomwood
Classname     : vB_Database
MySQL Version : 5.0.92-community-log

[B-runner]

Mega old thread bump.

What is suggested isn't exactly possible without more work, hence the error:

MySQL Error : Unknown column 'lastpost' in 'order clause'

The columns available in tagcontent are: tagid, contenttypeid, contentid, userid, dateline. There is no lastpost.

The dateline is the date the tag was added.
The userid is the user who added the tag (this could help track down a rouge tagger )
The contentid is an ID # based on when the content was created. This can allow you to sort based on when the thread was created, and not when the tag was applied. This is at least better for me since I can back-apply tags to old thread and it's not good to have those show up before new threads.

It looks like the contenttypeid determines if it is a forum post, blog or article.

For me, it looks like it puts posts before blog entries in results this list.


Note, as there is no "lastpost" in the tagcontent, and it won't make sense either since it only gets updated when a new tag is added. To get a lastpost one would have to take the contentid and then go to another table and query to get that based on the contentid, which would take hacking up the php a little more. I'm good with what I have for now.


(Adding info since this place is searched often and provides great info. Also, I don't get to contribute much elsewhere. )

[grey_goose]

Pretty crazy this is so buried. Had to dig it up since we've been using tags more and more. The default behavior of sorting by when the tag was added rather than by thread/post dates is... a really strange decision.

Change line 95 to "ORDER BY threadid ASC" to get threads from oldest to newest.
Change line 95 to "ORDER BY threadid DESC" to get threads from newest to oldest.

Ordering results by last post date would require JOINing the thread table but that looks like it would require some extra evaluation of 'contenttype' -- I'm guessing for people with CMS/Blog, which I don't have.

Still, ordering by threadid (thread started) will put them in some semblance of order for most rather than the pure WTF-ery of sorting by tag-added date.