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'))