author.php with ACF and CPTs

As I write this, I’ll do my best to avoid an XY Problem.

What I’m trying to do:

I’m trying to build an author.php template that:

  • displays data from Advanced Custom Fields on the user’s Profile Page
  • displays author’s posts
    • all posts that the user has authored
    • all projects (CPT) in which the user is Credited (assigned via an ACF Field for the CPT)

How I’ve tried solving this:

Attempt 1 (author.php template)

My first attempt, and it mostly worked. What caused me to abandon this approach was pagination. More on that later. I had three queries:

  • The first got all posts the user authored,
  • the second got all Projects the user was credited to,
  • and the third made use of post__in via array_unique( array_merge($query1, $query2) ) in order to display the posts from those two queries at once, organized by date.

    <?php
    get_header();
    
    global $wp_query;
    if( !isset($whose_ID) ) {
        $whose_ID = $wp_query->queried_object->data->ID;
    }
    
    /*
    |==========================================================================
    | First Query gets all posts by author
    |==========================================================================
    */
    $args = array(
        'author' => $whose_ID,
        'post_type' => 'post'
    );
    $query1 = new WP_Query( $args );
    $first_set = array();
    foreach($query1->posts as $post) {
        $first_set[] = $post->ID;
    }
    wp_reset_postdata();
    
    /*
    |==========================================================================
    | Second Query gets all Projects that are credited to author
    |==========================================================================
    */
    $args2 = array(
        'post_type' => 'projects',
        'meta_key' => 'project_credits_%_user',
        'meta_value' => $whose_ID,
    );
    $query2 = new WP_Query( $args2 );
    $second_set = array();
    foreach($query2->posts as $post) {
        $second_set[] = $post->ID;
    }
    wp_reset_postdata();
    
    /*
    |==========================================================================
    | Then the two queries are merged to create a bag of posts to grab from
    |==========================================================================
    |
    | What sucks about this is the $third_query_allowed. I had to figure out
    | a way to make sure that the query didn't run on an empty array. Bah.
    |
    */
    $third_query_allowed = true;
    if( !empty($first_set) && !empty($second_set) ) {
        $merged_queries = array_unique( array_merge( $first_set, $second_set ) );
    } elseif (!empty($first_set) && empty($second_set) ) {
        $merged_queries = $first_set;
    } elseif ( empty($first_set) && !empty($second_set) ) {
        $merged_queries = $second_set;
    } else {
        $merged_queries = array();
        $third_query_allowed = false;
    }
    
    /*
    |==========================================================================
    | So then this third query does the work of getting all the posts and
    | displaying them on the author page.
    |==========================================================================
    */
    $paged = ( get_query_var( 'paged' ) ) ? get_query_var( 'paged' ) : 1;
    $args3 = array(
        'post_type' => array( 'post', 'projects' ),
        'post__in' => $merged_queries,
        'orderby' => 'post__in',
        'posts_per_page' => 2,
        'paged' => $paged
    );
    $user_connected_to_post = new WP_Query( $args3 ); ?>
    
    <div id="primary" class="">
        <main id="main" class="site-main" role="main">
    
            <?php
            // ACF Helper
            $search = 'user_'.$whose_ID;
    
            // Header Info
            // All of this Works!!
            $name = get_the_author_meta( 'display_name', $whose_ID );
            $job_title = get_field( 'job_title', $search );
            $user_favorite_color = get_field( 'user_favorite_color', $search );
            $user_featured_image = get_field( 'user_featured_image', $search );
            $user_still_image = get_field( 'user_still_image', $search );
            $user_gif = get_field( 'user_gif', $search );
            $user_mp4 = get_field( 'user_mp4', $search );
    
            if(!empty($user_featured_image) && !empty($user_favorite_color)) {
                $header_styles = "style=\"background:url('".$user_featured_image['url']."') no-repeat scroll center center ".$user_favorite_color."; background-size: 100% auto;\"";
            } elseif(!empty($user_featured_image) && empty($user_favorite_color)) {
                $header_styles = "style=\"background:url('".$user_featured_image['url']."') no-repeat scroll center center #efefef; background-size: 100% auto;\"";
            } elseif(empty($user_featured_image) && !empty($user_favorite_color)) {
                $header_styles = "style=\"background-color:".$user_favorite_color."\"";
            }
            ?>
    
            <div class="container-fluid">
                <div class="author-page-header row">
                    <div class="author-page-bg col-sm-12" <?php echo $header_styles; ?>></div>
    
                    <video class="img-circle the-vid author-page-img" width="250" height="250" loop="loop" muted="" autoplay="autoplay" poster="<?php echo $user_still_image['url']; ?>">>
                        <source type="video/mp4" data-source="<?php echo $user_mp4['url']; ?>">
                    </video>
                    <img class="img-circle the-img author-page-img" alt="" data-source="<?php echo $user_gif['url']; ?>">
                </div>
            </div>
    
            <div class="container">
                <div class="row">
                    <header class="author-page-name lower-divider text-center">
                        <h1><?php echo $name; ?></h1>
                        <h3><?php echo $job_title; ?></h3>
                    </header><!-- .author-name -->
                </div>
    
                <div class="row">
                    <div class="author-page-social">
                        <?php
                        // ACF Bug is keeping this from working at the moment.
                        echo three_sons_social_list( $search, array( 'bellyflop', 'dale-earnhardt' ) ); ?>
                    </div>
                </div>
    
                <div class="row upper-divider">
                    <div class="col-xs-10 col-xs-offset-1 col-sm-8 col-sm-offset-2 author-page-bio">
                        <p><?php echo get_the_author_meta( 'description', $whose_ID ); ?></p>
                    </div>
                </div>
    
                <?php
                /*
                |==========================================================================
                | here's all the author's posts (this works)
                |==========================================================================
                */
                if( $third_query_allowed === true ){
    
                    if ( $user_connected_to_post->have_posts() ) : ?>
    
                        <div class="row masonry-grid">
    
                            <?php while ( $user_connected_to_post->have_posts() ) : $user_connected_to_post->the_post();
    
                                if($post->post_type === "projects") {
                                    get_template_part( 'lib/partials/projects', 'masonry_item' );
                                } else {
                                    // default post listing
                                    get_template_part( 'lib/partials/content', 'masonry_item' );
                                }
    
                            endwhile; ?>
    
                        </div> <!-- .masonry-grid -->
    
                        <?php // THIS IS THE THING THAT DID NOT WORK ?>
                        <div class="nav-previous alignleft"><?php next_posts_link( 'Older posts', $user_connected_to_post->max_num_pages ); ?></div>
                        <div class="nav-next alignright"><?php previous_posts_link( 'Newer posts', $user_connected_to_post->max_num_pages ); ?></div>
    
                    <?php endif;
                    wp_reset_postdata();
                    wp_reset_query();
                } ?>
    
            </div><!-- .container -->
    
        </main><!-- #main -->
    </div><!-- #primary -->
    
    <?php get_footer();
    

