I believe what I'm trying to do is too complex for the standard
meta_query parameter in
get_posts, so I'm trying to use a custom SQL query to do the following:
I have a game custom post that has custom fields for team1 and team2 stored as postmeta. Given team XXXX, I need to pull all posts that have the postmeta value of XXXX for either postmeta key team1 or postmeta key team2.
I also need to factor in the year, because 2 teams can play eachother every year. The year for the game post type is stored as postmeta year.
I tried using the following (testing in phpmyadmin) and it's not working...
My two variables here would be year=2014 and team=yankees:
SELECT DISTINCT (p.ID), p.post_title FROM wp_posts p JOIN wp_postmeta pm1 ON p.ID = pm1.post_id JOIN wp_postmeta pm2 ON p.ID = pm2.post_id WHERE 1 =1 AND p.post_type = 'game' AND ((pm1.meta_key = 'year' AND pm1.meta_value = '2014' AND pm2.meta_key = 'team1' AND pm2.meta_value = 'yankees') OR (pm1.meta_key = 'year' AND pm1.meta_value = '2014' AND pm2.meta_key = 'team2' AND pm2.meta_value = 'yankees'))