How to make an activities stream mixing posts and comments?

by

What I’m currently trying to make is an activities stream wich list :

  • The posts,
  • the comments,
  • both mixed between them and ordered by date.

My idea is to make two queries, but i don’t know how to mix them up. Here are my queries :

// Query the posts :
$queryPosts = "
    SELECT * FROM $wpdb->posts
    WHERE post_type="post"
    AND post_status="publish"
    ORDER BY post_date DESC
";

// Query the comments :
$queryComments = "
    SELECT * FROM $wpdb->comments
    ORDER BY comment_date DESC
";

Is that possible with some kind of SQL JOIN ?

UPDATE:
I tried what was suggested by @scribu about using SQL UNION and it’s working well : 

SELECT ID AS entry_id, post_date AS entry_date, post_content AS entry_content FROM $wpdb->posts
WHERE post_type="post"
AND post_status="publish"
UNION
SELECT comment_ID AS entry_id, comment_date AS entry_date, comment_content AS entry_content FROM $wpdb->comments
ORDER BY entry_date DESC

What I’m trying to do now is access some data which are in the posts table and not in the comments table. Any idea ?

Thanks by advance.

1 Answer
1

I believe you’re looking for SQL UNION.

Leave a Comment