vrandom

Hi @ainsleyclark

Awesome glad it started working mostly – lol.

Not sure what could be causing the page number problem.

I guess start dumping out the vars and checking the math.

// set vars for found_posts, max_posts_count and build the post_order_index
$found_posts = 0;
$max_posts_count=0;
foreach ($page_count_per_post_type as $page_count) {

	// make a total post found for all post_types
	$found_posts += $page_count[0];

	// max posts - total of posts per type
	$max_posts_count += $page_count[0];

	// set up post order index
	$post_order_index[$page_count[1]] = explode(',', $page_count[2]);

}

// set the max pages for the results
$max_posts_num_pages = ceil( $max_posts_count / ($post_per_posttype * count($post_types)));

Hi @ainsleyclark

odd i linked it from the gist and its line 320 there.

What you have should have worked. Add a dump of the sql, lets see what is looks like.

// base template for sql query
$sql_template = "select count(foe_342fj29x2_posts.id), foe_342fj29x2_posts.post_type#sql_col# from foe_342fj29x2_posts #join_templates# where foe_342fj29x2_posts.post_type in ('".implode("', '", $post_types)."') group by foe_342fj29x2_posts.post_type";

// Assemble Query

// adds a comma separated list of the post ids that are found in each join statement to the results
//$found_post_query_select_addon = "CASE " . implode(' ', $selectFoundPostQueryAddon). " ELSE null END as post_ids";

$found_post_query_select_addon = "post_type_0.grouped_id, post_type_1.grouped_id, post_type_2.grouped_id";

$sql_template = str_replace("#sql_col#", ','.$found_post_query_select_addon, $sql_template);

// adds generated joins to query
$sql_template = str_replace("#join_templates#", implode("\n", $joinTemplate), $sql_template);

echo "<pre>";
var_dump($sql_template);
echo "</pre>"

// execute posts build query
global $wpdb;
$page_count_per_post_type = $wpdb->get_results($sql_template, ARRAY_N);

// First place to check if we have results
echo "<pre>";
var_dump($page_count_per_post_type);
echo "</pre>"

Hi @ainsleyclark

Its not generating the post_ids result.

The $selectFoundPostQueryAddon array part of the query is not working for some reason.

For testing, try this to make sure there is data.

above where the pre statement is find the following and comment it out,

// $found_post_query_select_addon = "CASE " . implode(' ', $selectFoundPostQueryAddon). " ELSE null END as post_ids";

make a new line under it

$found_post_query_select_addon = "post_type_0.grouped_id, post_type_1.grouped_id, post_type_2.grouped_id";

then run the page again as see what data you get.

Ok, I see why the errors occurred.

First you forgot to update these.

// set up vars
$post_slug='news'; //$post->post_name;
$post_types = ['post1', 'post2', 'post3'];

So the first query returned no data which caused the errors.

To account for the possibility of no data returned from the first query we need to update one line. Wrap the while loop in a if count to make sure we have post_ids_to_fetch.

// if we need more posts to make total wanted on page
if (count($post_ids_to_fetch)) {
  while (count($post_ids_to_fetch) < (count($post_types) * $post_per_posttype)) {
    ...
  }
}

Give that a try and let me know how it goes.

Rebuilt code – a whole lot has changed.

It will:
– fetch up to $post_per_posttype number of posts for each post type specified in the $post_types array
– if this result is less than (count($post_types) * $post_per_posttype), then it will attempt to get more posts from the other post types.
– it will use the $post_types order to get new listings

Let me know if this works for what your wanting.

Readable copy here, https://gist.github.com/TerrePorter/110facd34376b8ab171890a8bb247081

