Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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.

share|improve this question
    
How many of each do you want? Do you want reverse chronological? What attributes of each do you want/need? Permalink, Excerpt? More? –  MikeSchinkel Mar 27 '11 at 15:53
    
@MikeSchinkel - "How many of each do you want?" I don't want to limit the result. "Do you want reverse chronological?" Yes. "What attributes of each do you want/need?" I just tried what was suggested by @scribu about using SQL UNION and it's working well but as you ask I have now some news problems about the data that I would like to be able to retrieve. For example I would like to access the "comment_count" which is in the posts table but doesn't have any equivalent in the comments table. –  inwpitrust Mar 27 '11 at 16:38
    
I you have an additional question please ask another question here on the WPSE site. –  MikeSchinkel Mar 28 '11 at 3:15

1 Answer 1

up vote 3 down vote accepted

I believe you're looking for SQL UNION.

share|improve this answer
    
Thanks for your suggestion, I updated my post. My next problem is: Each result is display in a div, I would like to be able to add a class with the type of result for each. For example: Each comment get a class="comment" and each post get a class="post". Any idea ? –  inwpitrust Mar 27 '11 at 16:52
2  
Sure, just add post_type AS entry_type and 'comment' AS entry_type columns and then check it when doing the output. –  scribu Mar 27 '11 at 17:09
    
You can use the same trick for the comment count: comment_count and 0 AS comment_count respectively. –  scribu Mar 27 '11 at 17:11
    
Thank you very much! –  inwpitrust Mar 27 '11 at 21:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.