WordPress get pagination on wpdb get_results

Question

How do I gee the numbered pagination of custom wpdb result?

below code will show one latest post from each authors in the site. I want to show 20 posts per page with a numbered pagination.

$postids = $wpdb->get_results( 
    "
     SELECT a.ID, a.post_title, a.post_author, a.post_date
     FROM $wpdb->posts a
     JOIN (
              SELECT max(post_date) post_date, post_author
                FROM $wpdb->posts
              WHERE post_status = 'publish' 
                AND post_type = 'post'
              GROUP BY post_author
          ) b ON a.post_author = b.post_author AND a.post_date = b.post_date
     WHERE post_status = 'publish' 
           AND post_type = 'post'
     ORDER BY post_date DESC
    "
);

foreach ( $postids as $postid ) 
{
    //setup_postdata( $postid );
    echo $postid->ID." :: ".$postid->post_title." :: ".$postid->post_author . "<br />" ;
    //get_template_part( 'content', 'category' );
}

Answer 1

You say "However, I know this is not a good way to do this" in your self answer. One thing I could add answering your question and using your answer is, you can use SQL_CALC_FOUND_ROWS with $wpdb

$result = $wpdb->get_results( 
    "SELECT SQL_CALC_FOUND_ROWS * FROM `wp_table` WHERE 1 LIMIT 10;"
);
$total_count = $wpdb->get_var(
    "SELECT FOUND_ROWS();"
);

This way you don't have to run the query twice.

Answer 2

So I ended getting what I wanted to do with below code. However, I know this is not a good way to do this. Please feel free to share your advice on this code.

global $wpdb;
global $post;

// Pagination Setup
$posts_per_page = 20;
$start = 0;
$paged = get_query_var( 'paged') ? get_query_var( 'paged', 1 ) : 1; // Current page number
$start = ($paged-1)*$posts_per_page;

$num_of_total_posts = $wpdb->get_results( 
    "
     SELECT a.ID, a.post_title, a.post_author, a.post_date
     FROM $wpdb->posts a
     JOIN (
              SELECT max(post_date) post_date, post_author
                FROM $wpdb->posts
              WHERE post_status = 'publish' 
                AND post_type = 'post'
              GROUP BY post_author
          ) b ON a.post_author = b.post_author AND a.post_date = b.post_date
     WHERE post_status = 'publish' 
           AND post_type = 'post'
     ORDER BY post_date DESC
    "
);
$total_posts = $wpdb->num_rows; // Get Total number of posts


$postids = $wpdb->get_results( 
    "
     SELECT a.ID, a.post_title, a.post_author, a.post_date, a.post_name
     FROM $wpdb->posts a
     JOIN (
              SELECT max(post_date) post_date, post_author
                FROM $wpdb->posts
              WHERE post_status = 'publish' 
                AND post_type = 'post'
              GROUP BY post_author
          ) b ON a.post_author = b.post_author AND a.post_date = b.post_date
     WHERE post_status = 'publish' 
           AND post_type = 'post'
     ORDER BY post_date DESC
     LIMIT $start, $posts_per_page
    "
);

// Loop content
foreach ( $postids as $post ):
    setup_postdata($post);
    get_template_part( 'content', 'category' );
endforeach;


// Display Pagination
$total_page = ceil( $total_posts / $posts_per_page); // Calculate Total pages

$prev_arrow = is_rtl() ? '→' : '←';
$next_arrow = is_rtl() ? '←' : '→';

global $wp_query;
$big = 999999999; // need an unlikely integer
if( $total > 1 )  {
     if( !$current_page = get_query_var('paged') )
         $current_page = 1;
     if( get_option('permalink_structure') ) {
         $format = 'page/%#%/';
     } else {
         $format = '&paged=%#%';
     }
    echo paginate_links(array(
        'base'          => str_replace( $big, '%#%', esc_url( get_pagenum_link( $big ) ) ),
        'format'        => $format,
        'current'       => max( 1, get_query_var('paged') ),
        'total'         => $total_page,
        'mid_size'      => 3,
        'type'          => 'list',
        'prev_text'     => $prev_arrow,
        'next_text'     => $next_arrow,
     ) );
}