Since I could not get pagination to work without a 404, I changed my approach.

Attempt 2

After reading several suggestions about how an author.php template uses the Main Query, I transitioned to a function that modified the main loop with the pre_get_posts action. I moved all the queries to their own function that returned just the array of posts I wanted to display for the author’s page. I used $query->set() and ran into an issue where one of my queries was an infinite loop. There was no foreach or while loops in my code, so that’s still a mystery. I abandoned this attempt because of the infinite loop, and Russian-doll functionality.

Attempt 3

Eventually I landed on this code, which in theory should work (but does not). You’ll see three direct $wpdb queries. Since I had just successfully nuked my Vagrant box for 45 minutes with a loop that was inexplicably infinite, I decided to try to create the most lean and memory-saving query possible. I don’t know if this is actually better, but it’s what I did. That’s not the point.

merged_author_archive():

function merged_author_archive( &$query ) {

    // Only do this weird shit for the author pages.
    if ( $query->is_author ) {

        // Start with a fresh query (Whether this line is here or not makes no difference)
        wp_reset_query();

        global $wpdb;
        // holy shit this is so gd complex it drives me crazy.
        // since the only thing that gets passed to wordpress on author.php (apparently) to start is the author name, that's what we're going to query.
        $author_nice_name = $query->query['author_name'];

        // Get the Author ID from the nicename.
        $author_ID = $wpdb->get_results("SELECT id FROM $wpdb->users WHERE user_nicename LIKE '$author_nice_name'");
        $author_ID = $author_ID[0]->id;

        // so we can get this on author pages.
        set_query_var( 'three_sons_author_id', $author_ID );

        // Get the IDs from posts authored and then save all the ids to $first_set
        $authored_posts = $wpdb->get_results("SELECT id FROM $wpdb->posts WHERE post_author = '$author_ID' AND post_type = 'post'");
        foreach ($authored_posts as $post) {
            $first_set[] = $post->id;
        }

        // Get the IDS from the projects credited
        $credited_projs = $wpdb->get_results("SELECT post_id FROM $wpdb->postmeta WHERE meta_key LIKE 'project_credits_%_user' AND meta_value = '$author_ID'");
        foreach ($credited_projs as $proj) {
            $second_set[] = $proj->post_id;
        }

        // First case: Both queries resulted in an array that wasn't empty.
        if( !empty($first_set) && !empty($second_set) ) {
            $just_these_posts = array_unique( array_merge( $first_set, $second_set ) );
        }
        // Second Case: Posts were not empty, but Projects were
        elseif (!empty($first_set) && empty($second_set) ) {
            $just_these_posts = $first_set;
        }
        // Third Case: Posts were empty, but Projects were not.
        elseif ( empty($first_set) && !empty($second_set) ) {
            $just_these_posts = $second_set;
        }

        // Here's the new $wp_query. We need to define the array of posts, and the post ids to send to the author page. Then we're done here.
        $query->set( 'post_type', array('post', 'projects') );
        $query->set( 'post__in', $just_these_posts );
    }

    // if it's not an author page, remove the action.
    remove_action( 'pre_get_posts', 'three_sons_merged_author_archive' );
}
add_action( 'pre_get_posts', 'three_sons_merged_author_archive' );

