1

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

"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);
1

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')
  • I ran this query, and it returned ALL rows, not the rows specific to the year/team parameters. – RailsTweeter May 20 '14 at 18:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.