[wp-trac] [WordPress Trac] #40327: Wrong SQL request for 'EXIST' OR 'NOT EXIST' on same post meta

[WordPress Trac]

#40327: Wrong SQL request for 'EXIST' OR 'NOT EXIST' on same post meta
--------------------------+-----------------------------
 Reporter:  solo14000     |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Query         |    Version:  4.7
 Severity:  normal        |   Keywords:
  Focuses:                |
--------------------------+-----------------------------
 Hi,

 I want to make a query on a custom post type (event post type) with custom
 post status (on air, forthcoming...) joining optional meta (event rating)
 then order by decreasing rating, defaulting to increasing title.

 I'm using 'new WP_Query()' way as secondary loop with the following WP
 query parameters:

 {{{#!php
 [
 // Valid events only
 'post_type'     => My_Post_Type::POST_TYPE_EVENT,
 'post_status'   => [
         My_Post_Type::EVENT_STATUS_ON_AIR,
         My_Post_Type::EVENT_STATUS_FORTHCOMING,
 ],
 // Join optional rating
 'meta_query'    => [
         'relation'=> 'OR',
         [
                 'key' => My_Post_Type::EVENT_ATTR_RATING,
                 'compare'=> 'EXISTS',
         ],
         [
                 'key' => My_Post_Type::EVENT_ATTR_RATING,
                 'compare' => 'NOT EXISTS',
         ],
 ],
 // Order by rating then title
 'orderby'       => [
         My_Post_Type::EVENT_ATTR_RATING => 'DESC',
         'post_title' => 'ASC',
                 ],
 // Limit number
 'posts_per_page' => self::NB_HOME_COMING_EVENTS,
 ]
 }}}

 Resulting SQL query is:

 {{{#!php
 SELECT SQL_CALC_FOUND_ROWS  cq_posts.* FROM cq_posts
 LEFT JOIN cq_postmeta ON ( cq_posts.ID = cq_postmeta.post_id )
 LEFT JOIN cq_postmeta AS mt1 ON (cq_posts.ID = mt1.post_id AND
 mt1.meta_key = 'ev_rating' )
 WHERE 1=1  AND (
   cq_postmeta.meta_key = 'ev_rating'
   OR
   mt1.post_id IS NULL
 ) AND cq_posts.post_type = 'event' AND ((cq_posts.post_status =
 'ev_on_air' OR cq_posts.post_status = 'ev_coming'))
 GROUP BY cq_posts.ID
 ORDER BY cq_postmeta.meta_value+0 DESC, cq_posts.post_title ASC
 LIMIT 0, 6
 }}}

 The result set is not what I was looking for (in fact has no sense at all
 for me)

 The right SQL query should be something like this:

 {{{#!php
 SELECT SQL_CALC_FOUND_ROWS  cq_posts.* FROM cq_posts
 LEFT JOIN cq_postmeta ON ( cq_posts.ID = cq_postmeta.post_id AND
 cq_postmeta.meta_key = 'ev_rating' )
 WHERE 1=1  AND cq_posts.post_type = 'event' AND ((cq_posts.post_status =
 'ev_on_air' OR cq_posts.post_status = 'ev_coming'))
 GROUP BY cq_posts.ID
 ORDER BY cq_postmeta.meta_value+0 DESC, cq_posts.post_title ASC
 LIMIT 0, 6
 }}}

 Only one LEFT JOIN is required.

 Can anyone confirm it is a bug or tell me what WP query parameters I must
 use instead ?

 Thanks in advance.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/40327>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform