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.

https://vborg.vbsupport.ru/external/2011/06/63.png

[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!