Author Page Custom Query WHERE author OR [post meta value] OR [post meta value]

Question

I am currently trying to perform a custom query on the authors page (author.php). I have two custom fields for posts that I want to query against (post_photographer and post_videographer).

What I am trying to do for the author's profile is get all the posts for the current user profile where the user is:

• the author of the post
• OR the post photographer
• OR the post videographer

So each person's profile MAY have posts that they didn't author (they were the photographer or videographer instead).

The following code is close to what I want, but the problem is that it retrieves posts where the current user is the author AND is either a post photographer OR post videographer. It needs to be where the current user is the author OR post photographer OR post videographer.

$args = array(
'author' => $posts[0]->post_author,
'meta_query' => array(
    'relation' => 'OR',
    array(
        'key' => 'post_photographer',
        'value' => $posts[0]->post_author,
        'type' => 'numeric'
    ),
    array(
        'key' => 'post_videographer',
        'value' => $posts[0]->post_author,
        'type' => 'numeric'
    )
  )
);
query_posts( $args );

Is this possible via WordPress queries (query_posts or WP_Query), or do I need to write custom SQL? Any help is greatly appreciated! If you need clarification please ask.

Answer 1

For anyone who may have a similar need, I solved this the following way (on authors.php):

First I get the author ID:

$author = get_user_by( 'slug', get_query_var( 'author_name' ) );

// ID is accessed this way:
$author_id = $author->ID;

I then created a custom query:

        $query = "
                SELECT p.* 
                FROM   $wpdb->postmeta m 
                             JOIN $wpdb->posts p 
                                 ON p.id = m.post_id 
                WHERE  ( m.meta_key = 'medium_post_photographers' 
                                 AND m.meta_value = '$author->ID' ) 
                                OR ( m.meta_key = 'medium_post_videographers' 
                                         AND m.meta_value = '$author->ID' ) 
                                     AND p.post_status = 'publish' 
                UNION DISTINCT 
                SELECT * 
                FROM   $wpdb->posts p 
                WHERE  post_author = $author->ID 
                             AND p.post_status = 'publish' 
                GROUP  BY p.id 
                ORDER  BY post_date DESC 
        ";

And finally use the following to get the results:

 $author_posts = $wpdb->get_results($query, OBJECT);   

Here is a simplified version of my loop to display the results:

<?php if ( $author_posts ) : ?>
    <?php global $post; ?>
    <?php foreach ( $author_posts as $post ) : setup_postdata($post); ?>
        <h6><?php echo the_title(); ?></h6>
        ...
    <?php endforeach; ?>
<?php else: ?>
    <div class="alert">There are no posts in this category.</div>
<?php endif; ?>

I hope this helps someone!

Answer 2

Either of these 2 filters should help you:

1. post_clauses (I suggest this one, its a little more detailed!)
2. posts_where

Check the examples in codex.