Posts Per Page -> Per Post Type

  • Ainsley Clark

    (@ainsleyclark)


    6 years ago

    Hi all,
    Sorry if this has been covered, been googling for a while.
    I have a query that contains 3 custom post types (‘news’, ‘exhibitors’, ‘speakers’).
    I would like to display a total of 6 posts per page and 2 custom post types each, so two news, two exhibitors and two speakers.
    Currently if I specify ‘posts_per_page’ => 6′ and there is only 1 news article, 1, exhibitor there will be 4 speakers shown.
    I almost need a ‘posts_per_post_type’ if that makes sense.
    Query below, many thanks in advance.

    $args = array( 'post_type' => array('news', 'exhibitors', 'speakers'), 'posts_per_page' => 6, 'order' => 'DESC', 'orderby' => 'type',  'paged' => $paged, 'tax_query' => array(
                        array(
                            'taxonomy' => 'foebar',
                            'field' => 'slug',
                            'terms' => array( $post_slug ) 
                        )
                    ));
Viewing 15 replies - 1 through 15 (of 62 total)
  • vrandom

    (@vrandom)

    I dont think what you want can be done in a single query.

    I see two possible ways depending on how you need the results.

    If you are using get_posts to run the query, you could run a query for each post_type, limit post_per_page to 2, then merge the three result arrays into one array and use that to display the posts.

    If you are using WP_Query to run the query, similar to the first option, run a query for each post_type, limit post_per_page to 2, retain a copy of the post ids. Next write a make the query for the WP_Query use post__in using the retained post ids.

    Hope that helps.

    Thread Starter Ainsley Clark

    (@ainsleyclark)

    Hi @vrandom
    Thank you very much for your quick reply.
    Currently I am using WP Query to list the posts, I originally had these as different query’s but I thought I couldn’t use pagination for the page?
    How would I display 2 posts of each custom post type using pagination as well? Code is below, thank you again.

    <?php 
	$post_slug=$post->post_name;
	$args = array( 'post_type' => array('news', 'exhibitors', 'speakers'), 'posts_per_page' => 2, 'order' => 'DESC', 'orderby' => 'type',  'paged' => $paged, 'tax_query' => array(
	    array(
	        'taxonomy' => 'foebar',
	        'field' => 'slug',
	        'terms' => array( $post_slug ) 
	    )
	));
	$loop = new WP_Query( $args );
	$speakerCounter = 0;
	$exhibitorCounter = 0;
	$columnwidth = 'col-lg-6';
	if ( have_posts() ) : 
	    while ( $loop->have_posts() ) : $loop->the_post(); 
	        $post_type = get_post_type( $post->ID );
	        if ($post_type == 'news') {
	            require( locate_template ('blocks/content-newsrow.php'));
	        }
	        if ($post_type == 'exhibitors') {
	            if ($exhibitorCounter == 0) {echo '<div class="row">';}
	                $exhibitorCounter++;
	                echo $exhibitorCounter;
	                require( locate_template ('blocks/content-exhibitor.php'));
	            if ($exhibitorCounter == 1) {echo '</div>';}
	        }
	        if ($post_type == 'speakers') { 
	            if ($speakerCounter == 0) {echo '<div class="row">';}
	                $speakerCounter++;
	                require( locate_template ('blocks/content-speaker.php'));
	            if ($speakerCounter == 1) {echo '</div>';}
	        } 
	    endwhile;
	else :
	    echo '<h3>No News</h3>';
	endif; 
	wp_reset_postdata(); 
	?>
    vrandom

    (@vrandom)

    Ok, that is a twist (pagination).

    I’ve been running a few tests.

    I tried my ideas grouping by years instead of post_types. I don’t have custom post types in my dev site. So i tried the same idea, get 2 posts for each year from a list of years.

    The paging is the problem. Each subquery has different paging results and if i extracted the ids from the subquerys then did a final query to only including those ids – the result is not 2 per year (aka post_type)

    I don’t think my original ideas will work.

    I think you are going to need a very custom query to accomplish this. To test if i could get the database to run the query. I wrote a query that would return a paged set of 2 posts per year for posts in my db.

    
SELECT wp_posts.id, wp_posts.post_date
FROM   wp_posts INNER JOIN (
  SELECT   GROUP_CONCAT(ID ORDER BY post_date DESC) grouped_id, year(post_date) as group_year
  FROM     wp_posts
  GROUP BY year(post_date)) group_max
  ON year(wp_posts.post_date) = group_max.group_year
     AND FIND_IN_SET(wp_posts.id, grouped_id) <=2
ORDER BY
  wp_posts.id, post_date DESC
