Reorder Posts in WordPress Programatically

WordPress uses the menu_order column in the posts table to manage the ordering of pages and hierarchical post types. If you have a situation where you need to display a collection of posts in a particular order, making use of this database column is the way to go. Then whether you are fetching posts with get_posts() or creating custom queries with WP_Query, all you need to do is pass 'menu_order post_title' as your orderby parameter.

Oftentimes, the next step is to allow users to easily change the display order. I really like to make it easy on users, so I use the built-in jQuery Sortable interaction to allow drag and drop reordering. Regardless of how you choose to allow reordering, you will have to update the menu_order value in the database for multiple posts.

Since I use AJAX to pass an array of ordered post IDs, performance is really important. So, I set out to find a way to reorder a collection of posts using a single MySQL query and this is what I came up with:

To use this function, all you have to do is pass in an ordered array of numeric post IDs. The function will take the post IDs in the order they appear in the array and update the menu_order value in the database for each post.

Comments

  1. Exactly what I was looking for.
    However, since the menu_order for a post is based on the index of the array. The first post will have a menu_order of 0 (zero)… Cool great.
    However all new posts that have been added AFTER this function has been ran… say on the admin page… will also have a menu_order of 0 (zero) by default. Correct? What would you use as a catch to put new posts at the end of the orderby->menu_order?
    Thanks again for the tip in the right direction.

    1. Correct. New posts have a default menu_order value of zero. Unfortunately, there is no hook that allows you to edit the default menu_order on new posts. The best way of resolving that would be to enqueue a jQuery script on the post-new.php page that updates the value on page load. You would need to use wp_localize_script() to pass the desired menu_order value after doing a query to find the highest menu_order value for the current post type.

  2. Awesome, you just saved me the headache of trying to figure this out. WP should have a built in function to be able to set the menu order for posts.

      1. @Micah thanks for the handy script.
        @Jesse Since 2.7.0 you can use the wp_insert_post_data filter to manipulate the post data before it is saved or updated.

  3. Thanks for the useful article.
    How much posts do you think could this script handle?
    E.g. is it a bad idea to reorder from e.g. 1000 -> 1 (I understand it’s an edge case but would like to understand the limitation).

    1. The performance could depend on how MySQL is configured, what server resources are available, and many other factors but my best guess is that you should be able to process around 10,000 posts per second.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.