Custom query (author is post_author or meta co_author) with Pagenavi pagination

Question

Ok, I think I found something that actually works, and I’m posting it just in case someone need it.

The solution is simply to avoid using Pagenavi just for this page, using paginate_links() instead and forcing it to behave more or less like Pagenavi to maintain coherence all over the site.

Let me explain.

My custom query is now something like this:

$query = "
SELECT $wpdb->posts.*
FROM $wpdb->posts, $wpdb->postmeta
WHERE
    $wpdb->posts.ID = $wpdb->postmeta.post_id
    AND $wpdb->posts.post_status="publish"
    AND $wpdb->posts.post_type="post"
    AND $wpdb->posts.post_date <= NOW()
    AND (
        $wpdb->posts.post_author = $curauth->ID
        OR (
            $wpdb->postmeta.meta_key = 'co_author'
            AND $wpdb->postmeta.meta_value = $curauth->ID
        )
    )
GROUP BY $wpdb->posts.ID
ORDER BY $wpdb->posts.post_date DESC
";

Then I create some useful variables for pagination:

global $wpdb;
global $post;

$total_query = "SELECT COUNT(*) FROM ($query) AS combined_table";
// Total count of rows
$total = $wpdb->get_var($total_query);

// Number of rows per page, as set in WordPress settings
$items_per_page = get_option('posts_per_page');

// Current page; defaults to 1 if not requested
$page = isset($_GET['page']) ? abs((int) $_GET['page']) : 1;

// Offset for query LIMIT
$offset = ($page*$items_per_page) - $items_per_page;

// Well... the next one is pretty much self-explained...
$last_page = ceil($total/$items_per_page);

Then I perform the query and build the loop:

$posts = $wpdb->get_results($query." LIMIT $offset, $items_per_page");

foreach ($posts as $post) :
    setup_postdata($post);
    // The Loop
endforeach;

Finally, the pagination (note that I’m using an hybrid of paginate_links() / wp_pagenavi() classes to style it):

// Echo "Page # of #"
echo '<span class="pages">Page '.$page.' of '.$last_page.'</span>';

// If this isn't the first page...
if ($page != 1)
    echo '<a class="page-numbers" href="'.$base_url.'">&lt; FIRST</a>';

// I don't really know how to explain this one because I found it somewhere I can't find again, but it works!
echo paginate_links(
    'base'        => add_query_arg('page', '%#%'),
    'prev_text'   => '&lt;',
    'next_text'   => '&gt;',
    'total'       => $last_page
    'current'     => $page
);

// If this isn't the last page...
if ($page != $last_page)
    echo '<a class="page-numbers" href="'.$base_url.'?page=".$last_page."">LAST &gt;</a>';

As you may have noticed, there is a $base_url variable: that’s the only caveat I have.
First of all, this is $base_url:

$current_url = $_SERVER["HTTP_HOST"] . $_SERVER["REQUEST_URI"];
$current_url = explode('?', $current_url);
$base_url="http://".$current_url[0];

The caveat is that I couldn’t get paginate_links() to echo rewritten urls, neither how to generate them for first page and last page links.
So now urls in this archive are something like http://www.example.com/author/username/?page=2 instead of http://www.example.com/author/username/page/2/

That’s it. I hope someone will find this useful!

Related Posts: