get_posts() expects invalid 'orderby' arguments + suggestion

[abrcam]

The get_children() or get_posts() template functions which call the WP_query::get_posts() method expect wrong parameters, for example, date. The actual column is called post_date but line 2320 of wp-includes/query.php is:

			$allowed_keys = array('author', 'date', 'title', 'modified', 'menu_order', 'parent', 'ID', 'rand', 'comment_count');

and further down at line 2329 there is:

			$orderby_array = array();
			foreach ( explode( ' ', $q['orderby'] ) as $i => $orderby ) {
				// Only allow certain values for safety
				if ( ! in_array($orderby, $allowed_keys) )
					continue;

The same goes for author, title, modified, etc. Some of them are correct though: ID, menu_order, comment_count.

If get_posts() is called without specifying any orderby argument, then it works fine since the default is correct in line 2314:

		if ( empty($q['orderby']) ) {
			$orderby = "$wpdb->posts.post_date " . $q['order'];

but if the orderby argument is specified and if it includes post_date or other correct column names, then they are ignored, e.g.:

	$images = get_children(array(
		'post_type' => 'attachment',
		'post_mime_type' => 'image',
		'post_parent' => get_the_ID(),
		'orderby' => 'menu_order post_date',
		'order' => 'ASC',
		), ARRAY_A);

will result in a query that is missing post_date since it gets ignored by line 2332, and yields unexpected results. The resulting wrong query at line 2623 is:

" SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_parent = 172  AND (post_mime_type LIKE 'image/%')  AND wp_posts.post_type = 'attachment' AND (wp_posts.post_status <> 'trash' AND wp_posts.post_status <> 'auto-draft')  ORDER BY menu_order ASC "

Note the missing post_date at the end.

Fix

Rename date to post_date in line 2320.

Suggestion

Allow passing different order values for different orderby columns, e.g. ORDER BY post_date ASC, menu_order ASC. Right now, the same order is applied to all orderby columns.

This can be easily implemented without breaking backward compatibility, by explode()'ing the order string using spaces so that the user can use, e.g.:

	$images = get_children(array(
		'orderby' => 'menu_order post_date',
		'order' => 'ASC DESC',
		), ARRAY_A);

in which ASC applies to menu_order and DESC to post_date.

Cheers,
Bogdan.

p.s. of course, all wrong orderby names should be renamed in line 2320, not just date.

I don't see how this is a bug given that the current orderby parameters are well documented: ​http://codex.wordpress.org/Class_Reference/WP_Query#Order_.26_Orderby_Parameters

Most orderby parameters have the prefix added later (lines 2366-7 on trunk):

default:
	$orderby = "$wpdb->posts.post_" . $orderby;

Renaming them would result in a massive backcompat headache as well. Maybe I'm missing something here?

Replying to abrcam:

Fix

Rename date to post_date in line 2320.

As kawauso pointed out, the prefix is added later:
​http://core.trac.wordpress.org/browser/tags/3.2.1/wp-includes/query.php#L2354

'orderby' => 'menu_order post_date' in your code should be 'orderby' => 'menu_order date', so this part of the ticket is invalid.

Suggestion

Allow passing different order values for different orderby columns, e.g. ORDER BY post_date ASC, menu_order ASC. Right now, the same order is applied to all orderby columns.

This part is a duplicate of #17065.

My apologies! I only now saw that the error is in the wrong usage of post_date instead of just date in the 'orderby' array key. Sorry for wasting your time!