WordPress get pagination on wpdb get_results

by

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

2 Answers
2

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.

Leave a Comment