Latest Activity in a category WordPress

Ant Eksiler comments:

I dont want plugin or standalone solutions

Oleg Butuzov comments:

http://wordpress.org/extend/plugins/get-recent-comments/

Oleg Butuzov comments:

as for posts for example from category one ....

<?php query_posts('cat=1'); ?>
...

an so on...

Oleg Butuzov comments:

http://codex.wordpress.org/Function_Reference/query_posts
http://codex.wordpress.org/The_Loop
http://www.smashingmagazine.com/2009/06/10/10-useful-wordpress-loop-hacks/

Ant Eksiler comments:

I know those bits but I would like to display a single bit showing the latest post or comment.

For example, if there is a new comment on a post 2 years old. The latest activity will be this comment. Not a post that is made a day ago.

As you can see, latest activity requires the comment time and post times are compared or stored in another variable.

Oleg Butuzov comments:

i know that you don't want a plugin, but still check the
http://wordpress.org/extend/plugins/get-recent-comments/

its work better than stickct sql. there no conditional tegs of wordpress for it.

instead using simple sql queries whan you will need to select categories that are nested by parent (that you will select), and select allposts ids that are in your categories, and only after that select comments that are connected to the posts from your categories.

well. better to use plugin.

Oleg Butuzov comments:

and yeah that plugin provide a code that you can easy add to please where you want to show the last comments. its not overloading front end.

Ant Eksiler comments:

I don't want to display latest comments?

Oleg Butuzov comments:

i asume you want to do it =). but one of your comments tell me that

<blockquote>I dont want plugin or standalone solutions</blockquote>...

check the plugin =)

Ant Eksiler comments:

I don't need the plugin because I can get the recent comments using:

$comments = get_comments('status=approve&number=4');


Latest Activity in a category means more advanced coding.

Oleg Butuzov comments:

didn't you wanted to get it fro specifyc category?

Ant Eksiler comments:

Please read my question again because I can certainly google "wordpress recent comments"

Oleg Butuzov comments:

// parent category
$parentID = 66;

//cateogries
$categories = get_terms('category', array('child_of' => $parentID, 'hide_empty' => false));
$categryList = array($parentID);
foreach($categories as $term){
$categryList[] = (int) $term->term_id;
}

// posts
$posts = get_objects_in_term($categryList, 'category');

// last activity
$sql = "
SELECT
'post' as type,
p.ID as id,
p.post_date as date,
p.post_title as content,
u.user_nicename
FROM
{$wpdb->posts} p,
{$wpdb->users} u
WHERE
p.ID in (".implode(',', $posts).") AND
p.post_author = u.ID
UNION SELECT
'comment',
c.comment_ID,
c.comment_date ,
c.comment_content ,
c.comment_author
FROM {$wpdb->comments} c
WHERE
c.comment_post_ID in (".implode(',', $posts).")
ORDER by date DESC LIMIT 10
";
$data = $wpdb->get_results($sql);

change parentID for category...
$data is yours!

cheers!

Oleg Butuzov comments:

with output

// parent category
$parentID = 66;

//cateogries
$categories = get_terms('category', array('child_of' => $parentID, 'hide_empty' => false));
$categryList = array($parentID);
foreach($categories as $term){
$categryList[] = (int) $term->term_id;
}

// posts
$posts = get_objects_in_term($categryList, 'category');

// last activity
$sql = "
SELECT
'post' as type,
p.ID as id,
p.post_date as date,
p.post_title as content,
u.user_nicename as user
FROM
{$wpdb->posts} p,
{$wpdb->users} u
WHERE
p.ID in (".implode(',', $posts).") AND
p.post_author = u.ID
UNION SELECT
'comment',
c.comment_ID,
c.comment_date ,
c.comment_content ,
c.comment_author
FROM {$wpdb->comments} c
WHERE
c.comment_post_ID in (".implode(',', $posts).")
ORDER by date DESC LIMIT 10
";
$data = $wpdb->get_results($sql);



