WordPress.org

Make WordPress Core

Opened 3 years ago

#40327 new defect (bug)

Wrong SQL request for 'EXIST' OR 'NOT EXIST' on same post meta

Reported by: solo14000 Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version: 4.7
Component: Query Keywords:
Focuses: Cc:
PR Number:

Description

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:

[
// 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:

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:

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.

Change History (0)

Note: See TracTickets for help on using tickets.