Swapping 'lastpost' for 'dateline' in a php script throws error in tags.php

[TWood]

I've been trying to change the sort order in the tags search results. Lynne suggested a look at the ..\vb\search\searchcontroller\tag.php file, which has this at the bottom:

                {
                        $sql = "
                                SELECT contenttypeid, tagcontent.contentid, tagcontent.contentid as threadid
                                FROM ". TABLE_PREFIX . "tagcontent as tagcontent
                                WHERE tagid = " . $equals['tag'] . " 
                                ORDER BY dateline DESC
                                LIMIT 20000";
                           
                        $rst = $vbulletin->db->query_read($sql); 
                        while ($row = $vbulletin->db->fetch_row($rst))
                        {
                                $results[] = $row;
                        }
                } 
                return $results;
        }
        private $sorts = array('dateline', 'contenttypeid', 'contentid');
        private $filters = array('tagid', 'contenttypeid', 'dateline');
}

note the part:

FROM ". TABLE_PREFIX . "tagcontent as tagcontent
WHERE tagid = " . $equals['tag'] . "
ORDER BY dateline DESC
LIMIT 20000";

But if I change 'dateline' to 'lastpost' I get this error when I run a tag search, which is based on tags.php in public_html folder:

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

I'm wondering if I need to change something in the part of ..\vb\search\searchcontroller\tag.php which reads:

        private $sorts = array('dateline', 'contenttypeid', 'contentid');
        private $filters = array('tagid', 'contenttypeid', 'dateline');

The reason I need to change this is because the default sort order for tags search results is the original date of a post - dateline, whereas I need it to sort on the last post in a thread - lastpost. In the image below, the circled thread has a response that is later than the post date of earlier threads, but is lower on the list. It should be at the top of the list.

[kh99]

Maybe try replacing the sql with:

			$sql = "
				SELECT contenttypeid, tagcontent.contentid, tagcontent.contentid as threadid, thread.lastpost AS lastpost
				FROM ". TABLE_PREFIX . "tagcontent as tagcontent
				LEFT JOIN ". TABLE_PREFIX . "thread as thread ON (thread.threadid = tagcontent.contentid)
				WHERE tagid = " . $equals['tag'] . " 
				ORDER BY lastpost DESC 
				LIMIT 20000";

[TWood]

kh99 -

You. Are. Awesome!

Thank you!