How do I use bb_query to fetch all topics that were started within a given date range?

Here’s an example found an example of a bb_query code on the bbPress blog:

$topic_query = new BB_Query( ‘topic’,

array(

‘topic_author’ => ‘mdawaffe’,

‘started’ => ‘2007-06’,

‘tag’ => ‘bbpress’,

‘order_by’ => ‘topic_start_time’

)

);

$topic_query->results; // Here’s the array of topics the query returned.

This shows how to get topics that where started on June 2007. But what if I wanted to get all topics that were started between June 2, 2007 10:30:49 PM and February 12, 2008 9:02:32 am?

I think I may be missing something totally obvious. Any suggestions? I

[@mrhoratio Member 16 years, 8 months ago]

I did some more digging, and it looks a way to do is to not use BB_query at all, but to use $bbdb->get_results instead:

$startdate=”20070602103049″;

$enddate=”20080212090232″;

$topics = $bbdb->get_results(“SELECT * FROM $bbdb->topics WHERE topic_start_time BETWEEN $startdate AND $enddate ORDER BY topic_start_time DESC”);

[@sambauers Participant 16 years, 7 months ago]

BB_Query can’t do this at the moment as far as I can tell. It probably should though.

I’ll make a trac ticket to suggest this.

At the moment you could do two BB_Queries and intersect their results. But that’s pretty poor.

Just be sure if you do use a direct query that you prepare it first. e.g.:

$startdate="20070602103049";

$enddate="20080212090232";



$query = $bbdb->prepare("SELECT * FROM %s WHERE topic_start_time BETWEEN %s AND %s ORDER BY topic_start_time DESC", $bbdb->topics, $startdate, $enddate);

$topics = $bbdb->get_results($query);

[@sambauers Participant 16 years, 7 months ago]

https://trac.bbpress.org/ticket/879

[@mrhoratio Member 16 years, 7 months ago]

Hey Sam, thanks for making a ticket for this. I was able to query for topics within a date from directly from WordPress as well. Here’s something I wrote to merge “super sticky” bbPress topics into the WordPress loop. It’s a bit of hack, but it brings the “Promote to Front Page” functionality of Drupal into WordPress/bbPress:

<?php get_header(); ?>



<?php



//Put WordPress posts into an array

$wp_posts = $posts;



if (!is_single()){



//Retrieve first post of previous page (we need this post's date to query bbPress topics

$offset=$paged*$posts_per_page+$posts_per_page;

$first_post_of_previous_page = get_posts('numberposts=1&offset='.$offset);



//First set the start and end dates to limit the query to the bbPress table

$startdate=(date('YmdHis',strtotime($first_post_of_previous_page[0]->post_date_gmt)));



//If it's the most recent page, set end date to today

if ($paged){

$enddate=(date('YmdHis',strtotime($wp_posts[0]->post_date_gmt)));

} else {

$enddate=gmdate('YmdHis');

}



//Retrieve "Super Sticky" topics from bbPress tables

//This assumes your bbPress and WordPress tables are in the same database

$bb_topics = $wpdb->get_results("SELECT * FROM bb_topics WHERE topic_sticky = 2 AND topic_start_time BETWEEN $startdate AND $enddate ORDER BY topic_start_time DESC");



//Map bbPress topics to WordPress posts structure

foreach($bb_topics as $bb_topic){

$bb_first_post = $wpdb->get_results("SELECT post_text FROM bb_posts WHERE post_position = '1' AND topic_id = $bb_topic->topic_id", ARRAY_A);

$bb_post->ID = "forum_topic_".$bb_topic->topic_id;

$bb_post->post_author = $bb_topic->topic_poster;

$bb_post->post_date = $bb_topic->topic_start_time;

$bb_post->post_content = $bb_first_post[0][post_text];

$bb_post->post_title = $bb_topic->topic_title;

$bb_post->post_status = "publish";

$bb_post->comment_status = "open";

$bb_post->ping_status = $bb_topic->post_id;

$bb_post->post_name = "forums/topic/".$bb_topic->topic_slug;

$bb_post->post_type = "post";

$bb_post->comment_count = $bb_topic->topic_posts-1;



//add bbPress topic to WordPress posts array

$wp_posts[] = $bb_post;



};



//Create function to sort array of posts by date

function compare($x, $y){

if ( $x->post_date == $y->post_date )

return 0;

else if ( $x->post_date < $y->post_date )

return 1;

else

return -1;

}



//Sort array

usort($wp_posts,'compare');

}



?>



<div id="content-box" class="span-8">



<div id="content-area" class="clearfix">



<?php if ($wp_posts): ?>

<?php foreach ($wp_posts as $post): ?>

<?php setup_postdata($post); ?>



<div class="entry">



<div class="entry-header clearfix">



<div class="info span-2">

<a href="<?php the_permalink(); ?>#comments" class="comment-activity"><?php comments_number('<strong>Post comment</strong>', '<strong>1</strong> Comment', '<strong>%</strong> Comments' );?></a>

</div><!-- end info -->



<div class="content span-6 last">

<h2><a href="<?php the_permalink(); ?>" rel="bookmark" title="Permanent Link to <?php the_title(); ?>"><?php the_title(); ?></a></h2>

</div><!-- end content -->



</div><!-- end entry-header -->



<div class="entry-content clearfix">



<div class="info span-2 clearfix">

<p><strong><?php the_author() ?></strong><br />

<?php the_time('M j, Y'); ?>

</p>

<p class="share-this"><?php akst_share_link(); ?></p>



<p class="post-comment"><a href="<?php the_permalink(); ?>#respond">Comment</a></p>



</div><!-- end info -->



<div class="content span-6 last clearfix">



<?php the_content('Click to continue'); ?>



<p class="entry-tags"><?php the_tags('<strong>Posted in: </strong>', ', ', ''); ?> </p>



</div><!-- end content -->



</div><!-- end entry-content -->



</div><!-- end entry -->



<?php endforeach; ?>



<?php include (TEMPLATEPATH . '/navigation.php'); ?>



<?php else : ?>



<h2 class="page_header center">Not Found</h2>

<div class="entry">

<p class="center">Sorry, but you are looking for something that isn't here.</p>

<?php include (TEMPLATEPATH . "/searchform.php"); ?>

</div>



<?php endif; ?>



</div><!-- end content-area-->



</div><!-- end content-box -->



<?php get_sidebar(); ?>



<?php get_footer(); ?>