Ordering by "post__in" doesn't work

Question

After creating a flat array of post IDs, I want to display those posts through a custom query in the same order as in my array. The doc states that 'post__in' "preserve post ID order given in the 'post__in' array". Yet in my case it does not. Why?

Creation of my array of post IDs:

if (have_rows('cases')):
  while (have_rows('cases')) : the_row();
    $array_cases[] = get_sub_field('case');
  endwhile;
endif;
print_r($array_cases);

Result:

Array(
[0] => 2959
[1] => 919
[2] => 914
...)

WP_Query():

$args = array(
    'post_type' => 'iw-project',
    'post__in' => $array_cases,
    'orderby' => 'post__in');
$query_cases = new WP_Query($args);
print_r($query_cases);

Result:

WP_Query Object(
...
[posts] => Array(
[0] => WP_Post Object (
    [ID] => 2959
    ...)
[1] => WP_Post Object (
    [ID] => 914
    ...)
[2] => WP_Post Object (
    [ID] => 1974
    ...)

The order in the resulting query is wrong. The second and third posts should be 919 and 914, not 914 and 1974.

Below is the raw SQL of the WP_Query object as requested by Kodos Johnson:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  JOIN wp_icl_translations t
ON wp_posts.ID = t.element_id
AND t.element_type = CONCAT('post_', wp_posts.post_type)  WHERE 1=1  AND wp_posts.ID IN (2959,919,914,1593,1583,1974,1649,993,675) AND wp_posts.post_type = 'iw-project' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled') AND ( ( t.language_code = 'en' AND wp_posts.post_type  IN ('post','page','iw-project','iw-contact','iw-slide','iw-client' )  ) OR wp_posts.post_type  NOT  IN ('post','page','iw-project','iw-contact','iw-slide','iw-client' )  )  ORDER BY wp_posts.menu_order, FIELD( wp_posts.ID, 2959,919,914,1593,1583,1974,1649,993,675 ) LIMIT 0, 8

Can you confirm that post 919 is in your result array at all? Does post 919 have post type 'iw-project' and is it not deleted? I don't see 919 in your result at all. So maybe it simply doesn't get matched by your query conditions. — Cave Johnson
Good question. But yes it is, in 7th position. And it belongs indeed to the "iw-project" post type. — drake035
Can you try all the solutions posted by the accepted answer here and let me know if it works? wordpress.stackexchange.com/questions/220014/… — Cave Johnson
I just tried installing wordpress to reproduce your problem but I could not reproduce it. My query is correctly preserving the order of posts from the post__in array (even without adding the order parameter). Can you please try getting the raw sql from your wp_query object? You can do this by printing out the request property like this: print_r($query_cases->request). That should give you the raw SQL. Edit the SQL that you get into your question. — Cave Johnson
@KodosJohnson thanks, I edited my question and added the raw SQL — drake035

Cave Johnson Cave Johnson · Accepted Answer · 2018-02-03T20:13:25

If you look at the SQL code that your WP_Query generates, you should see this snippet of code that handles the order:

ORDER BY wp_posts.menu_order, FIELD( wp_posts.ID, 2959,919,914,1593,1583,1974,1649,993,675 )

What this means is that it is ordering by wp_posts.menu_order BEFORE it does the order based on post IDs in your array (2959,919,914,...). It looks like a filter is overriding the default order of WP_Query results. This could be caused by either a theme or plugin.

The solution is to prevent other filters from modifying your query by passing in the parameter 'suppress_filters' => true. Also, you should call remove_all_actions('pre_get_posts'); before your WP_Query. This should prevent any other filters from modifying your query.

Credit goes to Pieter Goosen over at WP.SE for a very similar question.

Ordering by "post__in" doesn't work

1 Answers