Paginate Wordpress $wpdb Query?

Question

I have this query:

    <?php
    $query= "SELECT wposts.* FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta WHERE wposts.ID = wpostmeta.post_id AND wpostmeta.meta_key = 'votes' AND wposts.post_status = 'publish' AND wposts.post_type = 'post' ORDER BY CAST(wpostmeta.meta_value AS SIGNED) DESC LIMIT 10";
    $posts = $wpdb->get_results($query, OBJECT);
    if ($posts ) : foreach ($posts as $post):
    setup_postdata($post);
    ?>
    // Post here 
    <?php endforeach; endif; ?>
    <div class="pagination">
        <?php wp_pagenavi(); ?>
    </div>

I'm using this because Wordpress can't properly order meta_values that uses numbers, anyway...everything works fine except I have no clue on how to paginate this using wp_pagenavi.

Any idea?

Answer 1

global $wp_query; 
// per page 
$ppp = $limit;
// full found posts , query without limit 
$wp_query->found_posts = count($total);
// number of pages 
$wp_query->max_num_pages = ceil($wp_query->found_posts / $ppp); 

It Works!

Answer 2

<?php

global $wpdb, $wp_query, $paged;

$author = isset($_GET['author_name']) ? get_userdatabylogin($author_name) : get_userdata(intval($author));
$post_per_page = intval(get_query_var('posts_per_page'));
query_posts($query_string . '&posts_per_page=' . $post_per_page);
$paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
$offset = ($paged - 1) * $post_per_page;

$query = "SELECT `wp_postmeta`.*, `wp_posts`.* ";
$query .= "FROM `wp_postmeta` "; 
$query .= "LEFT JOIN `wp_posts` ON wp_posts.id = wp_postmeta.post_id "; 
$query .= "WHERE `meta_key` = 'autor' AND `post_status` = 'publish' AND `meta_value` = '%s' "; 
$postsQuery = $query . "LIMIT $offset, " . $post_per_page;

$numRowsQuery = $wpdb->prepare( $query, $author->id );
$allPosts = $wpdb->get_results($numRowsQuery); 
$wp_query->found_posts = $wpdb->num_rows;
$wp_query->max_num_pages = ceil($wp_query->found_posts / $post_per_page); 

$dataQuery = $wpdb->prepare( $postsQuery, $author->id );
$posts = $wpdb->get_results($dataQuery); 

?> 

<?php if ($posts) : ?>
    <?php foreach ($posts as $post): ?>
       <a href="<?php the_permalink(); ?>"><?php echo get_the_title(); ?></a>
    <?php endforeach; ?>
<?php endif; ?>

<?php wp_pagenavi(); ?>

Answer 3

Thank @jaziel matoso,

Below code works:

    global $wp_query; 
    $query = "SELECT wposts.* FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta 
       WHERE wposts.ID = wpostmeta.post_id AND wpostmeta.meta_key = 'votes'
       AND wposts.post_status = 'publish' AND wposts.post_type = 'post' ORDER BY 
       CAST(wpostmeta.meta_value AS SIGNED) DESC";

    $total_record = count($wpdb->get_results($query, ARRAY_A));

    $paged      = get_query_var('paged') ? get_query_var('paged') : 1;
    $post_per_page  = get_option('posts_per_page');
    $offset         = ($paged - 1)*$post_per_page;
    $max_num_pages  = ceil($total_record/ $post_per_page);

    $wp_query->found_posts = $total_record;
    // number of pages 
    $wp_query->max_num_pages = $max_num_pages;

    $limit_query    =   " LIMIT ".$post_per_page." OFFSET ".$offset;    

    $result =   $wpdb->get_results($query.$limit_query,OBJECT);// return OBJECT

   if($result):
    foreach ($result as $post):
      setup_postdata($post);
          ?>
           <h3><a href="<?php the_permalink(); ?>"><?php the_title(); ?></a></h3>
           <p><?php the_content(); ?></p>
        <?php
        endforeach;
        ?>
       <div class="navigation"><?php wp_pagenavi(); ?></div>
        <?php
   endif;

Done!

Answer 4

Although I wouldn't recommend it, you could try changing the properties of the global $wp_query object.

global $wp_query; // shouldn't be required
$query = "SELECT wposts.* FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta 
   WHERE wposts.ID = wpostmeta.post_id AND wpostmeta.meta_key = 'votes'
   AND wposts.post_status = 'publish' AND wposts.post_type = 'post' ORDER BY 
   CAST(wpostmeta.meta_value AS SIGNED) DESC LIMIT 10";
$posts = $wpdb->get_results($query, OBJECT);

$wp_query->posts = $posts;
$wp_query->is_paged = true;
$wp_query->current_post = -1;
// etc etc

You can look up the definition of the WP_Query class or do a var_dump() or print_r() on the $wp_query object after calling query_posts.

Good luck!