Support » Developing with WordPress » Posts Per Page -> Per Post Type

  • 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 ) 
                            )
                        ));
    • This topic was modified 3 years, 4 months ago by Ainsley Clark.
Viewing 15 replies - 16 through 30 (of 62 total)
  • ok, that confirms the base query is pulling results. So lets update that in the code.

    If we expand out to the next step in the query. Try this query.

    
    $sql = "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 = '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 18446744073709551610 offset 0
                ) d0
                GROUP BY d0.post_type";
    

    ok, the above query should return a comma separated list of ids and the post_type. I don’t see anything odd in that query. Give it a try and confirm you get data.

    Then continue to the next step in the query build.

    
    $sql = "select * from foe_342fj29x2_posts primary_foe_342fj29x2_posts
    
    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 = '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 18446744073709551610 offset 0
                ) d0
                GROUP BY d0.post_type) post_type_0
                ON primary_foe_342fj29x2_posts.post_type = post_type_0.post_type
    
            where
    		((primary_foe_342fj29x2_posts.post_type = post_type_0.post_type AND (FIND_IN_SET(primary_foe_342fj29x2_posts.id, post_type_0.grouped_id) <= 2 and FIND_IN_SET(primary_foe_342fj29x2_posts.id, post_type_0.grouped_id) > 0))";"
    

    I see a few things that dont look right.

    In this query there are two main tables? foe_342fj29x2_posts primary_foe_342fj29x2_posts. The sub query in the join is using foe_342fj29x2_posts to pull a list of ids, and the ON part is joining that to the other table primary_foe_342fj29x2_posts.post_type. The where part of the query is all using primary_foe_342fj29x2_posts.

    So we are getting a comma separated list of post ids from foe_342fj29x2_posts, then trying to get the posts from both tables where the ids from primary_foe_342fj29x2_posts are in the comma separated list.

    I don’t see how this will work this way.

    So can you explain why you need data from the two tables, foe_342fj29x2_posts primary_foe_342fj29x2_posts?

    Thread Starter Ainsley Clark

    (@ainsleyclark)

    Hi @vrandom

    I got the data returning from the first query:

    array(1) {
    [0]=>
    object(stdClass)#6075 (2) {
    [“grouped_id”]=>
    string(15) “398,397,392,343”
    [“post_type”]=>
    string(4) “news”
    }
    }

    With regards to the primary_ as I changed my wordpress table prefix, I changed all the primary_wp to primary_foe_tableprefix etc. I dont know if this would have something to do with it:

    Are you able to tell me where your spotting: So can you explain why you need data from the two tables, foe_342fj29x2_posts primary_foe_342fj29x2_posts?

    $post_slug=$post->post_name;
    
    global $wpdb;
    
    $sql = "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 = 'foebar' and foe_342fj29x2_terms.slug = '$post_slug'
    				and post_type in ('news') and post_status = 'publish'
    				order by post_date desc, id desc
    				LIMIT 18446744073709551610 offset 0
                ) d0
                GROUP BY d0.post_type";
    		
    $results = $wpdb->get_results($sql);
    
    echo "<pre>";
    var_dump($results);
    echo "</pre>";
    
    $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 = '$post_slug' 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(); ?>
    • This reply was modified 3 years, 4 months ago by Ainsley Clark.
    Thread Starter Ainsley Clark

    (@ainsleyclark)

    Ran the second query and got:

    WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 19] will have a look now.

    Thread Starter Ainsley Clark

    (@ainsleyclark)

    Now on to this:
    WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 19]

      where
                ((primary_foe_342fj29x2_posts.post_type = post_type_0.post_type AND (FIND_IN_SET(primary_foe_342fj29x2_posts.id, post_type_0.grouped_id) <= 2 and FIND_IN_SET(primary_foe_342fj29x2_posts.id, post_type_0.grouped_id) > 0))";

    Apologies, my SQL is poor!

    • This reply was modified 3 years, 4 months ago by Ainsley Clark.
    Thread Starter Ainsley Clark

    (@ainsleyclark)

    @vrandom sorry for all the messages!

    Missing bracket was causing it. Now getting all the data dumped out, is it now a case of using this sql query for the jointemplate[]?

    Thread Starter Ainsley Clark

    (@ainsleyclark)

    Been fiddling for a while now, Im up to this:

    News for Business Leaders:

    Notice: Undefined index: cache_results in /Users/localhost/website/wp-content/themes/theme/functions.php on line 1160

    I can see the pagination now though, but no posts.

    Im using this query:

    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 = 'foebar' 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)";
    }
    • This reply was modified 3 years, 4 months ago by Ainsley Clark.

    Sorry was busy with other things and didn’t notice the replies.

    Sounds like your getting closer.

    The two table names are in the first line of this code.

    
    select #sql_col# from foe_342fj29x2_posts primary_foe_342fj29x2_posts
    

    I think it should be one or the other but not both.

    Or do you have two “posts” tables?

    Found here in the code.

    
    $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#";
    
    Thread Starter Ainsley Clark

    (@ainsleyclark)

    I dont have two post tables, your right! Dont know where that came from.

    Now this:

    WordPress database error: [Unknown column ‘primary_foe_342fj29x2_posts.id’ in ‘field list’]

    Think its coming from this line:

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

    WordPress database error: [Unknown column 'primary_foe_342fj29x2_posts.id' in 'field list']
    select count(primary_foe_342fj29x2_posts.id) from foe_342fj29x2_posts 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 = 'foebar' and foe_342fj29x2_terms.slug = 'marketing-experts' and post_type in ('news') and post_status = 'publish' order by post_date desc, id desc LIMIT 18446744073709551610 offset 0 ) d0 GROUP BY d0.post_type) post_type_0	ON primary_foe_342fj29x2_posts.post_type = post_type_0.post_type 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 = 'foebar' and foe_342fj29x2_terms.slug = 'marketing-experts' and post_type in ('speakers') and post_status = 'publish' order by post_date desc, id desc LIMIT 18446744073709551610 offset 0 ) d1 GROUP BY d1.post_type) post_type_1	ON primary_foe_342fj29x2_posts.post_type = post_type_1.post_type 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 = 'foebar' and foe_342fj29x2_terms.slug = 'marketing-experts' and post_type in ('exhibitors') and post_status = 'publish' order by post_date desc, id desc LIMIT 18446744073709551610 offset 0 ) d2 GROUP BY d2.post_type) post_type_2	ON primary_foe_342fj29x2_posts.post_type = post_type_2.post_type where ((primary_foe_342fj29x2_posts.post_type = post_type_0.post_type ) or (primary_foe_342fj29x2_posts.post_type = post_type_1.post_type ) or (primary_foe_342fj29x2_posts.post_type = post_type_2.post_type )) group by primary_foe_342fj29x2_posts.post_type
    Thread Starter Ainsley Clark

    (@ainsleyclark)

    And also WordPress database error: [Unknown column ‘primary_foe_342fj29x2_posts.post_type’ in ‘where clause’]

    Been through it a few times and the sql query thats spitting out results and dumping them:

    $sql = "select * from foe_342fj29x2_posts primary_foe_342fj29x2_posts
    
    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 = 'foebar' and foe_342fj29x2_terms.slug = '$post_slug'
    				and post_type in ('news') and post_status = 'publish'
    				order by post_date desc, id desc
    				LIMIT 18446744073709551610 offset 0
                ) d0
                GROUP BY d0.post_type) post_type_0
                ON primary_foe_342fj29x2_posts.post_type = post_type_0.post_type
    
                WHERE
                ((primary_foe_342fj29x2_posts.post_type = post_type_0.post_type AND (FIND_IN_SET(primary_foe_342fj29x2_posts.id, post_type_0.grouped_id) <= 2 and FIND_IN_SET(primary_foe_342fj29x2_posts.id, post_type_0.grouped_id) > 0)))";
    		
    $results = $wpdb->get_results($sql);
    
    echo "<pre>";
    var_dump($results);
    echo "</pre>";

    Is (I think the same as this) bar the variables.

    $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 = 'foebar' 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
            #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']);
    }

    ok, it looks like you updated the sqlTemplate to only use foe_342fj29x2_posts. So the where statements need to be updated in the $sql statements.

    for the first block of code,

    
    $sql = "select * from foe_342fj29x2_posts
    
    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 = 'foebar' and foe_342fj29x2_terms.slug = '$post_slug'
    				and post_type in ('news') and post_status = 'publish'
    				order by post_date desc, id desc
    				LIMIT 18446744073709551610 offset 0
                ) d0
                GROUP BY d0.post_type) post_type_0
                ON foe_342fj29x2_posts.post_type = post_type_0.post_type
    
                WHERE
                ((foe_342fj29x2_posts.post_type = post_type_0.post_type AND (FIND_IN_SET(foe_342fj29x2_posts.id, post_type_0.grouped_id) <= 2 and FIND_IN_SET(foe_342fj29x2_posts.id, post_type_0.grouped_id) > 0)))";
    		
    $results = $wpdb->get_results($sql);
    
    echo "<pre>";
    var_dump($results);
    echo "</pre>";
    

    in the second block of code, the $whereTemplate and $whereTemplateAddon need to be updated, replace primary_foe_342fj29x2_posts to use the foe_342fj29x2_posts. There is one in the join statement at the ON line, and then the two where’s.

    Thread Starter Ainsley Clark

    (@ainsleyclark)

    Hi @vrandom

    Just to be sure, the first block I mentioned in my last post was just for testing right?

    I have removed primary_ from everything, I cant be sure what is used for?
    Anyway getting this error, but can see the pagination again.

    Notice: Undefined index: cache_results in

    
    $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 = 'foebar' 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 foe_342fj29x2_posts.post_type = post_type_$post_type_key.post_type";
    
            $whereTemplate[] = "foe_342fj29x2_posts.post_type = post_type_$post_type_key.post_type";
    
            $whereTemplateAddon[] = "AND (FIND_IN_SET(foe_342fj29x2_posts.id, post_type_$post_type_key.grouped_id) <= $post_per_posttype and FIND_IN_SET(foe_342fj29x2_posts.id, post_type_$post_type_key.grouped_id) > 0)";
    }
    
    $sql_template = "select #sql_col# from 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(foe_342fj29x2_posts.id)', $sqlQuerys['found_post_query']);
        $sqlQuerys['found_post_query'] = str_replace("#append#", 'group by 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']);
    }
    //

    Yes, the first block was just to make sure the sql parts were working. You can delete it.

    Humm, the cache_results error. You mentioned that in a previous post.

    
    Notice: Undefined index: cache_results in /Users/localhost/website/wp-content/themes/theme/functions.php on line 1160
    

    What is on line 1160 in that file?

    Thread Starter Ainsley Clark

    (@ainsleyclark)

    Im getting the posts 🙂 My issue was with the conditionals within the loop.

    Is there a way to use isset so I can silence the error?

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

    ;

    Other thing is that the pagination thinks it has 35 pages when it only has 2.

    Amazing. Thank you so much.

    odd it is suppose to set a default value for that.

    Change it to this, should fix it

    
     if ( isset($q['cache_results']) && $q['cache_results'] )
    

    The paging is complicated. This is how i worked it out.

    assume the following:

    Post type: news, has 4 posts
    Post type: speakers, has 6 posts
    Post type: exhibitors, has 8 posts

    We are showing 2 per type on the page.

    I calculated the number of pages each would require. For news (its 2 pages), speakers (its 3 pages), and exhibitors (is 4 pages). I then overwrote the paginator to use highest needed page count (exhibitors at 3 pages) this allows all the posts in the largest category to be displayed. Otherwise it just drops off results for post types.

    Thread Starter Ainsley Clark

    (@ainsleyclark)

    Hi @vrandom .
    Thanks for the reply, the isset stuff worked.

    Hmmm, currently is resulting in 130 posts, when there is . total of 8 posts, 3 news, 3 exhibitors and 1 speaker.

    Would it be here?

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

    On my other pages I only have prev page and next page, I was thinking loosing the 1, 2, 3, 4 etc would help but I dont think it would?

    Also is there a way of finding out the last post of that particular post type?

    E.g

    if ($post_type == 'news') {
        if ('this is the last post type of news') {
              //do something
        }
    }

    Thanks once again.

    • This reply was modified 3 years, 4 months ago by Ainsley Clark.
Viewing 15 replies - 16 through 30 (of 62 total)
  • The topic ‘Posts Per Page -> Per Post Type’ is closed to new replies.