LIMIT 18446744073709551610 OFFSET 0;

    limit is required so it is the max possible value, its not used as we are already limiting the result by the find_in_set option, but the offset would be the paging.

    I think this can be modify to fetch based on the post_type. This would allow you to do pagination as it would return the needed information for the pagination to work.

    Something like,

    
$sql_result = $wpdb->get_results( $sql, OBJECT);
$sql_posts_total = $wpdb->get_var( "SELECT FOUND_ROWS();" );
$max_num_pages = ceil($sql_posts_total / $post_per_page);

    Then the $max_num_pages could be used in the previous_posts_link and next_posts_link functions to make the pagination links.

    I have to go out of the house for a few hours, but i wanted to pass along where i was at with the issue.

    I will pick up where i am leave off at when i get back.

    Thread Starter Ainsley Clark

    (@ainsleyclark)

    Hi @vrandom

    Thanks again for your reply, I cant say that I am proficient with SQL so I only half got your reply.

    I tried to use a counter for each post type, but I’m getting some funky results, some appearing on different pages etc.

    Would be very appricaitie if you can help me out! Many thanks.

                    <?php 
                    $post_slug=$post->post_name;
                    $args = array( 'post_type' => array('news', 'exhibitors', 'speakers'), 'posts_per_page' => 6, 'order' => 'DESC', 'orderby' => 'type',  'paged' => $currpage, 'tax_query' => array(
                        array(
                            'taxonomy' => 'foebar',
                            'field' => 'slug',
                            'terms' => array( $post_slug ) 
                        )
                    ));
                    $loop = new WP_Query( $args );
                    $speakerCounter = 0;
                    $exhibitorCounter = 0;
                    $newsCounter = 0;
                    $columnwidth = 'col-lg-6';
                    if ( have_posts() ) : 
                        while ( $loop->have_posts() ) : $loop->the_post(); 
                            $post_type = get_post_type( $post->ID );
                            if ($post_type == 'news' && $newsCounter < 2) {
                                $newsCounter++;
                                require( locate_template ('blocks/content-newsrow.php'));
                            }
                            if ($post_type == 'exhibitors' && $exhibitorCounter < 2) {
                                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'));
                                if ($exhibitorCounter == 2) {echo '</div>';}
                            }
                            if ($post_type == 'speakers' && $speakerCounter < 2) { 
                                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'));
                                if ($speakerCounter == 1) {echo '</div>';}
                            } 
                        endwhile;
                    else :
                        echo '<h3>No News</h3>';
                    endif; 
                wp_reset_postdata(); 
                ?>
            </div>
            <div class="col-4">
                <?php get_sidebar(); ?>
            </div>
            <div class="col-12 pagination">
                <?php   echo previous_posts_link( 'Previous Page', $loop->max_num_pages);
                        echo get_next_posts_link( 'Next Page', $loop->max_num_pages );
                ?>
            </div>
        </div><!-- End Row -->
    vrandom

    (@vrandom)

    Ok, I think I have come up with a solution.

    I had to do a few things differently. I created my own WP_Query class (extending WP_Query) so that I could execute a sql statement directly. I did this because I was having such a pain trying to get everything to work with a wpdb->get_resutls query.

    The second thing I had to do was copy the “paginate_links” function, as the original would generate links based on the global wp_query not my $loop query. Not sure who thought people wouldn’t want links from WP_Query classes other than the global one. Anyways, it’s a butt load of dupe code which is annoying, but it serves its purpose.

    To get the pagination to work, the “found_posts” variable in the WP_Query class has to be set. This normally runs a “SELECT FOUND_ROWS()” against the sql, but we need a different solution as we are limiting the number of final results by the $post_per_posttype var. So we need to run the query twice, once to with the max possible $post_per_posttype setting to get the found_posts for the query. Mostly to get pagination to work we need to pass posts_per_page and found_posts as args to the custom WP_Query.

    After that we pass the final sql to the custom WP_Query class and we get back the WP_Query object which allow for all the special functions to work ($loop->have_posts(), $loop->the_post(), etc)

    I am assuming these template includes use the global $post var.

    require( locate_template (‘blocks/content-newsrow.php’));
    require( locate_template (‘blocks/content-exhibitor.php’));
    require( locate_template (‘blocks/content-speaker.php’));

    So they should work as is.

    I had a lot of fun figuring out the puzzle. ??

    Ok, the code.

    
