WordPress.org

Make WordPress Core

#21617 closed defect (bug) (duplicate)

Sorting on meta_key breaks meta query with 'OR' relation

Reported by: Tomauger Owned by:
Milestone: Priority: normal
Severity: normal Version:
Component: General Keywords:
Focuses: Cc:

Description

"Breaks" may have been a harsh term here. Things are working as they are supposed to, but there is room for improvement:

$movies = new WP_Query( array(
	'meta_query' => array (
		'relation' => 'OR',

		array(
			'key' => 'Year',
			'value' => 2000,
			'type' => 'numeric',
			'compare' => '>='
		),

		array(
			'key' => 'Rating',
			'value' => 10,
			'type' => 'numeric',
			'compare' => '='
		)
	),

	'meta_key' => 'Rating',
	'orderby' => 'meta_value_num',

	'posts_per_page' => -1
) );

Here, the desired outcome is to show movies from 2000 onward, but to also include any other movies that have a rating of '10'.

We currently need to specify the meta_key again, outside the meta_query in order to get the 'orderby' to work. Unfortunately this has the result of blowing away the meta query AND it seems to also not respect the order_by.

Here's the generated SQL:

SELECT wp_posts.* 
FROM wp_posts 

INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) 
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) 
INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id) 

WHERE 1=1 AND wp_posts.post_type = 'post' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
AND (wp_postmeta.meta_key = 'Rating' 
  OR (mt1.meta_key = 'Year' 
    AND CAST(mt1.meta_value AS SIGNED) >= '2000') 
  OR (mt2.meta_key = 'Rating' 
    AND CAST(mt2.meta_value AS SIGNED) = '10') 
) 
GROUP BY wp_posts.ID 
ORDER BY wp_postmeta.meta_value+0 DESC 

Note the wp_postmeta.meta_key = "Rating" OR - is what's causing most of the ruckus (though the sort is still broken too, wondering about the +0, though at first glance that appears to be OK).

This is probably a duplicate, but I can't seem to find the original thread about sorting and post meta.

Change History (1)

comment:1 scribu20 months ago

  • Milestone Awaiting Review deleted
  • Resolution set to duplicate
  • Status changed from new to closed
Note: See TracTickets for help on using tickets.