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 ?
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.