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

Question

I need to create a custom query for the author archive (where the author is post_author OR is a meta co_author), and it have to work with WP Pagenavi.

The query is something like this (omitting $wpdb and usual clauses for clarity):

SELECT posts.*
FROM posts, postmeta
WHERE
    posts.ID = postmeta.post_id AND
    (posts.post_author = $author OR
        (postmeta.meta_key = 'co_author' AND
         postmeta.meta_value = $author)
    )

I've already tried with no success:

• using LIMIT and OFFSET with query_var($paged) and 'posts_per_page' option, but Pagenavi total pages are the same as the query was the default author archive one;
• adding a where filter (' OR posts.post_author = $author') to a custom WP_Query checking for meta_key/value only, but Pagenavi completely disregards the filter;
• other stuff I actually cannot remember because I've tried them last night between 3 and 5 AM but I'm sure they didn't work...

The actual problem is that I can't find a way to check "meta_query OR post_author" using just the WP_Query argument array, like:

$the_query = new WP_Query(array(
    'post_author'    => $author,
    /* OR */
    'meta_query'     => array(
        array(
            'key'    => 'co_author',
            'value'  => $author
        )
    )
));

Unfortunately, I'm not really confident with query filters so I'm really really trying to find a simple workaround to make that work with Pagenavi. But, after plenty of hours on this issue, my mind isn't fresh anymore and I don't know how to proceed.

Thank you all in advance.

Answer 1

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!