…and its subsequent author.php:

get_header();

// getting the author ID from the query. was set in lib/inc/author-functions.php
$whose_ID = get_query_var('three_sons_author_id');

// Header Info
$name = get_the_author_meta( 'display_name', $whose_ID );

// ACF
$search                     = 'user_'.$whose_ID;
$job_title                  = get_field( 'job_title', $search );
$user_favorite_color        = get_field( 'user_favorite_color', $search );
$user_featured_image        = get_field( 'user_featured_image', $search );
$user_still_image           = get_field( 'user_still_image', $search );
$user_gif                   = get_field( 'user_gif', $search );
$user_mp4                   = get_field( 'user_mp4', $search );

if(!empty($user_featured_image) && !empty($user_favorite_color)) {
    $header_styles = "style=\"background:url('".$user_featured_image['url']."') no-repeat scroll center center ".$user_favorite_color."; background-size: 100% auto;\"";
} elseif(!empty($user_featured_image) && empty($user_favorite_color)) {
    $header_styles = "style=\"background:url('".$user_featured_image['url']."') no-repeat scroll center center #efefef; background-size: 100% auto;\"";
} elseif(empty($user_featured_image) && !empty($user_favorite_color)) {
    $header_styles = "style=\"background-color:".$user_favorite_color."\"";
}
?>

<div id="primary" class="">
    <main id="main" class="site-main" role="main">

        <div class="container-fluid">
            <div class="author-page-header row">
                <div class="author-page-bg col-sm-12" <?php echo $header_styles; ?>></div>

                <video class="img-circle the-vid author-page-img" width="250" height="250" loop="loop" muted="" autoplay="autoplay" <? /*poster="<?php echo $user_still_image['url']; ?>" */ ?>>
                    <source type="video/mp4" data-source="<?php echo $user_mp4['url']; ?>">
                </video>
                <img class="img-circle the-img author-page-img" alt="" data-source="<?php echo $user_gif['url']; ?>">
            </div>
        </div>

        <div class="container">
            <div class="row">
                <header class="author-page-name lower-divider text-center">
                    <h1><?php echo $name; ?></h1>
                    <h3><?php echo $job_title; ?></h3>
                </header><!-- .author-name -->
            </div>

            <div class="row">
                <div class="author-page-social">
                    <!-- just for now... -->
                    <p class="text-center">Social Links will go here but they're currently broken for authors.</p>
                    <?php // echo three_sons_social_list( $search, array( 'bellyflop', 'dale-earnhardt' ) ); ?>
                </div>
            </div>

            <div class="row upper-divider">
                <div class="col-xs-10 col-xs-offset-1 col-sm-8 col-sm-offset-2 author-page-bio">
                    <p><?php echo get_the_author_meta( 'description', $whose_ID ); ?></p>
                </div>
            </div>

            <?php
            if ( have_posts() ) : ?>

                <div class="row masonry-grid">

                    <?php while ( have_posts() ) : the_post();

                        if($post->post_type === "projects") {
                            get_template_part( 'lib/partials/projects', 'masonry_item' );
                        } else {
                            // default post listing
                            get_template_part( 'lib/partials/content', 'masonry_item' );
                        }

                    endwhile; ?>

                </div> <!-- .masonry-grid -->

                <div class="nav-previous alignleft"><?php next_posts_link( 'Older posts' ); ?></div>
                <div class="nav-next alignright"><?php previous_posts_link( 'Newer posts' ); ?></div>

            <?php endif; ?>

        </div><!-- .container -->

    </main><!-- #main -->