<?php

      class WP_Query_CustomSQL extends WP_Query
      {

         public function __construct( $sql = '', $query = [] ) {
            if ( ! empty( $sql ) ) {
               $this->query( $sql , $query );
            }
         }

         public function query( $sql, $query = [] ) {
            $this->init();
            $this->query = $this->query_vars = wp_parse_args( $query );
            $this->request = $sql;
            return $this->get_posts();
         }

         function get_posts()
         {
            global $wpdb;

            $q = &$this->query_vars;

            $post_type = $q['post_type'];
            $this->posts = null;

            $this->posts = $wpdb->get_results($this->request);

            $this->found_posts = $q['found_posts'];

                $this->max_num_pages = ceil( $this->found_posts / $q['posts_per_page'] );

            // Convert to WP_Post objects.
            if ($this->posts) {
               $this->posts = array_map('get_post', $this->posts);
            }

            if ( $this->posts ) {
               $this->post_count = count( $this->posts );

               $this->posts = array_map( 'get_post', $this->posts );

               if ( $q['cache_results'] )
                  update_post_caches($this->posts, $post_type, $q['update_post_term_cache'], $q['update_post_meta_cache']);

               $this->post = reset( $this->posts );
            } else {
               $this->post_count = 0;
               $this->posts = array();
            }

            return $this->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, // https://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'  => '',
         );

         $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;
         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;
      }
        // ----------------------------------------------------

      $post_slug=$post->post_name;

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

        // to query
      $sql ="SELECT wp_posts.*
         FROM   wp_posts INNER JOIN (
           SELECT GROUP_CONCAT(ID ORDER BY post_date DESC) grouped_id, post_type
           FROM wp_posts
            inner join wp_term_relationships on wp_term_relationships.object_id = wp_posts.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 ('news', 'exhibitors', 'speakers')
           GROUP BY post_type
           ) group_max
           ON wp_posts.post_type = group_max.post_type
             AND FIND_IN_SET(wp_posts.id, grouped_id) <=#post_per_posttype#
         ORDER BY
           wp_posts.id, post_type DESC
         LIMIT 18446744073709551610 OFFSET 0;";

      global $wpdb;
      // need to pass the max posts possible to the query as it would not be generated correctly
      $found_posts = $wpdb->query(str_replace('#post_per_posttype#', 18446744073709551610, $sql));

      // page_per_posts is for pagination (post_per_posttype * num_of_posttypes_in_query)
      $loop = new WP_Query_CustomSQL(str_replace('#post_per_posttype#', $post_per_posttype, $sql), array( 'posts_per_page' => $post_per_posttype * 3 , 'found_posts' => $found_posts));

      $speakerCounter = 0;
      $exhibitorCounter = 0;
      $columnwidth = 'col-lg-6';
      if ( $loop->have_posts() ) :
         while ( $loop->have_posts() ) : $loop->the_post();
            $post_type = get_post_type( $post->ID );
            if ($post_type == 'news' && $newsCounter < 2) {
               $newsCounter++;
               require( locate_template ('blocks/content-newsrow.php'));
               //echo $post_type . ' - '  .  get_permalink($post->ID)  . "<BR>";
           }
           if ($post_type == 'exhibitors' && $exhibitorCounter < 2) {
               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_type . ' - '  .  get_permalink($post->ID)  . "<BR>";
               if ($exhibitorCounter == 2) {echo '</div>';}
           }
           if ($post_type == 'speakers' && $speakerCounter < 2) {
               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_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'
         );

         // 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();

?>
    Thread Starter Ainsley Clark

    (@ainsleyclark)

    Hi @vrandom

    Wow, thank you VERY much for all of this, you have certainly gone to a lot of trouble to look at this for me.
    I defintley wouldnt have the knowledge to come up with something like this.

    I have pasted your extended class in my functions.php and popped the sql code in my page-news.php.
    After changing the table prefixes within the sql query, I am faced with this error:

    Notice
    : Undefined index: post_type in
    /wordpress/localhost/website/wp-content/themes/website_theme/functions.php
    on line
    1131

    Not sure where to go from here, thanks again.

    // ----------------------------------------------------

$post_slug=$post->post_name;

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

// to query
$sql ="SELECT foe_342fj29x2_posts.*
 FROM   foe_342fj29x2_posts INNER JOIN (
   SELECT GROUP_CONCAT(ID ORDER BY post_date DESC) grouped_id, post_type
   FROM foe_342fj29x2_posts
    inner join foe_342fj29x2_term_relationships on foe_342fj29x2_term_relationships.object_id = foe_342fj29x2_posts.id
    inner join foe_342fj29x2_term_taxonomy on foe_342fj29x2_term_relationships.term_taxonomy_id = foe_342fj29x2_term_taxonomy.term_taxonomy_id
    inner join foe_342fj29x2_terms on foe_342fj29x2_term_taxonomy.term_id = foe_342fj29x2_terms.term_id
    where foe_342fj29x2_term_taxonomy.taxonomy = 'category' and foe_342fj29x2_terms.slug = '$post_slug'
    and post_type in ('news', 'exhibitors', 'speakers')
   GROUP BY post_type
   ) group_max
   ON foe_342fj29x2_posts.post_type = group_max.post_type
     AND FIND_IN_SET(foe_342fj29x2_posts.id, grouped_id) <=#post_per_posttype#
 ORDER BY
 foe_342fj29x2_posts.id, post_type DESC
 LIMIT 18446744073709551610 OFFSET 0;";

