Forums / Cotonti / Support / Announcments at the top

Trustmaster
#14620 2009-07-20 21:25

Administrators
Thanked: 263 times

Actually the way sticky/lock/annoucnement is designed, you can't do it with a simple query because if you use:
$sql = sed_sql_query("SELECT t.*, p.poll_id FROM $db_forum_topics AS t LEFT JOIN
$db_polls AS p ON t.ft_id=p.poll_code  WHERE t.ft_sectionid='$s' $cond AND (p.poll_type='forum' OR p.poll_id IS NULL)
ORDER by ft_sticky DESC, ft_lock DESC, ft_".$o." ".$w."
LIMIT $d, ".$cfg['maxtopicsperpage']);
It will show announcements on top, then locked topics and after that normal topics. The only obvious solution for me, without changing the database, would be a spaghetti UNION query like:
$sql = sed_sql_query("(SELECT t.*, p.poll_id FROM $db_forum_topics AS t LEFT JOIN
$db_polls AS p ON t.ft_id=p.poll_code  WHERE t.ft_sectionid='$s' $cond AND (p.poll_type='forum' OR p.poll_id IS NULL)
AND t.ft_sticky = 1 AND t.ft_state = 1
ORDER by ft_".$o." ".$w.")
UNION
(SELECT t.*, p.poll_id FROM $db_forum_topics AS t LEFT JOIN
$db_polls AS p ON t.ft_id=p.poll_code  WHERE t.ft_sectionid='$s' $cond AND (p.poll_type='forum' OR p.poll_id IS NULL)
AND t.ft_sticky = 1 AND t.ft_state = 0
ORDER by ft_".$o." ".$w.")
UNION
(SELECT t.*, p.poll_id FROM $db_forum_topics AS t LEFT JOIN
$db_polls AS p ON t.ft_id=p.poll_code  WHERE t.ft_sectionid='$s' $cond AND (p.poll_type='forum' OR p.poll_id IS NULL)
AND t.ft_sticky = 0 AND t.ft_state = 0
ORDER by ft_".$o." ".$w.")
UNION
(SELECT t.*, p.poll_id FROM $db_forum_topics AS t LEFT JOIN
$db_polls AS p ON t.ft_id=p.poll_code  WHERE t.ft_sectionid='$s' $cond AND (p.poll_type='forum' OR p.poll_id IS NULL)
AND t.ft_sticky = 0 AND t.ft_state = 1
ORDER by ft_".$o." ".$w.")
LIMIT $d, ".$cfg['maxtopicsperpage']);
(it is system/core/forums/forums.topics.inc.php at line 311).

The last one will work for you, but speed is the price.

Added 3 minutes later:

And you could do it without a corehack, with a plugin:
<?php
/* ====================
[BEGIN_SED_EXTPLUGIN]
Code=ft_order
Part=topics
File=ft_order.topics
Hooks=forums.topics.main
Tags=
Order=10
[END_SED_EXTPLUGIN]
==================== */
if (!defined('SED_CODE')) { die("Wrong URL."); }

$sql = sed_sql_query("(SELECT t.*, p.poll_id FROM $db_forum_topics AS t LEFT JOIN
$db_polls AS p ON t.ft_id=p.poll_code  WHERE t.ft_sectionid='$s' $cond AND (p.poll_type='forum' OR p.poll_id IS NULL)
AND t.ft_sticky = 1 AND t.ft_state = 1
ORDER by ft_".$o." ".$w.")
UNION
(SELECT t.*, p.poll_id FROM $db_forum_topics AS t LEFT JOIN
$db_polls AS p ON t.ft_id=p.poll_code  WHERE t.ft_sectionid='$s' $cond AND (p.poll_type='forum' OR p.poll_id IS NULL)
AND t.ft_sticky = 1 AND t.ft_state = 0
ORDER by ft_".$o." ".$w.")
UNION
(SELECT t.*, p.poll_id FROM $db_forum_topics AS t LEFT JOIN
$db_polls AS p ON t.ft_id=p.poll_code  WHERE t.ft_sectionid='$s' $cond AND (p.poll_type='forum' OR p.poll_id IS NULL)
AND t.ft_sticky = 0 AND t.ft_state = 0
ORDER by ft_".$o." ".$w.")
UNION
(SELECT t.*, p.poll_id FROM $db_forum_topics AS t LEFT JOIN
$db_polls AS p ON t.ft_id=p.poll_code  WHERE t.ft_sectionid='$s' $cond AND (p.poll_type='forum' OR p.poll_id IS NULL)
AND t.ft_sticky = 0 AND t.ft_state = 1
ORDER by ft_".$o." ".$w.")
LIMIT $d, ".$cfg['maxtopicsperpage']);
?>
Call it ft_order.topics.php and supply the ft_order plugin with some simple ft_order.setup.php file.

Added 5 minutes later:

And here is your ft_order plugin. I hope it is a good lesson to everyone here. Have a nice day.
May the Source be with you!
This post was edited by Trustmaster (2009-07-20 21:36, 14 years ago)