</div><!-- #primary -->


<?php wp_reset_query();
get_footer();

Essentially, I believe this latest code should work. But it doesn’t. Something is not translating. If I do a var_dump($wp_query); die; on author.php, I see this (I deleted the irrelevant or sensitive bits):

object(WP_Query)#258 (51) {
  ["query"]=>
  array(1) {
    ["author_name"]=>
    string(15) "author-slug"
  }
  ["query_vars"]=>
  array(64) {
    ["author_name"]=>
    string(15) "author-slug"
    ["post__in"]=>
    array(9) {
      [0]=>
      string(1) "1"
      [1]=>
      string(3) "102"
      [2]=>
      string(3) "160"
      [3]=>
      string(3) "196"
      [4]=>
      string(3) "199"
      [5]=>
      string(3) "201"
      [6]=>
      string(3) "206"
      [7]=>
      string(3) "162"
      [8]=>
      string(3) "198"
    }
    ["three_sons_author_id"]=>
    string(1) "3"
    ["post_type"]=>
    array(2) {
      [0]=>
      string(4) "post"
      [1]=>
      string(8) "projects"
    }
    ["order"]=>
    string(4) "DESC"
  }
  ["date_query"]=>
  bool(false)
  ["queried_object"]=>
  object(WP_User)#152 (7) {
    ["data"]=>
    object(stdClass)#151 (10) {
      ["ID"]=>
      string(1) "3"
      ["user_login"]=>
      string(6) "author"
      ["user_nicename"]=>
      string(15) "author-slug"
      ["user_email"]=>
      string(16) "author@example.com"
      ["user_url"]=>
      string(17) "http://example.com"
      ["user_registered"]=>
      string(19) "2016-05-09 19:12:24"
      ["user_status"]=>
      string(1) "0"
      ["display_name"]=>
      string(15) "Author Name"
    }
    ["ID"]=>
    int(3)
    ["caps"]=>
    array(1) {
      ["administrator"]=>
      bool(true)
    }
    ["cap_key"]=>
    string(25) "wp_capabilities"
    ["roles"]=>
    array(1) {
      [0]=>
      string(13) "administrator"
    }
  }
  ["queried_object_id"]=>
  int(3)
  ["request"]=>
  string(489) "SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.ID IN (1,102,160,196,199,201,206,162,198) AND (wp_posts.post_author = 3) AND wp_posts.post_type IN ('post', 'projects') AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_author = 1 AND wp_posts.post_status = 'private')  ORDER BY wp_posts.post_date DESC LIMIT 0, 10"
  ["posts"]=>
  &array(1) {
    [0]=>
    object(WP_Post)#144 (24) {
      ["ID"]=>
      int(1)
      ["post_author"]=>
      string(1) "3"
      ["post_date"]=>
      string(19) "2016-05-03 20:12:01"
      ["post_date_gmt"]=>
      string(19) "2016-05-04 00:12:01"
      ["post_content"]=>
      string(85) "Welcome to WordPress. This is your first post. Edit or delete it, then start writing!"
      ["post_title"]=>
      string(12) "Hello world!"
      ["post_excerpt"]=>
      string(0) ""
      ["post_status"]=>
      string(7) "publish"
      ["comment_status"]=>
      string(6) "closed"
      ["ping_status"]=>
      string(6) "closed"
      ["post_password"]=>
      string(0) ""
      ["post_name"]=>
      string(11) "hello-world"
      ["to_ping"]=>
      string(0) ""
      ["pinged"]=>
      string(0) ""
      ["post_modified"]=>
      string(19) "2016-06-15 14:39:12"
      ["post_modified_gmt"]=>
      string(19) "2016-06-15 18:39:12"
      ["post_content_filtered"]=>
      string(0) ""
      ["post_parent"]=>
      int(0)
      ["guid"]=>
      string(19) "http://3sm.dev/?p=1"
      ["menu_order"]=>
      int(0)
      ["post_type"]=>
      string(4) "post"
      ["post_mime_type"]=>
      string(0) ""
      ["comment_count"]=>
      string(1) "1"
      ["filter"]=>
      string(3) "raw"
    }
  }
  ["post_count"]=>
  int(1)
  ["current_post"]=>
  int(-1)
  ["in_the_loop"]=>
  bool(false)
  ["post"]=>
  object(WP_Post)#144 (24) {
    ["ID"]=>
    int(1)
    ["post_author"]=>
    string(1) "3"
    ["post_date"]=>
    string(19) "2016-05-03 20:12:01"
    ["post_date_gmt"]=>
    string(19) "2016-05-04 00:12:01"
    ["post_content"]=>
    string(85) "Welcome to WordPress. This is your first post. Edit or delete it, then start writing!"
    ["post_title"]=>
    string(12) "Hello world!"
    ["post_excerpt"]=>
    string(0) ""
    ["post_status"]=>
    string(7) "publish"
    ["comment_status"]=>
    string(6) "closed"
    ["ping_status"]=>
    string(6) "closed"
    ["post_password"]=>
    string(0) ""
    ["post_name"]=>
    string(11) "hello-world"
    ["to_ping"]=>
    string(0) ""
    ["pinged"]=>
    string(0) ""
    ["post_modified"]=>
    string(19) "2016-06-15 14:39:12"
    ["post_modified_gmt"]=>
    string(19) "2016-06-15 18:39:12"
    ["post_content_filtered"]=>
    string(0) ""
    ["post_parent"]=>
    int(0)
    ["guid"]=>
    string(19) "http://3sm.dev/?p=1"
    ["menu_order"]=>
    int(0)
    ["post_type"]=>
    string(4) "post"
    ["post_mime_type"]=>
    string(0) ""
    ["comment_count"]=>
    string(1) "1"
    ["filter"]=>
    string(3) "raw"
  }
}

