Paginate WordPress $wpdb Query?

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

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!