I work with bbPress 0.9.
Been trying for hours to get this SQL into a view?
How should the array for bb_register_view() look like?
SELECT bbt.*, (SELECT COUNT(*) FROM bb_ratings WHERE topic_id=bbt.topic_id) AS counts
FROM bb_topics bbt, bb_ratings bbr
WHERE bbt.forum_id = 2 AND bbt.topic_status = 0
GROUP BY bbt.topic_id
ORDER BY counts DESC
I've never figured out how to do direct query setup for views, so what I do is just short-circuit the internal function and write my own. For example:
bb_register_view("highest-rated","Topics with the highest rating",array('append_meta'=>false,'sticky'=>false));
add_action( 'bb_custom_view', 'highest_rated' );
function highest_rated( $view ) {
if ($view=='highest-rated') {
global $bbdb, $topics, $view_count, $page;
$limit = bb_get_option('page_topics');
$offset = ($page-1)*$limit;
$where = apply_filters('get_latest_topics_where',"WHERE topic_status=0 ");
$query = " FROM $bbdb->topics $where ";
$restrict = " ORDER BY cast(topic_posts as UNSIGNED) DESC LIMIT $limit OFFSET $offset";
$view_count = $bbdb->get_var("SELECT count(*) ".$query);
$topics = $bbdb->get_results("SELECT * ".$query.$restrict);
$topics = bb_append_meta( $topics, 'topic' );
}
}.
Obviously change the query to your own and that might work for you. Don't forget to use $bbdb->bb_ratings the $bbdb part is critical.
You must log in to post.