Please note that the post__in array contains the correct posts…but for some reason, posts only has “Hello World” in it. There should be at least three projects attached to this author as well. They’re in the query, it looks like, but are not returned. For users that have not authored any posts, nothing shows up in the loop at all. For User ID 1, posts AND projects show up.

Maybe this has something to do with the request?

"SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.ID IN (1,102,160,196,199,201,206,162,198) AND (wp_posts.post_author = 3) AND wp_posts.post_type IN ('post', 'projects') AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_author = 1 AND wp_posts.post_status = 'private')  ORDER BY wp_posts.post_date DESC LIMIT 0, 10"

I know it’s complex and a lot to ask, but any help in next steps to take, or errors with my code would be outstandingly appreciated. Thanks!


Update, after @PieterGoosen reply:

I had to make a few changes to his code in order to support PHP < 5.4 (ugh – client servers!). The only major change is using a WP_Query instead of get_posts() for the second query, because get_posts() doesn’t hook into the posts_where action, which I needed because there’s a wildcard in the meta_key (due to Advanced Custom Fields Repeater Field):

function posts_where_credited( $where ) {
    $where = str_replace("meta_key = 'project_credits_%_user", "meta_key LIKE 'project_credits_%_user", $where);
    return $where;
}
add_filter( 'posts_where' , 'posts_where_credited' );

So then here is the pre_get_posts function:

