Wordpress latest Posts AND comments feed?

Question

I'm trying to write a custom SQL query that will create a list of the most recent Posts AND comments and I'm having trouble visualizing how i can do this.

I can pull the latest comments by date DESC and i can pull the latest posts by date DESC but how do I make a feed / query to show them both?

Here is my comment SQL

SELECT comment_id, comment_content, comment_date
FROM wp_comments
WHERE comment_author = 'admin'
ORDER BY comment_date DESC

Edit: more clear:

Sorry, I should have been a little more clear. I want to have a list like this based on what date they occurred:

Wordpress post 
wordpress post
wordpress comment
wordpress post
wordpress comment

So if someone comments on a 4 month old post, it will still show up at the top of this 'feed'

Answer 1

No special mySql necessary, just use the query_posts loop and the get_comments function in the loop.

 <?php query_posts('posts_per_page=10'); while (have_posts()) : the_post(); ?>

    <div>
        <a  href="<?php the_permalink() ?>" title="Permanent Link to <?php the_title_attribute(); ?>"<?php the_title_attribute(); ?></a>
        <a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>"><?php echo the_title(); ?></a>
        <?php 
            $comments = get_comments();
            foreach($comments as $comm) :
              echo($comm->comment_author);
              echo($comm->comment_content);
            endforeach;
        ?>
    </div>

<?php endwhile; ?>

Answer 2

I think your best bet (and to avoid using custom SQL) would be to grab the latest X posts and X comments, loop over each and build an array to merge the two into one 'most recent' data set;

$comments = get_comments('number=X');
$posts    = get_posts('posts_per_page=X');

$most_recent = array();

foreach ($comments as $comment)
    $most_recent[strtotime($comment->comment_date_gmt)] = $comment;

foreach ($posts as $post)
    $most_recent[strtotime($post->post_date_gmt)] = $post;

unset($comments, $posts);

krsort($most_recent);
$most_recent = array_slice($most_recent, 0, X);

foreach ($most_recent as $post_or_comment) {

    $is_post = isset($post_or_comment->post_date_gmt);

    // output comments and posts
}

It ain't too pretty, but it should do it.

Answer 3

To get a list based solely on the most recent timestamp from two tables, you need to use a UNION:

SELECT wc.comment_date AS dt
  FROM WP_COMMENTS wc
UNION ALL
SELECT wp.post_date AS dt
  FROM WP_POSTS wp
ORDER BY dt

...where dt is the column alias for the column that holds the date value for records in either table.

Use UNION ALL - because data is from two different tables, there's no change of duplicates to filter out. But that means you have to get the other columns you want from either table to line up based on data and data type...