0

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' );
}
  • What was the reason here for not using WP_Query? Or just altering the main query to avoid an extra db call? – Tom J Nowell Jun 5 '15 at 19:32
  • I tried WP_Query before but couldn't get it to work (wordpress.stackexchange.com/questions/78117/…). I get pagination and selecting the latest post issue with WP_Query. – Ohsik Jun 5 '15 at 19:35
  • This appears to be aduplicate question: wordpress.stackexchange.com/q/190511/21376 – s_ha_dum Jun 5 '15 at 19:39
  • I'm taking different approach here. For this question, I'm looking to find out a way to implement numbered pagination on custom wpdb result. – Ohsik Jun 5 '15 at 19:43
  • 1
    Can you provide more context? Are you trying to implement an archive, and ignoring the main loop/query? Ditching WP_Query was the wrong thing to do – Tom J Nowell Jun 5 '15 at 19:49
3

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.

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() ? '&rarr;' : '&larr;';
$next_arrow = is_rtl() ? '&larr;' : '&rarr;';

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,
     ) );
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.