function author_pre_get_posts ( $q ) {
    // Remove action
    remove_action( 'pre_get_posts', __FUNCTION__ );

    // Only target the main query, front end only on author archive pages
    if (    !is_admin()
         && $q->is_main_query()
         && $q->is_author()
    ) {
        wp_reset_query();
        wp_reset_postdata();

        // Now we can run our custom queries to get post ID's
        $author_name = sanitize_title_for_query( $q->query['author_name'] );

        // This section is also used by WP_Query to get user id
        $author = get_user_by( 
            'slug', 
            $author_name 
        );
        $author_id = absint( $author->ID );

        // Get the posts
        $args_1 = array(
            'author'                 => $author_id, // Get from referenced query
            'fields'                 => 'ids', // Only get the Post's IDs, faster, lighter, better, stronger
            'posts_per_page'         => -1, // Get all posts
            'cache_results'          => false, // Do not update post cache
            'update_post_meta_cache' => false, // Do not update post meta cache
            'update_post_term_cache' => false, // Do not cache post terms
        );
        $post_type_post_ids = get_posts( $args_1 );

        // Get the projects
        $args_2 = array(
            'post_type'              => 'projects',
            'fields'                 => 'ids', // Only get the Post's IDs, faster, lighter, better, stronger
            'posts_per_page'         => -1, // Get all posts
            'cache_results'          => false, // Do not update post cache
            'update_post_meta_cache' => false, // Do not update post meta cache
            'update_post_term_cache' => false, // Do not cache post terms
            'meta_query' => array(
                array(
                    'key'           => 'project_credits_%_user',
                    'value'         => $author_id,
                )
            )
        );
        $post_type_projects_ids = new WP_Query( $args_2 );
        // retrieve *JUST* the IDs
        $post_type_projects_ids = $post_type_projects_ids->posts;

        // It is now just a matter of merging the two arrays of ids
        $combined = array_unique( 
            array_merge( 
                $post_type_post_ids, 
                $post_type_projects_ids 
            ) 
        );

        // We need to make 100% sure we have id's, otherwise post__in will return all posts
        if ( !$combined ) {
            return;
        }

        var_dump($combined);

        // Lets build the query
        $q->set( 'post_type', array('post', 'projects') );
        $q->set( 'post__in', $combined );
    }
}
add_action( 'pre_get_posts', 'author_pre_get_posts' );

var_dump($combined) will give me the IDs of posts and projects and likewise display those posts and projects like I’d expect…but only for User ID 1. For any other author’s author.php page, the only objects that are being shown are posts with the post_type of post — no projects will display for any author/user EXCEPT for the user with User ID 1.

So that’s the only thing left to fix, and I am not sure how to fix it.

Solutions Collecting From Web of "author.php with ACF and CPTs"

REWORKED APPROACH

The issue with the original answer is that although we pass post ID’s to post__in parameter, being on the author page, the main query removes all posts that does not belong to the author. We can pass an empty string to author_name via the pre_get_posts filter, but that inherintly breaks the query object, which is a no-no.

This calls for a new approach, and this is what we will do:

  • We will let the main query run as normal, that is, it will return all posts from the default post type post which was authored by the current author being viewed. That will automatically take care of issue 2. Issue one with displaying the custom fields is handled in the original approach.

    We can use pre_get_posts here if we need to append custom post types to the original main query, but from what I understand, you only need normal posts, so this would not be necessary

  • To sort the second issue where we need only posts from the post type projetcs where the author being viewed is mentioned, we will rather alter the SQL query generated than adding our posts via pre_get_posts

    For this, we will make use of the posts_where filter. We will run a custom query to return all posts’ id’s where the author is mentioned from projects, we will then take those id’s, and alter the SQL query’s WHERE clause to also getthose posts.

    We will still use our custom trigger to trigger the custom query to also alter the generated SQL for the meta_query.

So, for the code, you can remove all code I posted in my original approach, and replace it with our new filter. (Just again, we need PHP 5.4)

add_filter( 'posts_where', 'posts_where_credited', 10, 2 ); 
function posts_where_credited( $where, \WP_Query $q )
{
    // Lets first check our trigger and change the SQL if needed
    if ( true === $q->get( 'wpse_trigger' ) )
        $where = str_replace(
            "meta_key = 'project_credits_%_user",
            "meta_key LIKE 'project_credits_%_user",
            $where
        );

    // Make sure that we target the main query, front end on author pages
    if (    !is_admin()
         && $q->is_main_query()
         && $q->is_author()
    ) {
        // Get the current author
        $author_name = sanitize_title_for_query( $q->get( 'author_name' ) );
        // Just to make sure we actually have a valid uathor name, if not, bail
        if ( !$author_name )
            return $where;

        $author = get_user_by( 
            'slug',
            $author_name
        );
        $author_id = absint( $author->ID );

        // Get the posts in which the author is mentioned from our post type
        $args = [
            'wpse_trigger'           => true, // Our custom trigger
            'post_type'              => 'projects',
            'posts_per_page'         => -1,
            'fields'                 => 'ids',
            'suppress_filters'       => false,
            'cache_results'          => false,
            'update_post_term_cache' => false,
            'update_post_meta_cache' => false,
            'meta_query'             => [
                [
                    'key'   => 'project_credits_%_user',
                    'value' => $author_id
                ]
            ]
        ];
        $post_ids = get_posts( $args );
        // Make sure we have id's, else bail
        if ( !$post_ids )
            return $where;

        $post_ids = implode( ',', array_map( 'absint', $post_ids ) );

        // We have id's, lets adjust the SQL WHERE clauses
        global $wpdb;

        $where .= " OR ( $wpdb->posts.ID IN ( $post_ids ) ) ";
    }
    return $where;
}

