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

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?

From extensive searching, I’ve tried to combine a range of solutions from similar-ish questions on this. But now I’m going round in circles as I’m unsure how the join should be made and how I’m to go about building the custom query in the first place. I’ve really got no further than:

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

if ( $latestresult )
{
foreach ( $latestresult as $post )
{
setup_postdata( $post );
?>
<h4 class=”result-date”><?php echo get_the_date(‘l d F’); ?></h4>
<div class=”result-info”>
<a href=”<?php the_permalink(); ?>”>
<span class=”result-column”><?php the_field(‘venue’); ?></span>
<span class=”result-column”><?php the_field(‘competition’); ?></span>
<span class=”result-column”><?php the_field(‘score’); ?></span>
<span class=”result-column”>
<span class=”score-box”>
<span class=”score-main”><?php the_field(‘score’); ?></span>
</span>
</span>
</a>
</div>
<?php
}
}

This outputs the last two match results as desired, though with the team code – not name, which would come from another post type. When I add a join to the query, nothing is outputted. I’ve tried:

INNER JOIN wp_postmeta pm ON p.post_id = pm.post_id

…and joining on the two custom fields direct, but get the feeling I’m trying to make too great a leap between where I’m at with my understanding and what I’m trying to achieve. Is this possible? If so, how far away am I from getting there? Other articles I’ve used for reference include:

Select User by Joining Multiple Meta Value Results
https://stackoverflow.com/questions/15169010/sql-query-for-custom-post-type-and-multiple-custom-fields?lq=1
Query that joins the postmeta table twice

Read more here:: Custom Queries: Joining On Meta Values From Two Custom Post Types

Leave a Reply

Your email address will not be published. Required fields are marked *