WP Query—Relationship between two custom post types and their fields

Question

I'm using Advanced Custom Fields and Genesis and trying to modify a template to reflect a relationship between two custom post types (using their associated custom fields.) In this case, I have custom post types for "Staff" and "Reports", and for staff, I have the custom fields "first_name" and "last_name"; and associated with the "Reports" CPT, I also have two custom fields "primary_contact" and "associated_contact"— and in the "single-staff.php" template, I'd like to render a list of any associated reports in the sidebar that would include entries (post titles) for which that staff member is defined as either a "primary_contact" or an "associated_contact."

Thus, for example, if I've created "Tom Jones" as a staff member (an instance of the "Staff" post type), is it possible for me to define a query that outputs a list of reports for which "Tom Jones" is defined as a "primary_contact" and/or "associated_contact" in any instances of the "Reports" custom post type? I'm including a (very) rough concept for this query below:

$args = array(
'meta_query' => array(
        'relation' => 'OR',
        array(
            'post_type' => 'reports',
            'meta_key' => 'primary_contact',
            'meta_value' => '$this.full_name',
        ),
        array(
            'post_type' => 'reports',
            'meta_key' => 'associated_contact',
            'meta_value' => '$this.full_name',
        )
    )
);

$query = new WP_Query( $args );

Please let me know if the question is not sufficiently clear-- and thank you for any insight!

Answer 1

I haven't tested this but it SHOULD work. You'll want to put it in the single-staff.php. You'll have to add the details (actual query, post types, etc).

$reports = get_posts(array(
                            'post_type' => 'reports',  //use actual post type
                            'meta_query' => array(
                                'relation' => 'or',
                                array(
                                    'key' => 'primary_contact', // name of custom field
                                    'value' => '"' . get_the_ID() . '"', // matches exaclty "123", not just 123. This prevents a match for "1234"
                                    'compare' => 'LIKE'
                                )
                                array(
                                    'key' => 'associated_contact', // name of custom field
                                    'value' => '"' . get_the_ID() . '"', // matches exaclty "123", not just 123. This prevents a match for "1234"
                                    'compare' => 'LIKE'
                                )
                            )
                        ));

                        ?>
                        <?php if( $reports ): ?>
                            <ul>
                            <?php foreach( $reports as $report ): ?>
                                <li>
                                    <?php echo get_the_title( $doctor->ID ); ?>
                                </li>
                            <?php endforeach; ?>
                            </ul>
                        <?php endif; ?>

Answer 2

Another option: there's an ACF add-on called Bidirectional Relationships that may simplify this for you. You define relationships in ACF, and then when you're editing either post type, you'll see a box that lets you search or select from a list the related content. You can then use that info in your templates however you like.