WordPress.org

Support

Support » How-To and Troubleshooting » [Resolved] One recent post from each category

[Resolved] One recent post from each category

  • I needed to get a list of recent posts – one from each category and could not find a built-in capabilities, so I created a custom SQL-query:

    $wpdb->get_results (
    	"SELECT posts.ID, terms.term_id FROM $wpdb->posts AS posts
    	JOIN(SELECT MAX(posts.post_date) AS post_date, posts.post_type AS post_type, posts.post_status AS post_status, terms.term_id AS term_id
    	FROM $wpdb->posts AS posts
    	LEFT JOIN $wpdb->term_relationships AS rel ON posts.ID=rel.object_ID
    	LEFT JOIN $wpdb->term_taxonomy AS tax USING(term_taxonomy_id)
    	LEFT JOIN $wpdb->terms AS terms USING(term_id)
    	WHERE posts.post_type='post' AND posts.post_status='publish' AND tax.taxonomy='category'
    	GROUP BY term_id) AS terms USING(post_date, post_type, post_status) ORDER BY post_date DESC"
    	);

    Query works fine, but I’m novice to WordPress and ‘ll be grateful to get point to a possible redundancy or errors of the query. Or there may be an easier way?

    NOTE: This query recognizes that the post can be simultaneously in multiple categories. For example, if post_1 of 12-Dec in cat_1 and cat_2, and post_2 of 14-Dec in cat_2 and cat_3, output will be cat_1->post_1, cat_2->post_2, cat_3->post_2.

