orderby post__in interferes with menu_order

[Matthias82]

Hi,

with Version 3.9 the following issue came up.

$query_pages = array(3,9,6,2,10);

$pagequery = array(
'posts_per_page' => count($query_pages),
'post_type' => 'page',
'post__in' => $query_pages,
'orderby'=> 'post__in');

query_posts($pagequery);

When running through the loop, all posts on my front end have the same order as they have inside the $query_pages array. But once a post has a menu_order value like 1 or 2 it gets forced to the end of the loop and the order is distorted.

Page Structure inside Dashboard:
-- Page ( ID 3 )
--- child of Page 3 with ( ID 6 and menu order 2 )
--- child of Page 3 with ( ID 9 and menu order 1 )
-- Page ( ID 2 )
-- Page ( ID 10 )

Expected Output by using the above code:
-- Page ( ID 3 )
-- Page ( ID 9 )
-- Page ( ID 6 )
-- Page ( ID 2 )
-- Page ( ID 10 )

Generated Output
-- Page ( ID 3 )
-- Page ( ID 2 )
-- Page ( ID 10 )
-- Page ( ID 9 )
-- Page ( ID 6 )

[wonderboymusic]

Matthias82 - thanks for the report. I will make a unit test out of your example. If confirmed, this needs to be fixed.

[haxxxton]

I am having a similar problem since upgrading to 3.9, but even just with top level posts (ie. not nested).
Here is my code:

    $icons = array(310, 230, 272, 247, 234);
    
    echo '<pre>';
    print_r($icons);
    echo '</pre>';

    $attached_images = new WP_Query(array(
        'post__in' => $icons,
        'post_type' => 'attachment',
        'post_parent' => $post->ID,
        'post_mime_type' => 'image',
        'post_status' => 'inherit',
        'posts_per_page' => '-1',
        'orderby' => 'post__in'
    ));

    echo '<ul><li>ID - menu_order</li>';
    foreach($attached_images->posts as $icon){
        echo '<li>'.$icon->ID.' - '.$icons->menu_order.'</li>';
    }
    echo '</ul>';

expected output:

Array
(
    [0] => 310
    [1] => 230
    [2] => 272
    [3] => 247
    [4] => 234
)

ID - menu_order
310 - 79
230 - 100
272 - 42
247 - 17
234 - 4

generated output:

Array
(
    [0] => 310
    [1] => 230
    [2] => 272
    [3] => 247
    [4] => 234
)

ID - menu_order
234 - 4
247 - 17
272 - 42
310 - 79
230 - 100

[lukecarbis]

I've tested this on 1.3.9 and 1.4.0, and I can't reproduce the problem on either.

Here's what I tried:

$query_pages = array( 4, 6, 8, 2 );

$pages = new WP_Query(
	array(
		'posts_per_page' => count( $query_pages ),
		'post_type'      => 'page',
		'post__in'       => $query_pages,
	    'orderby'        => 'post__in',
	)
);

echo '<ul>';
foreach( $pages->posts as $page ) {
    echo '<li>Page ID: ' . $page->ID . ' - Order: ' . $page->menu_order . '</li>';
}
echo '</ul>';

Here's the expected and generated output:

Page ID: 4 - Order: 22
Page ID: 6 - Order: 0
Page ID: 8 - Order: 0
Page ID: 2 - Order: 4

Here's the query that is generated (taken from the posts_request filter with a priority of 100). It looks right to me:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.ID IN (4,6,8,2) AND wp_posts.post_type = 'page' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'future' OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' OR wp_posts.post_status = 'private')  ORDER BY FIELD( wp_posts.ID, 4,6,8,2 ) LIMIT 0, 4

Can you try to reproduce this problem on a new clean install?

[DeBAAT]

Might this have to do with the fact that variable $post__in is only set in the specific case where $q['p'] is not set and $q['post__in'] is?
See the following code, starting at line 2441 of query.php:

// If a post number is specified, load that post
if ( $q['p'] ) {
	$where .= " AND {$wpdb->posts}.ID = " . $q['p'];
} elseif ( $q['post__in'] ) {
	$post__in = implode(',', array_map( 'absint', $q['post__in'] ));
	$where .= " AND {$wpdb->posts}.ID IN ($post__in)";
} elseif ( $q['post__not_in'] ) {
	$post__not_in = implode(',',  array_map( 'absint', $q['post__not_in'] ));
	$where .= " AND {$wpdb->posts}.ID NOT IN ($post__not_in)";
}

Because if this variable is not set, it will not be used in the code, starting on line 2614:

// Order by
if ( empty($q['orderby']) ) {
	$orderby = "$wpdb->posts.post_date " . $q['order'];
} elseif ( 'none' == $q['orderby'] ) {
	$orderby = '';
} elseif ( $q['orderby'] == 'post__in' && ! empty( $post__in ) ) {
	$orderby = "FIELD( {$wpdb->posts}.ID, $post__in )";
} elseif ( $q['orderby'] == 'post_parent__in' && ! empty( $post_parent__in ) ) {
	$orderby = "FIELD( {$wpdb->posts}.post_parent, $post_parent__in )";
} else {
	// Used to filter values

[wonderboymusic]

I just committed some unit tests: [28619].

I can't reproduce this in trunk. Please test against trunk and let me know if the problem persists.

[McGuive7]

I'm experiencing the same issue, however in my case I believe it's coming from a page/post ordering plugin that overrides the query order_by parameter. Might this be the case for any of y'all?

[McGuive7]

Hmmm, scratch that. I'm having the same issue. Here's my query:

$query_args = array(
	'posts_per_page'      => count( $post_in_array ),
	'post_type'           => 'campaign-spotlight',
	'order_by'            => 'post__in',
	'order'               => 'ASC',
	'post__in'            => $post_in_array,
	'ignore_sticky_posts' => true,
);


// Run query
$the_query = new WP_Query( $query_args );

And here's what the SQL command ends up looking like:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.ID IN (1973,1979,1976) AND wp_posts.post_type = 'campaign-spotlight' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')  ORDER BY wp_posts.post_date ASC LIMIT 0, 3

No idea why it's still ordering by post date. Any help?

Replying to McGuive7:

I'm experiencing the same issue, however in my case I believe it's coming from a page/post ordering plugin that overrides the query order_by parameter. Might this be the case for any of y'all?

[McGuive7]

PS this needs to be re-opened I think.

[McGuive7]

I'm a fool! Was using order_by instead of orderby. You're welcome - derp.

Replying to McGuive7:

Hmmm, scratch that. I'm having the same issue. Here's my query:

$query_args = array(
	'posts_per_page'      => count( $post_in_array ),
	'post_type'           => 'campaign-spotlight',
	'order_by'            => 'post__in',
	'order'               => 'ASC',
	'post__in'            => $post_in_array,
	'ignore_sticky_posts' => true,
);


// Run query
$the_query = new WP_Query( $query_args );

And here's what the SQL command ends up looking like:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.ID IN (1973,1979,1976) AND wp_posts.post_type = 'campaign-spotlight' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')  ORDER BY wp_posts.post_date ASC LIMIT 0, 3

No idea why it's still ordering by post date. Any help?

Replying to McGuive7:

I'm experiencing the same issue, however in my case I believe it's coming from a page/post ordering plugin that overrides the query order_by parameter. Might this be the case for any of y'all?

[Ben_W]

I am afraid the menu_order is interfering the "post_in" order. Please see my testing query below:

$argsSticky = array(
    'posts_per_page' => 16,
    'post_type' => array('post','video','ad_panel','instagram'),
    //'post__in' => $stickyPostIdArray,
    'post__in' => array(22153,23663,23360,23652),
    'cat' => $category_id,
    'orderby' =>'post__in',
    'paged' => $paged
);

$stickyPosts = new WP_Query($argsSticky);

The query I get is as follow:
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) WHERE 1=1 AND wp_posts.ID IN (22153,23663,23360,23652) AND ( wp_term_relationships.term_taxonomy_id IN (15)) AND wp_posts.post_type IN ('post', 'video', 'ad_panel', 'instagram') AND (wp_posts.post_status = 'publish' OR wp_posts.post_author = 53 AND wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY wp_posts.menu_order, FIELD( wp_posts.ID, 22153,23663,23360,23652 ) LIMIT 0, 16

The post id 23663 has menu_order=1, and post id 23360 has menu_order=5, the rest post has menu_order=0, therefore these two post will always positioned as last 2 posts. because of

ORDER BY wp_posts.menu_order, FIELD( wp_posts.ID, 22153,23663,23360,23652 )

This happens on wordpress 4.2.4

[Ben_W]

Trying reopening this ticket.

[dd32]

@Ben_W using the exact code you've offered, I get the following SQL: ORDER BY FIELD( wp_developsrc_posts.ID, 22153,23663,23360,23652 )

You've most likely got a plugin altering your orderings.

[Ben_W]

Hi Dion,

I have just tested this on a fresh installation of 4.2.4, you are right, the query turns out to be fine:

[request] => SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.ID IN (22153,23663,23360,23652) AND wp_posts.post_type IN ('post', 'video', 'ad_panel', 'instagram') AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') ORDER BY FIELD( wp_posts.ID, 22153,23663,23360,23652 ) LIMIT 0, 16

My apologies. going hunting for the rogue plugin now. Cheers