Posts Per Page -> Per Post Type

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 7 years, 1 month ago by Ainsley Clark.

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 7 years, 1 month ago by Ainsley Clark.

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 7 years, 1 month ago by Ainsley Clark.

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