<?php
    // hack to overwrite the max_num_queries var
		class WP_Query_CustomSQL extends WP_Query
		{

			function get_posts() {
			    $posts = parent::get_posts();

			    // overwrite max_num_pages settings if set
			    $q = &$this->query_vars;

			    if (isset($q['max_num_pages'])) {
			        $this->max_num_pages = $q['max_num_pages'];
			    }

			    return $posts;
            }
		}

		function paginate_links_with_provided_wpquery( $args = '', $wp_query) {
			global $wp_rewrite;

			// Setting up default values based on the current URL.
			$pagenum_link = html_entity_decode( get_pagenum_link() );
			$url_parts    = explode( '?', $pagenum_link );

			// Get max pages and current page out of the current query, if available.
			$total   = isset( $wp_query->max_num_pages ) ? $wp_query->max_num_pages : 1;
			$current = get_query_var( 'paged' ) ? intval( get_query_var( 'paged' ) ) : 1;

			// Append the format placeholder to the base URL.
			$pagenum_link = trailingslashit( $url_parts[0] ) . '%_%';

			// URL base depends on permalink settings.
			$format  = $wp_rewrite->using_index_permalinks() && ! strpos( $pagenum_link, 'index.php' ) ? 'index.php/' : '';
			$format .= $wp_rewrite->using_permalinks() ? user_trailingslashit( $wp_rewrite->pagination_base . '/%#%', 'paged' ) : '?paged=%#%';

			$defaults = array(
				'base'               => $pagenum_link, // http://example.com/all_posts.php%_% : %_% is replaced by format (below)
				'format'             => $format, // ?page=%#% : %#% is replaced by the page number
				'total'              => $total,
				'current'            => $current,
				'aria_current'       => 'page',
				'show_all'           => false,
				'prev_next'          => true,
				'prev_text'          => __( '&laquo; Previous' ),
				'next_text'          => __( 'Next &raquo;' ),
				'end_size'           => 1,
				'mid_size'           => 2,
				'type'               => 'plain',
				'add_args'           => array(), // array of query args to add
				'add_fragment'       => '',
				'before_page_number' => '',
				'after_page_number'  => '',
				'hide_page_numbers'  => false
			);

			$args = wp_parse_args( $args, $defaults );

			if ( ! is_array( $args['add_args'] ) ) {
				$args['add_args'] = array();
			}

			// Merge additional query vars found in the original URL into 'add_args' array.
			if ( isset( $url_parts[1] ) ) {
				// Find the format argument.
				$format = explode( '?', str_replace( '%_%', $args['format'], $args['base'] ) );
				$format_query = isset( $format[1] ) ? $format[1] : '';
				wp_parse_str( $format_query, $format_args );

				// Find the query args of the requested URL.
				wp_parse_str( $url_parts[1], $url_query_args );

				// Remove the format argument from the array of query arguments, to avoid overwriting custom format.
				foreach ( $format_args as $format_arg => $format_arg_value ) {
					unset( $url_query_args[ $format_arg ] );
				}

				$args['add_args'] = array_merge( $args['add_args'], urlencode_deep( $url_query_args ) );
			}

			// Who knows what else people pass in $args
			$total = (int) $args['total'];
			if ( $total < 2 ) {
				return;
			}
			$current  = (int) $args['current'];
			$end_size = (int) $args['end_size']; // Out of bounds?  Make it the default.
			if ( $end_size < 1 ) {
				$end_size = 1;
			}
			$mid_size = (int) $args['mid_size'];
			if ( $mid_size < 0 ) {
				$mid_size = 2;
			}
			$add_args = $args['add_args'];
			$r = '';
			$page_links = array();
			$dots = false;

			if ( $args['prev_next'] && $current && 1 < $current ) :
				$link = str_replace( '%_%', 2 == $current ? '' : $args['format'], $args['base'] );
				$link = str_replace( '%#%', $current - 1, $link );
				if ( $add_args )
					$link = add_query_arg( $add_args, $link );
				$link .= $args['add_fragment'];

				/**
				 * Filters the paginated links for the given archive pages.
				 *
				 * @since 3.0.0
				 *
				 * @param string $link The paginated link URL.
				 */
				$page_links[] = '<a class="prev page-numbers" href="' . esc_url( apply_filters( 'paginate_links', $link ) ) . '">' . $args['prev_text'] . '</a>';
			endif;
			if (! $args['hide_page_numbers']) {
                for ( $n = 1; $n <= $total; $n++ ) :
                    if ( $n == $current ) :
                        $page_links[] = "<span aria-current='" . esc_attr( $args['aria_current'] ) . "' class='page-numbers current'>" . $args['before_page_number'] . number_format_i18n( $n ) . $args['after_page_number'] . "</span>";
                        $dots = true;
                    else :
                        if ( $args['show_all'] || ( $n <= $end_size || ( $current && $n >= $current - $mid_size && $n <= $current + $mid_size ) || $n > $total - $end_size ) ) :
                            $link = str_replace( '%_%', 1 == $n ? '' : $args['format'], $args['base'] );
                            $link = str_replace( '%#%', $n, $link );
                            if ( $add_args )
                                $link = add_query_arg( $add_args, $link );
                            $link .= $args['add_fragment'];

                            /** This filter is documented in wp-includes/general-template.php */
                            $page_links[] = "<a class='page-numbers' href='" . esc_url( apply_filters( 'paginate_links', $link ) ) . "'>" . $args['before_page_number'] . number_format_i18n( $n ) . $args['after_page_number'] . "</a>";
                            $dots = true;
                        elseif ( $dots && ! $args['show_all'] ) :
                            $page_links[] = '<span class="page-numbers dots">' . __( '&hellip;' ) . '</span>';
                            $dots = false;
                        endif;
                    endif;
                endfor;
			}
			if ( $args['prev_next'] && $current && $current < $total ) :
				$link = str_replace( '%_%', $args['format'], $args['base'] );
				$link = str_replace( '%#%', $current + 1, $link );
				if ( $add_args )
					$link = add_query_arg( $add_args, $link );
				$link .= $args['add_fragment'];

				/** This filter is documented in wp-includes/general-template.php */
				$page_links[] = '<a class="next page-numbers" href="' . esc_url( apply_filters( 'paginate_links', $link ) ) . '">' . $args['next_text'] . '</a>';
			endif;
			switch ( $args['type'] ) {
				case 'array' :
					return $page_links;

				case 'list' :
					$r .= "<ul class='page-numbers'>\n\t<li>";
					$r .= join("</li>\n\t<li>", $page_links);
					$r .= "</li>\n</ul>\n";
					break;

				default :
					$r = join("\n", $page_links);
					break;
			}
			return $r;
		}
        // ----------------------------------------------------

        // set up vars
		$post_slug='news'; //$post->post_name;
        $post_types = ['post1', 'post2', 'post3'];

        // number of post to show per each post_type
        $post_per_posttype = 2;

        // to keep the last post id that is being displayed on the page
        $last_post_id_by_type= [];

        // keeps the display of posts order
        $post_order_index= [];

        // get the current page number
        $current_page_number = get_query_var('paged', 1);
        // if 0, then we are on page 1
        if ($current_page_number==0) { $current_page_number = 1; }

        // check if we have sql_offset info for this page load
        $transient_key = implode('-', $post_types).'_sqloffset_page';
        if ( false === ( $sql_offset = get_transient( $transient_key . $current_page_number) ) ) {
            // no valid transient set
            foreach ($post_types as $post_type_key => $post_type) {
                $sql_offset[$post_type] = 0;
            }
        }

		// Make SQL Parts
        $joinTemplate = [];
        $whereTemplate = [];
		foreach ($post_types as $post_type_key => $post_type) {

		    $joinTemplate[] = "left join 			 
                (select GROUP_CONCAT(id  order by post_date desc, id desc) as grouped_id, post_type from (
                      SELECT id, post_type, post_date
                      FROM wp_posts 
                        inner join wp_term_relationships on wp_term_relationships.object_id = id
                        inner join wp_term_taxonomy on wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
                        inner join wp_terms on wp_term_taxonomy.term_id = wp_terms.term_id
                        where wp_term_taxonomy.taxonomy = 'category' and wp_terms.slug = '$post_slug'
                       and post_type in ('$post_type')
                       and post_status = 'publish'
                       order by post_date desc, id desc                       
                      ) d$post_type_key
                     GROUP BY d$post_type_key.post_type) post_type_$post_type_key		 
                     ON wp_posts.post_type = post_type_$post_type_key.post_type";

		    $selectFoundPostQueryAddon[] = "WHEN wp_posts.post_type = '$post_type' THEN post_type_$post_type_key.grouped_id";
		}

		// base tempate for sql query
		$sql_template = "select count(wp_posts.id), wp_posts.post_type#sql_col# from wp_posts #join_templates# where wp_posts.post_type in ('".implode("', '", $post_types)."') group by wp_posts.post_type";

        // Assemble Query

		// adds a comma seperated list of the post ids that are found in each join statement to the results
		$found_post_query_select_addon = "CASE " . implode(' ', $selectFoundPostQueryAddon). " ELSE null END as post_ids";
		$sql_template = str_replace("#sql_col#", ','.$found_post_query_select_addon, $sql_template);

		// adds generated joins to query
		$sql_template = str_replace("#join_templates#", implode("\n", $joinTemplate), $sql_template);

        // execute posts build query
		global $wpdb;
		$page_count_per_post_type = $wpdb->get_results($sql_template, ARRAY_N);

        // set vars for found_posts, max_posts_count and build the post_order_index
		$found_posts = 0;
		$max_posts_count=0;
		foreach ($page_count_per_post_type as $page_count) {

		    // make a total post found for all post_types
		    $found_posts += $page_count[0];

		    // max posts - total of posts per type
		    $max_posts_count += $page_count[0];

		    // set up post order index
		    $post_order_index[$page_count[1]] = explode(',', $page_count[2]);

		}

		// set the max pages for the results
		$max_posts_num_pages =  ceil( $max_posts_count / ($post_per_posttype * count($post_types)));

        // get base post ids to show using the normal post_per_posttype
		$no_more_post_for_post_type=[];
		$post_ids_to_fetch = [];
		foreach ($post_order_index as $k => $v) {

            // using the sql_offset for this post type, get the next x results from the post_order_index
            $t = array_slice($v, $sql_offset[$k], $post_per_posttype);

            // if number available is less than or equal to the offset+post_per_posttype, then there are no more post to fetch for this type
            if (count($v) <=  ($sql_offset[$k] + $post_per_posttype)) {
                $no_more_post_for_post_type[] = $k;
            }

            // keep a growing list of post ids
		    $post_ids_to_fetch = $post_ids_to_fetch + array_combine($t, array_pad([], count($t), $k));
		}

		// if we need more posts to make total wanted on page
	    while (count($post_ids_to_fetch) < (count($post_types) * $post_per_posttype)) {

	        // get current count of postids to fetch by post type
		    $post_ids_to_fetch_count_per_type = array_count_values($post_ids_to_fetch);

		    // remove any post_types that do not have any more posts
		    $post_types_with_more_posts = [];
		    foreach ($post_types as $k => $v) {
		        if (!in_array($v, $no_more_post_for_post_type)) {
		            $post_types_with_more_posts[] =$v;
		        } else {
		            unset($post_ids_to_fetch_count_per_type[$v]);
		        }
		    }

		    // if have more posts
		    if (count($post_types_with_more_posts)) {

		        // get the post type with the lowest count from the number of ids in $post_ids_to_fetch
                $fetch_more_from_post_type = array_search(min($post_ids_to_fetch_count_per_type), $post_ids_to_fetch_count_per_type);

                // get the next post id from the post order index
                $t = array_slice($post_order_index[$fetch_more_from_post_type], $sql_offset[$fetch_more_from_post_type] + $post_ids_to_fetch_count_per_type[$fetch_more_from_post_type], 1);

                // check if this is the last id available
                if ((count($post_order_index[$fetch_more_from_post_type]) <=  ($sql_offset[$fetch_more_from_post_type] + $post_ids_to_fetch_count_per_type[$fetch_more_from_post_type] + 1))) {
                    $no_more_post_for_post_type[] = $fetch_more_from_post_type;
                }

                $post_ids_to_fetch = $post_ids_to_fetch + array_combine($t, array_pad([], count($t), $fetch_more_from_post_type));

		    } else {
		        //There are no more posts to fetch, stop trying to get more
		        break; // exit the while loop
		    }

		}

		// get current count of postids to fetch by post type
		$post_ids_to_fetch_count_per_type = array_count_values($post_ids_to_fetch);

		// fetch the display posts query
		$loop = new WP_Query_CustomSQL(
		        array(
		                'post_type'      => $post_types,
                        'post__in'      => array_keys($post_ids_to_fetch),
		                'max_num_pages' => $max_posts_num_pages
		             )
		        );

 		// put the post in the order of the post_types array
		if ($loop->have_posts()) {

		    // make posts index by post_type
		    $tmpPosts = [];
            foreach ($loop->posts as $k => $v) {
                $tmpPosts[get_post_type( $v->ID )][$v->ID] = $v;

                // update the offset to add in this post to the count
                $sql_offset[get_post_type( $v->ID )] += 1;
            }

            // assemble new ordered posts
            $finPosts = [];
            foreach ($post_types as $k => $v) {
                if (isset($tmpPosts[$v])) {

                    // reorder based on the sql result order
                    foreach ($post_order_index[$v] as $k1 => $v1) {
                        if (isset($tmpPosts[$v][$v1])) {

                            // make an array of posts grouping them in the post_types order
                            $finPosts[] = $tmpPosts[$v][$v1];

                            // set last post id for each type
		                    $last_post_id_by_type[$v] = (int) $v1;
                        }
                    }
                }
            }

            // update the $loop with the new ordered posts
            $loop->posts = $finPosts;

            // save the sql offset data for the next page view
            if ($current_page_number + 1 <= $max_posts_num_pages) {
                set_transient($transient_key . ($current_page_number + 1), $sql_offset);
            }
		}

		// begin display code

		$speakerCounter = 0;
		$exhibitorCounter = 0;
		$columnwidth = 'col-lg-6';
		if ( $loop->have_posts() ) :
			while ( $loop->have_posts() ) : $loop->the_post();

		        // get post type of current post
				$post_type = get_post_type( $post->ID );

				/// do something with the last post of each post_type being displayed on the page
                if ($last_post_id_by_type[$post_type] == $post->ID) {
                      //do something
                      echo "LAST POST";
                }

				if ($post_type == 'post1' && $newsCounter < $post_ids_to_fetch_count_per_type['post1']) {
                    $newsCounter++;
                    //require( locate_template ('blocks/content-newsrow.php'));
                    echo $post->ID . '] ' . $post_type . ' - '  .  get_permalink($post->ID)  . "<BR>";
                }
                if ($post_type == 'post2' && $exhibitorCounter < $post_ids_to_fetch_count_per_type['post2']) {
                    if ($exhibitorCounter == 0) {
                        echo '<div class="row"><div class="col-12 mb-2 mb-lg-3">'; ?>
                        <span class="contentbrands__title type__weight--medium">Exhibitors for <?php echo $contentbrand; ?>:</span>
                        </div></div>
                        <?php echo '<div class="row">'; ?>
                    <?php }
                        $exhibitorCounter++;
                        //require( locate_template ('blocks/content-exhibitor.php'));
                        echo $post->ID . '] ' . $post_type . ' - '  .  get_permalink($post->ID)  . "<BR>";
                    if ($exhibitorCounter == 2) {echo '</div>';}
                }
                if ($post_type == 'post3' && $speakerCounter < $post_ids_to_fetch_count_per_type['post3']) {
                    if ($speakerCounter == 0) {
                        echo '<div class="row"><div class="col-12 mb-2 mb-lg-3">'; ?>
                        <span class="contentbrands__title type__weight--medium">Experts for <?php echo $contentbrand; ?>:</span>
                        </div></div>
                        <?php echo '<div class="row">'; ?>
                    <?php }
                        $speakerCounter++;
                        //require( locate_template ('blocks/content-speaker.php'));
                        echo $post->ID . '] ' . $post_type . ' - '  .  get_permalink($post->ID)  . "<BR>";
                    if ($speakerCounter == 1) {echo '</div>';}
                }

			endwhile;

			// Previous/next page navigation.
			$args = array(
				'prev_text'          => __( 'Previous page', 'twentysixteen' ),
				'next_text'          => __( 'Next page', 'twentysixteen' ),
				'before_page_number' => '<span class="meta-nav screen-reader-text">' . __( 'Page', 'twentysixteen' ) . ' </span>',
				'screen_reader_text' => __( 'Posts navigation' ),
				'type'               => 'plain',
				'hide_page_numbers'          => true
			);

			// Set up paginated links.
			$links = paginate_links_with_provided_wpquery( $args , $loop);

			if ( $links ) {
				echo _navigation_markup( $links, 'pagination', $args['screen_reader_text'] );
			}
			?>

			<?php
		else :
			echo '<h3>No News</h3>';
		endif;
		wp_reset_postdata();