global $wpdb;
// need to pass the max posts possible to the query as it would not be generated correctly
$found_posts = $wpdb->query(str_replace('#post_per_posttype#', 18446744073709551610, $sql));

// page_per_posts is for pagination (post_per_posttype * num_of_posttypes_in_query)
$loop = new WP_Query_CustomSQL(str_replace('#post_per_posttype#', $post_per_posttype, $sql), array( 'posts_per_page' => $post_per_posttype * 3 , 'found_posts' => $found_posts));

$speakerCounter = 0;
$exhibitorCounter = 0;
$columnwidth = 'col-lg-6';

if ( $loop->have_posts() ) :
 while ( $loop->have_posts() ) : $loop->the_post();
    $post_type = get_post_type( $post->ID );
    if ($post_type == 'news' && $newsCounter < 2) {
       $newsCounter++;
       require( locate_template ('blocks/content-newsrow.php'));
       echo $post_type . ' - '  .  get_permalink($post->ID)  . "<BR>";
   }
   if ($post_type == 'exhibitors' && $exhibitorCounter < 2) {
       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_type . ' - '  .  get_permalink($post->ID)  . "<BR>";
       if ($exhibitorCounter == 2) {echo '</div>';}
   }
   if ($post_type == 'speakers' && $speakerCounter < 2) {
       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_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'
 );

 // 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();

