WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] One recent post from each category (11 posts)

  1. Roues
    Member
    Posted 3 years ago #

    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.

  2. ucfknight10
    Member
    Posted 3 years ago #

    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.

  3. Roues
    Member
    Posted 3 years ago #

    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.

  4. ucfknight10
    Member
    Posted 3 years ago #

    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?

  5. Roues
    Member
    Posted 3 years ago #

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

  6. ucfknight10
    Member
    Posted 3 years ago #

    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.

  7. Roues
    Member
    Posted 3 years ago #

    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!

  8. ucfknight10
    Member
    Posted 3 years ago #

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

  9. Roues
    Member
    Posted 3 years ago #

    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.

  10. Roues
    Member
    Posted 3 years ago #

    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' );
  11. ucfknight10
    Member
    Posted 3 years ago #

    glad it works :) *mark as resolved*

Topic Closed

This topic has been closed to new replies.

About this Topic