ORIGINAL APPROACH

You are very close with your approaches, just a glitch here and there. Lets break all of this down

displays data from Advanced Custom Fields on the user’s Profile Page

This should be straight forward. Unfortunately I have never worked with ACF, so I do not know how the data is stored. However, in short, all you need is the current ID of the author archive page being viewed. You already have that ID available (+1 for using the queried object).

Alternatively, and a much more reliable way to get the queried object will be to make use of the main query object stored in $GLOBALS['wp_the_query']. $GLOBALS['wp_the_query'] is much more reliable than $GLOBALS['wp_query'] as the latter can be altered by crappy functions like query_posts. So in short, in your author archive page, you can use the following

if( !isset($whose_ID) ) {
    $whose_ID = absint( $GLOBALS['wp_the_query']->queried_object_id );
}

You can then use $whose_ID in your get_field() functions to return the ACF data. From your code, it seems that the key has the user prefix, so

$search = 'user_' . $whose_ID;

should do.

displays author’s posts all posts that the user has authored

It is here that all your approaches lack a small thing or two or just need some refinement. Your first approach and last approach should have been merged into one. One thing that we need to keep in mind here is that WP_Query does not natively support the type of query that you are after, so we cannot natively do this in one query, so we will need more than one query. This is turn needs us to be clever as this can get expensive and even break the bank.

What we will be doing here to solve this section is:

  • Use get_posts to get all the post ID’s from the post type post that the author has written. This can be an expensive overhead, so we need to be very clever here. get_posts are faster than WP_Query as it legally breaks paging, but we need to make it even faster as we do not need to break the bank.

    What we will do is, to make it super fast, we will be passing 'fields' => 'ids' to our get_posts query args. This will tell the query to only return an array of post id’s. This makes the query very fast as we do not return any other postdata. We can also make is even faster by telling the query not to cache post terms and meta data. This is all irrelevant stuff that we do not need

So lets look at this query (PLEASE NOTE: All code is untested and requires PHP 5.4+)

$author_name = sanitize_title_for_query( $q->query['author_name'] );

$args_1 = [
    'author_name'            => $author_name, // Get from referenced query
    'posts_per_page'         => -1, // Get all posts
    'cache_results'          => false, // Do not update post cache
    'update_post_meta_cache' => false, // Do not update post meta cache
    'update_post_term_cache' => false, // Do not cache post terms
];
$post_ids_1 = get_posts( $args_1 );

$post_ids_1 will now hold an array of post ids from posts that the author been currently viewed authored from the default post type post

  • all projects (CPT) in which the user is Credited (assigned via an ACF Field for the CPT)

For this section, we will follow the same exact process as above, so I’m not going to go into detail here. Lets look at the code

// This section is also used by WP_Query to get user id
$author = get_user_by( 
    'slug', 
    $author_name 
);
$author_id = absint( $author->ID );

$args_2 = [
    'post_type'              => 'projects',
    'posts_per_page'         => -1, // Get all posts
    'cache_results'          => false, // Do not update post cache
    'update_post_meta_cache' => false, // Do not update post meta cache
    'update_post_term_cache' => false, // Do not cache post terms
    'meta_query' => [
        [
            'key'   => 'project_credits_%_user',
            'value' => $author_id
        ]
    ]
];
$post_ids_2 = get_posts( $args_2 );

You now have all the post ids where the author was mentioned from post type projects

Now that we have all relevant id’s, it is time to put everything together in our pre_get_posts action