?>
    Thread Starter Ainsley Clark

    (@ainsleyclark)

    Upon digging, when printing this $q variable, I get Array ( [posts_per_page] => 6 [found_posts] => 0 ).
    Presuming it has something to do with the found posts.

    vrandom

    (@vrandom)

    Ok, I have made a quite a few modifications.

    The query has been completely redesigned, as well as how i was calculating the paging to work.

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

    
<?php
	
		class WP_Query_CustomSQL extends WP_Query
		{

			public function __construct( $sql = '', $query = [] ) {
				if ( ! empty( $sql ) ) {
					$this->query( $sql , $query );
				}
			}

			public function query( $sql, $query = [] ) {
				$this->init();
				//$this->query = $this->query_vars = array();
				$this->query = $this->query_vars = wp_parse_args( $query );
				$this->request = $sql;
				return $this->get_posts();
			}

			function get_posts()
			{
				global $wpdb;

				$q = &$this->query_vars;

				$post_type = $q['post_type'];
				$this->posts = null;

				$this->posts = $wpdb->get_results($this->request);

				$this->found_posts = $q['found_posts'];

				if (isset($q['posts_per_page'])) {
				    $this->max_num_pages = $q['max_num_pages'];
				} else {
				    $this->max_num_pages = ceil( $this->found_posts / $q['posts_per_page'] );
				}

				// Convert to WP_Post objects.
				if ($this->posts) {
					$this->posts = array_map('get_post', $this->posts);
				}

				if ( $this->posts ) {
					$this->post_count = count( $this->posts );

					$this->posts = array_map( 'get_post', $this->posts );

					if ( $q['cache_results'] )
						update_post_caches($this->posts, $post_type, $q['update_post_term_cache'], $q['update_post_meta_cache']);

					$this->post = reset( $this->posts );
				} else {
					$this->post_count = 0;
					$this->posts = array();
				}

				return $this->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, // https://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'  => '',
			);

			$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;
			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;
		}
        // ----------------------------------------------------

		$post_slug='news'; //$post->post_name;

        $post_types = ['post1', 'post2', 'post3'];

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

        $sql_offset = get_query_var('paged', 0);
        if ($sql_offset -1 > 0) {
            $sql_offset = ($sql_offset - 1) * $post_per_posttype;
        }

		// Make SQL Parts
        $joinTemplate = [];
        $whereTemplate = [];
        $whereTemplateAddon = [];

		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
                       LIMIT 18446744073709551610 offset #sql_offset#
                      ) d$post_type_key
                     GROUP BY d$post_type_key.post_type) post_type_$post_type_key		 
                     ON primary_wp_posts.post_type = post_type_$post_type_key.post_type";

		    $whereTemplate[] = "primary_wp_posts.post_type = post_type_$post_type_key.post_type";

		    $whereTemplateAddon[] = "AND (FIND_IN_SET(primary_wp_posts.id, post_type_$post_type_key.grouped_id) <= $post_per_posttype and FIND_IN_SET(primary_wp_posts.id, post_type_$post_type_key.grouped_id) > 0)";

		}

		$sql_template = "select #sql_col# from wp_posts primary_wp_posts
			    #join_templates_0#
			    #join_templates_1#
			    #join_templates_2#
                where
                  ((#where_0# #where_addon_0#)
			    or
 			      (#where_1# #where_addon_1#)
			    or
 			      (#where_2# #where_addon_2#))
 			      #append#";

        // Assemble Queries
		$sqlQuerys['found_post_query'] = $sql_template;
		$sqlQuerys['wp_query'] = $sql_template;

		$found_posts_sql = $sql_template;
		foreach ($post_types as $post_type_key => $post_type) {

		    $sqlQuerys['found_post_query'] = str_replace("#sql_col#", 'count(primary_wp_posts.id)', $sqlQuerys['found_post_query']);
		    $sqlQuerys['found_post_query'] = str_replace("#append#", 'group by primary_wp_posts.post_type', $sqlQuerys['found_post_query']);
		    $sqlQuerys['found_post_query'] = str_replace("#where_addon_$post_type_key#", '', $sqlQuerys['found_post_query']);
		    $sqlQuerys['found_post_query'] = str_replace("#join_templates_$post_type_key#", str_replace("#sql_offset#", 0,  $joinTemplate[$post_type_key]), $sqlQuerys['found_post_query']);
		    $sqlQuerys['found_post_query'] = str_replace("#where_$post_type_key#", $whereTemplate[$post_type_key], $sqlQuerys['found_post_query']);

		    $sqlQuerys['wp_query'] = str_replace("#sql_col#", '*', $sqlQuerys['wp_query']);
		    $sqlQuerys['wp_query'] = str_replace("#append#", "", $sqlQuerys['wp_query']);
		    $sqlQuerys['wp_query'] = str_replace("#where_addon_$post_type_key#", $whereTemplateAddon[$post_type_key], $sqlQuerys['wp_query']);
		    $sqlQuerys['wp_query'] = str_replace("#join_templates_$post_type_key#", str_replace("#sql_offset#", $sql_offset,  $joinTemplate[$post_type_key]), $sqlQuerys['wp_query']);
		    $sqlQuerys['wp_query'] = str_replace("#where_$post_type_key#", $whereTemplate[$post_type_key], $sqlQuerys['wp_query']);
		}
        //
		global $wpdb;
		// need to pass the max posts possible to the query as it would not be generated correctly
		$page_count_per_post_type = $wpdb->get_results($sqlQuerys['found_post_query'], ARRAY_N);

		// get the largest page count on a page
		$largest_page_count_per_post_type = 0;
		$found_posts = 0;
		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];
		}

		// page_per_posts is for pagination (post_per_posttype * num_of_posttypes_in_query)
		$loop = new WP_Query_CustomSQL($sqlQuerys['wp_query'], array( 'posts_per_page' => $post_per_posttype * 3 , 'found_posts' => $found_posts, 'max_num_pages' => ceil( $largest_page_count_per_post_type / $post_per_posttype  )));

		// 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;
            }

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

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

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

				 if ($post_type == 'post1' && $newsCounter < 2) {
                    $newsCounter++;
                    //require( locate_template ('blocks/content-newsrow.php'));
                    echo $post->ID . '] ' . $post_type . ' - '  .  get_permalink($post->ID)  . "<BR>";
                }
                if ($post_type == 'post2' && $exhibitorCounter < 2) {
                    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 < 2) {
                    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'
			);

			// 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();

?>
    Thread Starter Ainsley Clark

    (@ainsleyclark)

    Hi @vrandom

    Thanks once again for your reply.

    I’m still getting the same error. Notice: Undefined index: post_type …

    I have copied the class into functions.php and the other section into my page where I want to display the posts.
    I have changed all the wp_ to my table prefix, including primary_wp, think this is right?
    Also changed my post types in the array. I don’t know if I am missing something.
    You should definitely make this into a plugin when it gets going!

    Code attached: 

     // ----------------------------------------------------

 $post_slug='news'; //$post->post_name;

 $post_types = ['news', 'speakers', 'exhibitors'];

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

 $sql_offset = get_query_var('paged', 0);
 if ($sql_offset -1 > 0) {
     $sql_offset = ($sql_offset - 1) * $post_per_posttype;
 }

 // Make SQL Parts
 $joinTemplate = [];
 $whereTemplate = [];
 $whereTemplateAddon = [];

 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 foe_342fj29x2_posts 
                 inner join foe_342fj29x2_term_relationships on foe_342fj29x2_term_relationships.object_id = id
                 inner join foe_342fj29x2_term_taxonomy on foe_342fj29x2_term_relationships.term_taxonomy_id = foe_342fj29x2_term_taxonomy.term_taxonomy_id
                 inner join foe_342fj29x2_terms on foe_342fj29x2_term_taxonomy.term_id = foe_342fj29x2_terms.term_id
                 where foe_342fj29x2_term_taxonomy.taxonomy = 'category' and foe_342fj29x2_terms.slug = '$post_slug'
                and post_type in ('$post_type')
                and post_status = 'publish'
                order by post_date desc, id desc
                LIMIT 18446744073709551610 offset #sql_offset#
               ) d$post_type_key
              GROUP BY d$post_type_key.post_type) post_type_$post_type_key		 
              ON primary_foe_342fj29x2_posts.post_type = post_type_$post_type_key.post_type";

     $whereTemplate[] = "primary_foe_342fj29x2_posts.post_type = post_type_$post_type_key.post_type";

     $whereTemplateAddon[] = "AND (FIND_IN_SET(primary_foe_342fj29x2_posts.id, post_type_$post_type_key.grouped_id) <= $post_per_posttype and FIND_IN_SET(primary_foe_342fj29x2_posts.id, post_type_$post_type_key.grouped_id) > 0)";

 }

 $sql_template = "select #sql_col# from foe_342fj29x2_posts primary_foe_342fj29x2_posts
         #join_templates_0#
         #join_templates_1#
         #join_templates_2#
         where
           ((#where_0# #where_addon_0#)
         or
            (#where_1# #where_addon_1#)
         or
            (#where_2# #where_addon_2#))
            #append#";

 // Assemble Queries
 $sqlQuerys['found_post_query'] = $sql_template;
 $sqlQuerys['wp_query'] = $sql_template;

 $found_posts_sql = $sql_template;
 foreach ($post_types as $post_type_key => $post_type) {

     $sqlQuerys['found_post_query'] = str_replace("#sql_col#", 'count(primary_foe_342fj29x2_posts.id)', $sqlQuerys['found_post_query']);
     $sqlQuerys['found_post_query'] = str_replace("#append#", 'group by primary_foe_342fj29x2_posts.post_type', $sqlQuerys['found_post_query']);
     $sqlQuerys['found_post_query'] = str_replace("#where_addon_$post_type_key#", '', $sqlQuerys['found_post_query']);
     $sqlQuerys['found_post_query'] = str_replace("#join_templates_$post_type_key#", str_replace("#sql_offset#", 0,  $joinTemplate[$post_type_key]), $sqlQuerys['found_post_query']);
     $sqlQuerys['found_post_query'] = str_replace("#where_$post_type_key#", $whereTemplate[$post_type_key], $sqlQuerys['found_post_query']);

     $sqlQuerys['wp_query'] = str_replace("#sql_col#", '*', $sqlQuerys['wp_query']);
     $sqlQuerys['wp_query'] = str_replace("#append#", "", $sqlQuerys['wp_query']);
     $sqlQuerys['wp_query'] = str_replace("#where_addon_$post_type_key#", $whereTemplateAddon[$post_type_key], $sqlQuerys['wp_query']);
     $sqlQuerys['wp_query'] = str_replace("#join_templates_$post_type_key#", str_replace("#sql_offset#", $sql_offset,  $joinTemplate[$post_type_key]), $sqlQuerys['wp_query']);
     $sqlQuerys['wp_query'] = str_replace("#where_$post_type_key#", $whereTemplate[$post_type_key], $sqlQuerys['wp_query']);
 }
 //
 global $wpdb;
 // need to pass the max posts possible to the query as it would not be generated correctly
 $page_count_per_post_type = $wpdb->get_results($sqlQuerys['found_post_query'], ARRAY_N);

 // get the largest page count on a page
 $largest_page_count_per_post_type = 0;
 $found_posts = 0;
 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];
 }

 // page_per_posts is for pagination (post_per_posttype * num_of_posttypes_in_query)
 $loop = new WP_Query_CustomSQL($sqlQuerys['wp_query'], array( 'posts_per_page' => $post_per_posttype * 3 , 'found_posts' => $found_posts, 'max_num_pages' => ceil( $largest_page_count_per_post_type / $post_per_posttype  )));

 // 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;
     }

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

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

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

          if ($post_type == 'post1' && $newsCounter < 2) {
             $newsCounter++;
             //require( locate_template ('blocks/content-newsrow.php'));
             echo $post->ID . '] ' . $post_type . ' - '  .  get_permalink($post->ID)  . "<BR>";
         }
         if ($post_type == 'post2' && $exhibitorCounter < 2) {
             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 < 2) {
             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'
     );

     // 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(); ?>
    vrandom

    (@vrandom)

    Humm, I only found one place where the phrase “post_type” is used as a index key. Not sure why it doesn’t give an error on my dev site though.

    In the WP_Query_CustomSQL class, get_post function, 

    
$q = &$this->query_vars;

$post_type = $q['post_type'];

    change that to

    
$q = &$this->query_vars;

$post_type = '';
if (isset($q['post_type'])) {
    $post_type = $q['post_type'];
}
    Thread Starter Ainsley Clark

    (@ainsleyclark)

    Hi @vrandom

    Still no luck, the code above successfully got rid of the error, but there are no posts being put in so its not outputting anything.

    Currently the query is for a custom taxonomy, Im adding this code to a page template for all of the taxonomy pages.
    Im not sure if this is already in place I’m also not sure what should be in the post_slug variable at the top of the query. Maybe its not working because of that? I cant be sure.

    Would be great if this could get working.

    Many thanks again.

    
$post_slug=$post->post_name;

$post_types = ['news', 'speakers', 'exhibitors'];

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

$sql_offset = get_query_var('paged', 0);
if ($sql_offset -1 > 0) {
    $sql_offset = ($sql_offset - 1) * $post_per_posttype;
}

// Make SQL Parts
$joinTemplate = [];
$whereTemplate = [];
$whereTemplateAddon = [];

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 foe_342fj29x2_posts 
                inner join foe_342fj29x2_term_relationships on foe_342fj29x2_term_relationships.object_id = id
                inner join foe_342fj29x2_term_taxonomy on foe_342fj29x2_term_relationships.term_taxonomy_id = foe_342fj29x2_term_taxonomy.term_taxonomy_id
                inner join foe_342fj29x2_terms on foe_342fj29x2_term_taxonomy.term_id = foe_342fj29x2_terms.term_id
                where foe_342fj29x2_term_taxonomy.taxonomy = 'category' and foe_342fj29x2_terms.slug = '$post_slug'
            and post_type in ('$post_type')
            and post_status = 'publish'
            order by post_date desc, id desc
            LIMIT 18446744073709551610 offset #sql_offset#
            ) d$post_type_key
            GROUP BY d$post_type_key.post_type) post_type_$post_type_key		 
            ON primary_foe_342fj29x2_posts.post_type = post_type_$post_type_key.post_type";

    $whereTemplate[] = "primary_foe_342fj29x2_posts.post_type = post_type_$post_type_key.post_type";

    $whereTemplateAddon[] = "AND (FIND_IN_SET(primary_foe_342fj29x2_posts.id, post_type_$post_type_key.grouped_id) <= $post_per_posttype and FIND_IN_SET(primary_foe_342fj29x2_posts.id, post_type_$post_type_key.grouped_id) > 0)";

}

