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
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;