add_action( 'pre_get_posts', function ( $q )
{
    // Remove action
    remove_action( current_action(), __FUNCTION__ );

    // Only target the main query, front end only on author archive pages
    if (    !is_admin()
         && $q->is_main_query()
         && $q->is_author()
    ) {
        // Now we can run our custom queries to get post ID's

        $author_name = sanitize_title_for_query( $q->query['author_name'] );

        $args_1 = [
            'author_name'            => $author_name, // Get from referenced query
            'posts_per_page'         => -1, // Get all posts
            'cache_results'          => false, // Do not update post cache
            'update_post_meta_cache' => false, // Do not update post meta cache
            'update_post_term_cache' => false, // Do not cache post terms
        ];
        $post_ids_1 = get_posts( $args_1 );

        // This section is also used by WP_Query to get user id
        $author = get_user_by( 
            'slug', 
            $author_name 
        );
        $author_id = absint( $author->ID );

        $args_2 = [
            'post_type'              => 'projects',
            'posts_per_page'         => -1, // Get all posts
            'cache_results'          => false, // Do not update post cache
            'update_post_meta_cache' => false, // Do not update post meta cache
            'update_post_term_cache' => false, // Do not cache post terms
            'meta_query' => [
                [
                    'key'   => 'project_credits_%_user',
                    'value' => $author_id
                ]
            ]
        ];
        $post_ids_2 = get_posts( $args_2 );

        // It is now just a matter of merging the two arrays of ids
        $combined = array_unique( 
            array_merge( 
                $post_ids_1, 
                $post_ids_2 
            ) 
        );

        // We need to make 100% sure we have id's, otherwise post__in will return all posts
        if ( !$combined )
            return;

        // Lets build the query
        $q->set( 'post_type', ['post', 'projects'] );
        $q->set( 'post__in',  $combined            );
    }
});

You can display your posts normally on your author archive page with the default loop. Just a note, according to the site’s syntax highlighter, there seems to be a syntax error on the code in your author.php

EDIT

I had a look at your edit, and there are a couple of issues, but it does not necessarily means this will solve the issue

  • You do not need to call wp_reset_postdata() and wp_reset_query(). The latter is only used with query_posts() which you should never ever use

  • get_posts() does accept to be altered by filters, but this is not default behavior. get_posts() passes 'suppress_filters' => true to WP_Query by default which suppress filters altering the query. You can override this by simply passing 'suppress_filters' => false to you get_posts arguments. This will allow filters to alter the query

  • By default, any of the filters in WP_Query will alter all instances of WP_Query (which includes the main query). It is always a good idea to use some kind of trigger to trigger a filter as to target only the relevant query. I would definitely do this with your posts_where filter.

    What we can do is, lets call our trigger wpse_trigger. We can pass 'wpse_trigger' => true to our query argument of the query we would want to target with our filter. All we need to do now is to check inside our filter if our trigger is set and if it is set to true. Remember, the current query is passed by reference to the filter as second parameter.

    Lets look at the code

    function posts_where_credited( $where, \WP_Query $q ) 
    {
        // Lets remove the filter
        remove_filter( current_filter(), __FUNCTION__ );
    
        // Lets see if our trigger is set and if the value is true, if not, bail
        if ( true !== $q->get( 'wpse_trigger' ) )
            return $where
    
        // Our trigger is set and true, lets alter this query
        $where = str_replace(
            "meta_key = 'project_credits_%_user", 
            "meta_key LIKE 'project_credits_%_user", 
            $where
        );
    
        return $where;
    }
    add_filter( 'posts_where' , 'posts_where_credited', 10, 2 );
    

    Your query arguments for your second get_posts instance where you make the meta query can now look something like this:

    $args_2 = [
        'wpse_trigger'    => true,
        'suppress_filter' => false,
        // Rest of your query args
    ];
    
  • I do not know if your filter actually works correctly with the meta_key LIKE, but you can have a look at this post for some alternatives if it actually is query two that fails

What is quite strange is that everything works for author 1, but not for ny other author. Here is a few things you can check

  • Keep your WP_Query instance for the second query inside your pre_get_posts action. Then after that query (right after $post_type_projects_ids = new WP_Query();), do var_dump( $post_type_projects_ids->request ); and load the author page. This will print the SQL query on top of your author page. Check if resultant SQL query matches on all other author pages and that of author 1. If they differ, you have an issue somewhere in a plugin or in the theme with a bad filter (like posts_where or posts_clauses), action (bad pre_get_posts) or an issue with capabilities of your custom post type. Note, you have swith to WP_Query as this will not work with get_posts

  • If the SQL queries checks out, then do var_dump( $post_type_projects_ids->posts ); and check if you actually have posts that is returned from the query. This will just be an array with ids if there are posts

  • If the above checks out, go to your author page, and anywhere on that page, add var_dump( $wp_query->request );. This will print the SQL query generated by the main query. Again, because author 1 works, compare that SQL query with the SQL query of other author pages. Except for post and author id’s, the queries should match. If not, refer to bullet point one for debugging