WordPress.org

Make WordPress Core

Opened 5 years ago

Closed 5 years ago

#29604 closed defect (bug) (duplicate)

Meta query OR and meta key sort generate incorrect WHERE clause

Reported by: Pwhitehurst Owned by:
Milestone: Priority: normal
Severity: normal Version: 4.0
Component: Query Keywords:
Focuses: Cc:

Description (last modified by boonebgorges)

I have the following arguments

$args3 = array(
    'post_type' => 'match',
    'orderby' => 'meta_value',
    'meta_key' => 'match_date',
    'order' => 'ASC',
    'meta_query' => array(           
        array(
            'key'     => 'home_team',
            'value'   => $teamID,
            'compare' => '=' ,                                
        ),
        'relation' => 'OR',
         array(
            'key'     => 'away_team',
            'value'   => $teamID,
            'compare' => '=' ,                                
        ),
    ),
);

Generates the following SQL which incorrectly OR's the match_date with home_team and away_team

REQUEST:SELECT SQL_CALC_FOUND_ROWS deece_posts.ID FROM deece_posts INNER JOIN deece_postmeta ON deece_posts.ID = deece_postmeta.post_id
INNER JOIN deece_postmeta AS mt1 ON (deece_posts.ID = mt1.post_id)
INNER JOIN deece_postmeta AS mt2 ON (deece_posts.ID = mt2.post_id) WHERE 1=1 AND deece_posts.post_type = 'match' AND (deece_posts.post_status = 'publish' OR deece_posts.post_status = 'private') AND (deece_postmeta.meta_key = 'match_date'
OR (mt1.meta_key = 'home_team' AND CAST(mt1.meta_value AS CHAR) = '120')
OR (mt2.meta_key = 'away_team' AND CAST(mt2.meta_value AS CHAR) = '120') ) GROUP BY deece_posts.ID ORDER BY deece_postmeta.meta_value ASC LIMIT 0, 10

It should generate the following SQL with an additional AND and parentheses around the OR condition in the where , as below.

REQUEST:SELECT SQL_CALC_FOUND_ROWS deece_posts.ID FROM deece_posts INNER JOIN deece_postmeta ON deece_posts.ID = deece_postmeta.post_id
INNER JOIN deece_postmeta AS mt1 ON (deece_posts.ID = mt1.post_id)
INNER JOIN deece_postmeta AS mt2 ON (deece_posts.ID = mt2.post_id) WHERE 1=1 AND deece_posts.post_type = 'match' AND (deece_posts.post_status = 'publish' OR deece_posts.post_status = 'private') AND (deece_postmeta.meta_key = 'match_date' AND
( (mt1.meta_key = 'home_team' AND CAST(mt1.meta_value AS CHAR) = '120')
OR (mt2.meta_key = 'away_team' AND CAST(mt2.meta_value AS CHAR) = '120')) ) GROUP BY deece_posts.ID ORDER BY deece_postmeta.meta_value ASC LIMIT 0, 10

Change History (2)

#1 @boonebgorges
5 years ago

  • Description modified (diff)

#2 @boonebgorges
5 years ago

  • Milestone Awaiting Review deleted
  • Resolution set to duplicate
  • Status changed from new to closed

Duplicate of #25538.

Note: See TracTickets for help on using tickets.