WP Query and order by particular specified IDs

Question

My WP_Query request looks like:

$query_args = array('posts_per_page' => $products, 'no_found_rows' => 1, 'post_status' => 'publish', 'post_type' => 'product' );
$query_args['meta_query'] = $woocommerce->query->get_meta_query();
$query_args['meta_query'][] = array(
    'key' => '_featured',
    'value' => 'yes'
);

$r = new WP_Query($query_args);

What argument I need to add to return a query ordered by IDs in a particular row (ex. I need to return products IDs 161,165,131,202 in that order).

Edit: I have added a post__in and orderby arguments which pulls only those particular products by IDs but not in the order specified in the array but it looks like based on when product was added to the back end.

$query_args = array('posts_per_page' => $products, 'no_found_rows' => 1, 'post_status' => 'publish', 'post_type' => 'product', 'post__in' => array(161,165,131,202), 'orderby' => 'post__in', 'order' => 'ASC' );

WP Query for that looks like:

SELECT wp_posts.ID 
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 ) 
WHERE 1=1  
AND wp_posts.ID IN (161,165,131,202) 
AND wp_posts.post_type = 'product' 
AND ((wp_posts.post_status = 'publish')) 
AND ( ( wp_postmeta.meta_key = '_visibility' 
AND CAST(wp_postmeta.meta_value AS CHAR) IN ('visible','catalog') ) 
AND (mt1.meta_key = '_featured' AND CAST(mt1.meta_value AS CHAR) = 'yes' )) 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.menu_order, FIELD( wp_posts.ID, 161,165,131,202 ) 
LIMIT 0, 5

no clue why ORDER BY is set to be menu_order not post__in

Answer 1

To keep the same post order as it was passed to the post__in parameter, you need to pass post__in as value to orderby argument and ASC to the order argument

$query_args = [
    'posts_per_page' => $products, 
    'no_found_rows'  => true, 
    'post_type'      => 'product', 
    'post__in'       => [161,165,131,202],
    'orderby'        => 'post__in',
    'order'          => 'ASC'
];

EDIT

Looking at your edit, you have a bad filter somewhere, most probably a bad pre_get_posts action or a posts_orderby filter. Do the two following tests

• Add 'suppress_filters' => true, to your query arguments, if that helps, you have a bad filter

• Add remove_all_actions( 'pre_get_posts' ); before you do your query, if that helps, you have a bad pre_get_posts filter

• A third, but most probable useless check would be to add wp_reset_query() before the custom query, if that helps, you have a bad query somewhere, most probably one using query_posts

Answer 2

To include the other posts not in the array, you can try the following which creates an array with keys that combine the post id with an indicator so you can do a key sort (ksort) and return all the posts with the array first

$query_args = array('posts_per_page' => $products, 'no_found_rows' => 1, 'post_status' => 'publish', 'post_type' => 'product' );

$r = new WP_Query($query_args);

$my_posts = array();
$id_order = array(161, 165, 131, 202);

foreach($r->posts as $key => $the_post){
  if (in_array($the_post->ID, $id_order)) {
    $index = array_search($the_post->ID, $id_order);
    $my_posts["a" . $index . '_' . $the_post->ID] = $the_post; // store the order of the returned posts by post ID
  } else {
    $my_posts["b0_" . $the_post->ID] = $the_post; // store the order of the returned posts by post ID
  }
}

ksort($my_posts);

foreach($my_posts as $post){ // go through custom list of post IDs
  setup_postdata($post);

  // do your content here
  ?><article><h2><?php the_title();?></h2><?php
  the_content();
  ?></article><?php
}

Answer 3

You can order in ascending or descending order, but you can't specify a custom order - in the query parameters at least.

The easiest way to do what you're wanting to do - display featured products in a particular order - would be to rearrange the results after the query has occured. That is, if it's feasible for you to come back and change this code whenever you want to change the order (or add new products). If you want a more robust solution, I'd be looking at adding a custom field to the products, such as 'featured_order', and then ordering your query by that field. I can run through that if you wish.

That said, assuming you're after the easiest solution here, you'll want to add something like this to the end of the code snippet you've posted:

$my_posts = array();
$id_order = array(161, 165, 131, 202);

foreach($r->posts as $key => $the_post){
  $my_posts[$the_post->ID] = $key; // store the order of the returned posts by post ID
}

foreach($id_order as $id){ // go through custom list of post IDs
  $post = $r->posts[$my_posts[$id]]; // grab the query result for the post ID we want
  setup_postdata($post);
  // the_title(); the_content(); ........
}

It's important to note that if the post ID is not present in the $id_order array, it will NOT be shown by this code - even if it was returned by the query.

EDIT: If you want to make sure the other posts NOT in the array do still return, see the answer posted by juz.