Yes that code block is in the paginate_links_with_provided_wpquery function.

Then down in the main code, update the settings (add hide_page_numbers to the array) for the paginate function call.

// Previous/next page navigation.
$args = array(
	'prev_text' => __( 'Previous page', 'twentysixteen' ),
	'next_text'          => __( 'Next page', 'twentysixteen' ),
	'before_page_number' => '<span class="meta-nav screen-reader-text">' . __( 'Page', 'twentysixteen' ) . ' </span>',
	'screen_reader_text' => __( 'Posts navigation' ),
	'type'               => 'plain',
	'hide_page_numbers'          => true
);

ok, i see what is happening. Odd that it didnt happen on my dev site but who knows why.

After the second query, there is some code that reorders the $loop->post as it was not grouping the post type next to each other and that caused some weird display issues.

I have updated the code to use the sql order to rebuild the array.

// get the largest page count on a page
		$largest_page_count_per_post_type = 0;
		$found_posts = 0;
		$last_post_id_by_type= [];
		$post_order_index= [];
		foreach ($page_count_per_post_type as $page_count) {
		    $largest_page_count_per_post_type = ($page_count[0] > $largest_page_count_per_post_type)? $page_count[0]:$largest_page_count_per_post_type;
		    $found_posts += $page_count[0];

		    // set up post order index
		    $post_order_index[$page_count[1]] = explode(',', $page_count[2]);

		    // get last post id for each type
		    $last_post_id_by_type[$page_count[1]] = (int) end($post_order_index[$page_count[1]]);

		}

