Ordering by "post__in" doesn't result in the correct order

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 will "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 result 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:

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

Answer 1

If you look at the WP_Query-generated SQL code you provided, you should note this snippet 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.

Some credit for the solution goes to this WP.SE answer for a very similar question.