Custom Queries: Joining On Meta Values From Two Custom Post Types

Question

Hi – I'm hoping this is the right place to be asking this question.

I am in the process of moving my full football club website onto Wordpress, therefore using a single database for the information I hold. I'm doing this (for context) to make things easier in the long-run.

Before, there was a database table for Opponents, Matches, Players etc. Now I have Custom Post Types for these. Each entry in these tables has a unique ID that could be referenced in other tables where appropriate: Opponent ID, Match ID, Player ID and so on.

In a Match page, for example, I'd extract and output data from the Opponent and Player tables with joins - a basic example of which would be:

$query = "SELECT me.date, me.opponent, me.score, ce.name, ce.ID, me.ID, ce.badge FROM  
matchengine me RIGHT JOIN clubengine ce ON me.opponent=ce.ID WHERE me.ID = ".$id."";

From this, I get

me.date      ce.name (from ID)    me.score
==========================================
01/01/1900   Team Name United     2-1

instead of

me.date      ce.id                me.score
==========================================
01/01/1900   200                  2-1

What I haven't yet been able to do is to replicate this in Wordpress. I can output info from a single post type i.e. Match, but not yet found the way to extract the relevant data from another post type too (if there is one). My instinct is joining two post types on a shared meta_value – or meta_key?

EDIT (11 August): What I've done is taken @SallyCJ's advice and made the Opponent field a Post Object (finally finding out how to import data from CSV as this!). Using the following code within the query calling the 'Match' posts, I am able to output the team name and not just three digit ID code. I'm sure it's rough and ready in Wordpress circles, but it does the job for me.

$opposition = get_field('club');
if($opposition) echo $opposition->post_title;

Answer 1

So based on our discussion, the meta named opposition (for match posts) has its value set to the slug of the corresponding club post; e.g. club-1, club-two, etc., right?

If so, then you first get the ID of the club post using a custom query (see in the code below) and then you can access any data you want from the club post such as the post title and content. Just make sure to always check if the post ID (identified by $club_id in the code below) is greater than 0 (zero), because otherwise the data you get would be for the current post in the foreach loop, which is for the current match post and not the club post associated with that match post. (I hope you can understand this..)

if ( $latestresult )
{
    foreach ( $latestresult as $post )
    {
        setup_postdata( $post );

        // Get the ID of the `club` post associated with the current `match` post.
        $club_id = $wpdb->get_var( $wpdb->prepare( "
            SELECT ID
            FROM $wpdb->posts
            WHERE post_name = %s
            AND post_type = 'club'
        ", get_field( 'opposition', $post->ID ) ) );

        // Don't use `get_the_title()` with a zero value; so check if $club_id > 0
        $player_name = $club_id > 0 ? get_the_title( $club_id ) : 'N/A';
    ?>
    ...
    <div class="result-info">       
    <a href="<?php the_permalink(); ?>">
    ...
    <span class="result-column"><?php echo $player_name; ?></span>
    ... 
    <span class="thumbnail">
        <?php echo get_the_post_thumbnail( $club_id, 'thumbnail' ); ?>
    </span>
    </a>
    </div>
    <?php
    }   
}

However, if possible, you should probably better change the ACF field type to Post Object, where the meta value would be the ID of the club post and not its slug. But for that, make sure to set the "Allow Null" to "Yes", and "Select multiple values" to "No" — see this image.

And if you can do that, then the code would be simpler:

if ( $latestresult )
{
    foreach ( $latestresult as $post )
    {
        setup_postdata( $post );

        // Get the ID of the `club` post associated with the current `match` post.
        // Set the third argument to `false` to get the post ID instead of object.
        $club_id = get_field( 'opposition', $post->ID, false );

        // Don't use `get_the_title()` with a zero value; so check if $club_id > 0
        $player_name = $club_id > 0 ? get_the_title( $club_id ) : 'N/A';
    ?>
    ...
    <div class="result-info">       
    <a href="<?php the_permalink(); ?>">
    ...
    <span class="result-column"><?php echo $player_name; ?></span>
    ... 
    <span class="thumbnail">
        <?php echo get_the_post_thumbnail( $club_id, 'thumbnail' ); ?>
    </span>
    </a>
    </div>
    <?php
    }   
}

Additional Note

Although this code works:

$latestresult = $wpdb->get_results( 
"SELECT * FROM $wpdb->wp_posts
WHERE p.post_type = 'match'
ORDER BY post_date DESC
LIMIT 2"
);

I did it using get_posts():

$latestresult = get_posts( [
    'post_type'      => 'match',
    'orderby'        => 'date',
    'order'          => 'DESC',
    'posts_per_page' => 2, // LIMIT
] );

Because that's how we're supposed to do it in WordPress. =) (The other way would be using new WP_Query( [ ... ] ).)