Viewing 10 replies - 1 through 10 (of 10 total)
  • why not just use wordpress’s built in functions? you could grab all categories, and then foreach category, grab the most recent post. let us know if you need help doing this.

    Yes, initially did.

    But I’m stopped on pagination and also saw the problem that foreach category will a lot of sql queries.

    In general I thought it would be convenient to use a WP loop, because due to this will be available built-in WP features.

    Now I’ve created the class (based on the http://wordpress.stackexchange.com/questions/3708/custom-taxonomy-wp-query-for-all-terms-in-a-taxonomy example):

    class Recent_in_Taxonomies extends WP_Query {
    	var $taxonomy;
    	function __construct ( $args = array() ) {
    		add_filter ( 'posts_join', array( &$this, 'posts_join' ), 10, 2 );
    		$this->taxonomy = $args['taxonomy'];
    		parent::query ( $args );
    	}
    	function posts_join( $join, $query ) {
    		global $wpdb;
    		$join .=<<<SQL
    JOIN( SELECT MAX( {$wpdb->posts}.post_date ) AS post_date FROM {$wpdb->posts}
    LEFT JOIN {$wpdb->term_relationships} AS rel ON {$wpdb->posts}.ID=rel.object_ID
    LEFT JOIN {$wpdb->term_taxonomy} AS tax USING( term_taxonomy_id )
    LEFT JOIN {$wpdb->terms} AS trm USING( term_id )
    WHERE tax.taxonomy='{$this->taxonomy}' GROUP BY trm.term_id ) AS rmt USING( post_date )
    SQL;
    		return $join;
    	}
    }

    and call it:

    $args[ 'post_type' ] = 'post';
    $args[ 'taxonomy' ] = 'category';
    $args[ 'paged' ] = $paged;
    
    $loop = new Recent_in_Taxonomies( $args );
    
    get_template_part ( 'loop' );

    while ( have_posts() ) : the_post();

    Everything seems to be ok.

    But now is a new huge problem: query recognizes that the post can be simultaneously in multiple categories, and loop receive post__in = 1, 2, 45, 1, 45, 1.

    Yes, I need to get in the loop pages including duplicates (I did this query for). But loop only shows unique post, and I’m not yet figured out what to do.

    so if a post is the most recent in two categories, it only shows that post for the first category, and then the second category it shows the second most recent post?

    OMG, I was stupid.

    But now understand:

    if a post is the most recent in two categories, it only shows that post for the first category

    – found that there were just the general recent posts list, but it was the result of default wp_query on home page! My query was out after and I have not seen it result.

    I rewrote the code, my index.php now is:

    <?php
    
    get_header();
    
    if  ( is_home() ) : // Get latest posts from each category
    
    	function recent_join ( $join, $query ) {
    		global $wpdb;
    		$taxonomy = 'category';
    		$join .=<<<SQL
    JOIN( SELECT MAX( {$wpdb->posts}.post_date ) AS post_date, trm.term_id AS term_ID FROM {$wpdb->posts}
    LEFT JOIN {$wpdb->term_relationships} AS rel ON {$wpdb->posts}.ID=rel.object_ID
    LEFT JOIN {$wpdb->term_taxonomy} AS tax USING( term_taxonomy_id )
    LEFT JOIN {$wpdb->terms} AS trm USING( term_id )
    WHERE tax.taxonomy='{$taxonomy}' GROUP BY trm.term_id ) AS pmx USING( post_date )
    SQL;
    		return $join;
    	}
    	add_filter ( 'posts_join', 'recent_join', 10, 2 );
    
    	function recent_fields ( $fields, $query ) {
    		global $wpdb;
    		return $fields . ', term_ID';
    	}
    	add_filter ( 'posts_fields', 'recent_fields', 10, 2 );
    
    	$args = array (
    		'post_type' => 'post',
    		'taxonomy' => 'category',
    		'paged' => get_query_var ( 'paged' ) ? get_query_var ( 'paged' ) : 1
    	);
    	$wp_query = new WP_Query ( $args );
    
    endif; // ------------------------------------------------
    
    get_template_part ( 'loop' );
    
    get_footer();
    
    ?>

    and it working!

    But unfortunately the default wp_query is still running in front of my cycle (although it isn’t needed, not used).
    Please tell me whether and how to replace with my own (or disable) default wp_query (on homepage)?

    you would need to add a function to run on a hook that takes place before the query, and detect the user is viewing the home page, and disable the default query that way.

    already unsuccessfully tried ( w/o hook ) at the functions.php

    what the hook’s name ( init ? ) and where is the ‘place before’ default wp_query? 10x!

    here is a list of hooks, in chronological order: http://codex.wordpress.org/Plugin_API/Action_Reference

    Great, I placed it in a ‘parse_query’ hook.

    Now looking for another way to run query only on home page, because is_home == TRUE only after performing wp_query.

    Found solution:

    function recent_get_posts ( &$wp_query ) {
    		if ( is_home() ) :
    			static $first_call = true;
    			if ( ! $first_call ) return;
    			else $first_call = false;
    			$wp_query->query_vars = array(
    				'post_type' => 'post',
    				'taxonomy' => 'category',
    				'paged' => get_query_var( 'paged' ) ? get_query_var( 'paged' ) : 1
    			);
    			function recent_join( $join, $query ) {
    				global $wpdb;
    				$taxonomy = 'category';
    				$join .=<<<SQL
    JOIN( SELECT MAX( {$wpdb->posts}.post_date ) AS post_date, trm.term_id AS term_ID FROM {$wpdb->posts}
    LEFT JOIN {$wpdb->term_relationships} AS rel ON {$wpdb->posts}.ID=rel.object_ID
    LEFT JOIN {$wpdb->term_taxonomy} AS tax USING( term_taxonomy_id )
    LEFT JOIN {$wpdb->terms} AS trm USING( term_id )
    WHERE tax.taxonomy='{$taxonomy}' GROUP BY trm.term_id ) AS pmx USING( post_date )
    SQL;
    				return $join;
    			}
    			add_filter( 'posts_join', 'recent_join', 10, 2 );
    			function recent_fields( $fields, $query ) {
    				global $wpdb;
    				return $fields . ', term_ID';
    			}
    			add_filter( 'posts_fields', 'recent_fields', 10, 2 );
    		endif;
    	}
    	add_action ( 'pre_get_posts', 'recent_get_posts' );

    glad it works 🙂 *mark as resolved*

Viewing 10 replies - 1 through 10 (of 10 total)
  • The topic ‘[Resolved] One recent post from each category’ is closed to new replies.