Get array of posts where a specified Post ID is used in an ACF Pro Repeater Field

Question

I'm not sure if this is possible, but someone might be able to tell me for sure.

This is a situation that could be solved by a linking table if I was making a bespoke solution.

In a typical post, I have a repeater field where I list books. The books are saved as a custom post type, so you might have a page with 10 recommended/related books. Some books are referenced in multiple posts. Let's say you've got a post about John Grisham and a post about legal thrillers, they might both include The Pelican Brief. So far so good.

But what if I want to reverse the query?

When I go to the single.php page for a book, is there a way to list the posts that have referenced it? Can my page about The Pelican Brief show that it has been included on the post about John Grisham and the post about legal thrillers? Or am I asking for the impossible?!

If this is possible, then I don't know where to begin.

Answer 1

Assume that you have the repeater created with ACF PRO with the key recommended_books, and then for each repeater, you have a field for Book ID called book_id, so in the single.php (it should be single-book.php if you want to use it specifically for the post type book) file of book, you can do something like this to get the IDs of author where the current book is referenced.

global $wpdb;

$book_id = get_the_ID(); // Assign the ID of the current book;
$authors = $wpdb->get_results("SELECT {$wpdb->prefix}postmeta.post_id FROM {$wpdb->prefix}postmeta
    INNER JOIN {$wpdb->prefix}posts
    WHERE {$wpdb->prefix}postmeta.post_id = {$wpdb->prefix}posts.ID
       AND {$wpdb->prefix}posts.post_type = 'author'
       AND meta_key LIKE 'recommended_books_%_book_id' AND meta_value={$book_id}");

if($authors) {
    foreach ($authors as $author) {
        $author_id = $author->post_id;
        // Do your stuff to display the author
    }
}

Please remember to update the key recommended_books, book_id and the post type author if you have them setup differently.

Now with the array of post_id you get, you can display the list of author however you want.