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

[@_ck_ Participant 16 years, 5 months ago]

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.