Query that joins the postmeta table twice

Question

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

Answer 1

"My SQL doesn't work" questions are difficult because they are nearly untestable without having the OP's dataset, but...

I think you may be making this too complicated.

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 IN ('team1','team2')
  AND pm2.meta_value =  'yankees'
)

I think that may do what you need. And yes, I am pretty sure you can't do that with WP_Query directly, but...

function swap_key_wpse_144950($where) {
  remove_filter('posts_where','swap_key_wpse_144950');
  $where = str_replace("= 'xxteamsxx'"," IN ('team1','team2')",$where);
  return $where;
}
add_filter('posts_where','swap_key_wpse_144950');

$args = array(
  'meta_query' => array(
    array(
      'key' => 'year',
      'value' => '2014'
    ),
    array(
      'key' => 'xxteamsxx',
      'value' => 'yankees'
    )
  )
);
$q = new WP_Query($args);
var_dump($q->request);

Answer 2

Could you try to write this query as

SELECT DISTINCT (p.ID), p.post_title
FROM wp_posts p
left outer join wp_postmeta pm1 ON p.ID = pm1.post_id AND pm1.meta_key = 'team1' and pm1.meta_value =  'yankees'
left outer join wp_postmeta pm2 ON p.ID = pm2.post_id  and pm2.meta_key =  'team2'  and pm2.meta_value =  'yankees'
left outer join wp_postmeta pm on p.ID= pm.post_id and pm.meta_key = 'year' and pm.meta_value = '2014'
WHERE p.post_type =  'game'

You don't need to put the condition where 1=1 as you have plenty other where clauses (don't need a fake one). Let me know the result

EDIT

What about this one

SELECT DISTINCT (p.ID), p.post_title
FROM wp_posts p
left outer join wp_postmeta pm1 ON p.ID = pm1.post_id AND pm1.meta_key = 'team1' 
left outer join wp_postmeta pm2 ON p.ID = pm2.post_id  and pm2.meta_key =  'team2'  
left outer join wp_postmeta pm on p.ID= pm.post_id and pm.meta_key = 'year' 
WHERE p.post_type =  'game'  and pm.meta_value = '2014' and (pm1.meta_value = 'yankees' or pm2.meta_value = 'yankees')