echo '<ul>';
foreach($data as $item){
switch($item->type){
case 'comment':
?>
<li class='comment'><a href="<?php echo get_comment_link($item->id); ?>"><?php echo apply_filters('the_title', substr($item->content,0,255)); ?></a><br /> by <?php echo $item->user; ?> </li>
<?

break;
case 'post':
?>
<li class='post'><a href="<?php echo get_permalink($item->id); ?>"><?php echo apply_filters('the_title', substr($item->content,0,255)); ?></a><br /> by <?php echo $item->user; ?> </li>
<?
break;
}
}
echo '</ul>';

Ant Eksiler comments:

Thats a good start and I have thought of it before but that is true for the last post only.

What if a user makes a comment on a post that is older? You script wont see that.

Lew Ayotte comments:

Ah, good point... let me rethink...

Lew Ayotte comments:

This seems to work... except the comment link, still trying to figure that one out:


$args = array('cat' => 4,
'showposts' => 1,
'orderby' => 'date' );
$latest = new WP_Query($args);

$latest->the_post();
//print_r($post);

$args = array('status' => "approve",
'order' => 'ASC',
'number' => 1);
$comment = get_comments($args);
//print_r($comment);

if (strtotime($comment[0]->comment_date_gmt) > strtotime($post->post_date_gmt)) { ?>
<a href="<?php get_comment_link($comment[0]->comment_ID); ?>"><?php the_title(); ?></a><br />
by <a href="<?php echo esc_url($comment[0]->comment_author_url); ?>"><?php echo $comment[0]->comment_author; ?></a>
<?php } else { ?>
<a href="<?php the_permalink() ?>"><?php the_title(); ?></a><br />
by <?php the_author() ?>
<?php }


Lew Ayotte comments:

Oh wait, that's not for a comment in a specific category...

Lew Ayotte comments:

Try this... everything *should* be working now :)

$args = array('cat' => 4,
'showposts' => 1,
'orderby' => 'date' );
$latest = new WP_Query($args);

$latest->the_post();
//print_r($post);

$args = array('status' => "approve",
'order' => 'ASC' );
$comments = get_comments($args);
foreach ($comments as $comment) {
if (in_category(4, $comment->comment_post_ID)) {
$latest_comment = $comment;
break;
}
}
//print_r($comment);

if (strtotime($latest_comment->comment_date_gmt) > strtotime($post->post_date_gmt)) { ?>
<a href="<?php echo get_comment_link($latest_comment->comment_ID); ?>"><?php the_title(); ?></a><br />
by <a href="<?php echo esc_url($latest_comment->comment_author_url); ?>"><?php echo $latest_comment->comment_author; ?></a>
<?php } else { ?>
<a href="<?php the_permalink() ?>"><?php the_title(); ?></a><br />
by <?php the_author() ?>
<?php }

Lew Ayotte comments:

The comment arg should be DESC not ASC:

$args = array('cat' => 4,
'showposts' => 1,
'orderby' => 'date' );
$latest = new WP_Query($args);

$latest->the_post();
//print_r($post);

//echo "<hr>";

$args = array('status' => "approve",
'order' => 'DESC' );
$comments = get_comments($args);
foreach ($comments as $comment) {
if (in_category(4, $comment->comment_post_ID)) {
$latest_comment = $comment;
break;
}
}
//print_r($comments);

//echo "<hr>";

if (strtotime($latest_comment->comment_date_gmt) > strtotime($post->post_date_gmt)) { ?>
<a href="<?php echo get_comment_link($latest_comment->comment_ID); ?>"><?php the_title(); ?></a><br />
by <a href="<?php echo esc_url($latest_comment->comment_author_url); ?>"><?php echo $latest_comment->comment_author; ?></a>
<?php } else { ?>
<a href="<?php the_permalink() ?>"><?php the_title(); ?></a><br />
by <?php the_author() ?>
<?php }