then down the page,

// put the post in the order of the post_types array
		if ($loop->have_posts()) {

		    // make posts index by post_type
		    $tmpPosts = [];
            foreach ($loop->posts as $k => $v) {
                $tmpPosts[get_post_type( $v->ID )][$v->ID] = $v;
            }

            // assemble new ordered posts
            $finPosts = [];
            foreach ($post_types as $k => $v) {
                if (isset($tmpPosts[$v])) {
                    // reorder based on the sql result order
                    foreach ($post_order_index[$v] as $k1 => $v1) {
                        if (isset($tmpPosts[$v][$v1])) {
                            $finPosts[] = $tmpPosts[$v][$v1];
                        }
                    }
                }
            }

            // update the $loop with the new ordered posts
            $loop->posts = $finPosts;
		}

“The last post is always for the very last post, regardless of the page.”

I thought that was what you wanted, but i think you want the last post of each type for the current page.

Remove the $last_post_id_by_type from the $page_count_per_post_type foreach

		    // get last post id for each type
		    $last_post_id_by_type[$page_count[1]] = (int) end($post_order_index[$page_count[1]]);		

Update the “// assemble new ordered posts” code from above

 // assemble new ordered posts
            $finPosts = [];
            foreach ($post_types as $k => $v) {
                if (isset($tmpPosts[$v])) {

                    // reorder based on the sql result order
                    foreach ($post_order_index[$v] as $k1 => $v1) {
                        if (isset($tmpPosts[$v][$v1])) {
                            $finPosts[] = $tmpPosts[$v][$v1];

                            // set last post id for each type
		            $last_post_id_by_type[$v] = (int) $v1;
                        }
                    }
                }
            }

The last_post_id_by_type[post_type] should now contain the last post id from the current page.

• This reply was modified 7 years, 3 months ago by vrandom. Reason: typo-o