$sql_template = "select #sql_col# from foe_342fj29x2_posts primary_foe_342fj29x2_posts
        #join_templates_0#
        #join_templates_1#
        #join_templates_2#
        where
        ((#where_0# #where_addon_0#)
        or
        (#where_1# #where_addon_1#)
        or
        (#where_2# #where_addon_2#))
        #append#";

// Assemble Queries
$sqlQuerys['found_post_query'] = $sql_template;
$sqlQuerys['wp_query'] = $sql_template;

$found_posts_sql = $sql_template;
foreach ($post_types as $post_type_key => $post_type) {

    $sqlQuerys['found_post_query'] = str_replace("#sql_col#", 'count(primary_foe_342fj29x2_posts.id)', $sqlQuerys['found_post_query']);
    $sqlQuerys['found_post_query'] = str_replace("#append#", 'group by primary_foe_342fj29x2_posts.post_type', $sqlQuerys['found_post_query']);
    $sqlQuerys['found_post_query'] = str_replace("#where_addon_$post_type_key#", '', $sqlQuerys['found_post_query']);
    $sqlQuerys['found_post_query'] = str_replace("#join_templates_$post_type_key#", str_replace("#sql_offset#", 0,  $joinTemplate[$post_type_key]), $sqlQuerys['found_post_query']);
    $sqlQuerys['found_post_query'] = str_replace("#where_$post_type_key#", $whereTemplate[$post_type_key], $sqlQuerys['found_post_query']);

    $sqlQuerys['wp_query'] = str_replace("#sql_col#", '*', $sqlQuerys['wp_query']);
    $sqlQuerys['wp_query'] = str_replace("#append#", "", $sqlQuerys['wp_query']);
    $sqlQuerys['wp_query'] = str_replace("#where_addon_$post_type_key#", $whereTemplateAddon[$post_type_key], $sqlQuerys['wp_query']);
    $sqlQuerys['wp_query'] = str_replace("#join_templates_$post_type_key#", str_replace("#sql_offset#", $sql_offset,  $joinTemplate[$post_type_key]), $sqlQuerys['wp_query']);
    $sqlQuerys['wp_query'] = str_replace("#where_$post_type_key#", $whereTemplate[$post_type_key], $sqlQuerys['wp_query']);
}
//
global $wpdb;
// need to pass the max posts possible to the query as it would not be generated correctly
$page_count_per_post_type = $wpdb->get_results($sqlQuerys['found_post_query'], ARRAY_N);

 // get the largest page count on a page
$largest_page_count_per_post_type = 0;
$found_posts = 0;
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];
}

// page_per_posts is for pagination (post_per_posttype * num_of_posttypes_in_query)
$loop = new WP_Query_CustomSQL($sqlQuerys['wp_query'], array( 'posts_per_page' => $post_per_posttype * 3 , 'found_posts' => $found_posts, 'max_num_pages' => ceil( $largest_page_count_per_post_type / $post_per_posttype  )));

// 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;
    }

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

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

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

          if ($post_type == 'news' && $newsCounter < 2) {
             $newsCounter++;
             require( locate_template ('blocks/content-newsrow.php'));
             
             echo $post->ID . '] ' . $post_type . ' - '  .  get_permalink($post->ID)  . "<BR>";
         }
         if ($post_type == 'exhibitors') {
             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 == 'speakers') {
             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'
     );

     // 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 ss</h3>';
 endif;
 wp_reset_postdata(); ?>
business-leaders
    vrandom

    (@vrandom)

    This is the original code where the $post_slug was used.

    
$post_slug=$post->post_name;
$args = array( 'post_type' => array('news', 'exhibitors', 'speakers'), 'posts_per_page' => 2, 'order' => 'DESC', 'orderby' => 'type',  'paged' => $paged, 'tax_query' => array(
	    array(
	        'taxonomy' => 'foebar',
	        'field' => 'slug',
	        'terms' => array( $post_slug ) 
	    )
	));

    It sounds odd to me that a $post->post_name would be also used for a taxonomy slug.

    It is what i based the final sql off of, but i didnt have a taxonomy called foebar so i used category. I’m assuming that you dont have a actuall taxonomy called foebar.

    Do you need the tax_query portion?

    Thread Starter Ainsley Clark

    (@ainsleyclark)

    Hi @vrandom,

    I must have been using the $post_slug to return the taxonomy, the post slug has the same url as the taxonomy slug. The taxonomy is called foebar, and each has a page and Im using this one page template for all 5 of them, the idea is that each page will pull in speakers, exhibitors and news that are tagged with the particular taxonomy.

    In the SQL query I changed ‘category’ to the post slug but still yielding no results. Is there a way to test what the sql query is spitting out?

    Not 100% sure about the tax_query portion?

    Thanks so much.

    vrandom

    (@vrandom)

    So you created a custom taxonomy type called foobar and have attached that type to each custom posttype (‘news’, ‘exhibitors’, ‘speakers’).

    Ok put this right below the $post_slug=$post->post_name line. It should dump a single record from the database using the ‘news’ post type, a custom taxonomy named ‘foobar’ with a slug of the $page_slug value.

    
global $wpdb;

$sql = "SELECT id, post_type, post_date
FROM foe_342fj29x2_posts 
inner join foe_342fj29x2_term_relationships on foe_342fj29x2_term_relationships.object_id = id
inner join foe_342fj29x2_term_taxonomy on foe_342fj29x2_term_relationships.term_taxonomy_id = foe_342fj29x2_term_taxonomy.term_taxonomy_id
inner join foe_342fj29x2_terms on foe_342fj29x2_term_taxonomy.term_id = foe_342fj29x2_terms.term_id
where 
foe_342fj29x2_term_taxonomy.taxonomy = 'foobar' and foe_342fj29x2_terms.slug = '$post_slug'
and post_type in ('news')
and post_status = 'publish'
order by post_date desc, id desc
limit 1 offset 0";
		
$results = $wpdb->get_results($sql);

echo "<pre>";
var_dump($results);
echo "</pre>";
    Thread Starter Ainsley Clark

    (@ainsleyclark)

    Hi @vrandom

    Got it echoing this:

    array(1) {
    [0]=>
    object(stdClass)#6075 (3) {
    [“id”]=>
    string(3) “395”
    [“post_type”]=>
    string(4) “news”
    [“post_date”]=>
    string(19) “2019-02-24 09:36:45”
    }
    }

Viewing 15 replies - 1 through 15 (of 62 total)
  • The topic ‘Posts Per Page -> Per Post Type’ is closed to new replies.