Different post sort order within different categories

5 comments

1. I recently overcame this same challenge. We needed a way to save a different sort order for each post in multiple categories.

   There is an unused column in the wp_term_relationships table, term_order which defaults to 0 and is already cast as an integer.

   When a term is assigned to a post it gets an entry in this table for each term assigned. An object_ID (post_ID), a term_taxonomy_ID and the term order is set to 0.

   Challenges:

   1. The term_taxonomy_ID is sometimes different from the term_ID so you will have to know your term_taxonomy_IDs for each category.

   2. Since the default is always 0 you need a method to set this at a higher number or all your new posts will always come first.

      • The easy way is just to alter the database default to a higher number. The other method is to create a function and attach it to the save_post, update_post or draft_to_publish hook.

   3. You will also need a way to query these posts since term_order is not part of the WP_Query class.

      • Use the posts_where filter or write a custom query function.

   4. You will also need a way to sort these posts. I will outline the Ajax drag and drop method I used but you could also use a metabox or custom field.

   Ajax drag and drop sort:

   You will need to create an admin options page and query for the posts and output them to a custom table or unordered list. You can also run this using Ajax so the user just has to select a category from a select box then load the posts from that category. (I will only be providing the Ajax and Php functions to save the order).

   PHP wp_ajax function:

   add_action ( 'wp_ajax_item_sort', 'wnd_save_item_order' );
       function wnd_save_item_order () {
           global $wpdb;
           $wpdb->flush ();
           $posts = $_POST[ 'order' ];
           $str = str_replace ( "post-", "", $posts );
           $order = explode ( ',', $str );
           $cat_id = (int)$_POST[ 'cat' ];
           $counter = 1;
           foreach ( $order as $item_id ) {
   
               $wpdb->query ( "UPDATE $wpdb->term_relationships SET term_order = '$counter' WHERE object_id = '$item_id' AND term_taxonomy_id = '$cat_id'" );
               $counter++;
           }
            $response = '<div id="message" class="updated fade"> <p>Sort Order successfully updated</p></div>';
            echo $response;
            die( '<div id="message" class="updated fade"> <p>An error occured, order has not been saved.</p></div>' );
       }
   

   The jQuery Ajax:

   // Add this to the admin_enque_scripts and do a $pagenow check.
   function sort_order_js() { ?> 
     jQuery(document).ready(function($) {
        var catID = $("#cat-select option:selected").val()
         $("#" + catID + "-save-order").bind("click", function() {
         $("#" + catID + "-load-animation").show();
   
             $.post(ajaxurl, { action:'item_sort', cat: catID, pos: position, order: $("#" + catID + "-sortable").sortable('toArray').toString() },
   
             function(response) {
                 $("#" + catID + "-update-response").text('Updated');
                 $("#" + "-load-animation").hide();
                  });
                   return false;
               });
   
               <?php } ?>
   

   How do we get our newly ordered posts?

   posts_orderby filter:

   add_filter('posts_orderby', 'custom_posts_orderby');
   function custom_posts_orderby($order) {
       $order_by = 'wp_term_relationships.term_order';
       $direction = 'DESC';
       $order = " ORDER BY $order_by $direction";
       return $order;
   }
   

   Custom query API

   You can run this function passing it an array of arguments similar to WP_Query and it will return an array of post_ids

   function custom_get_post_ids( $args ) {
           $defaults = array (
               'category_id'               => NULL,
               'exclude_array'             => array (),
               'post_status_array'         => array ( 'publish' ),
               'post_status'               => NULL,
               'post_type_array'           => array ( 'post' ),
               'post_type'                 => NULL,
               'offset'                    => 0,
               'length'                    => 7,
               'order_by'                  => 'wp_term_relationships.term_order',
               'order_direction'           => 'ASC',
               'secondary_order_by'        => 'post_date',
               'secondary_order_direction' => 'DESC',
           );
   
       $args = wp_parse_args( $args, $defaults );
       extract( $args, EXTR_SKIP );
       if ( isset( $post_type ) ) {
           $post_type_array = array ( $post_type );
       }
       // If the post_status passed to us is a string, convert it to an array
       if ( isset( $post_status ) ) {
           $post_status_array = array ( $post_status );
       }
       global $wpdb;
       $query = 'SELECT wp_posts.ID FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)';
       $query .= ' WHERE wp_term_relationships.term_taxonomy_id IN (' . intval( $category_id ) . ')';
       if ( count( $exclude_array ) > 0 ) {
           $exclude = implode( ',', $exclude_array );
           $query .= ' AND wp_posts.ID NOT IN (' . $exclude . ')';
       }
       $query .= " AND wp_posts.post_type IN('" . implode( "','", $post_type_array ) . "')";
       $query .= " AND wp_posts.post_status IN('" . implode( "','", $post_status_array ) . "')";
           $query .= " ORDER BY $order_by $order_direction";
       if ( ! empty( $secondary_order_by ) ) {
           $query .= ",$secondary_order_by $secondary_order_direction";
       }
       $query .= " LIMIT $offset, $length /*_get_post_ids() */";
       $num_results = $wpdb->query( $query );
       $res = array ();
       foreach ( $wpdb->last_result as $r ) {
           array_push( $res, intval( $r->ID ) );
       }
       return ( $res );
       }
   

   Finally we just need to set new posts to have a term_taxonomy_id higher than the usual amount of posts to be ordered.

   function default_sort_order() {
       global $post, $wpdb;
       $categories = get_the_category( $post->ID);
       $id = $post->ID;
           foreach ( $categories as $cat ) {
               $cat_id = $cat->term_taxonomy_id;
               $wpdb->query ( "UPDATE $wpdb->term_relationships SET term_order = 20 WHERE object_id = '$id' AND term_taxonomy_id = '$cat_id'" );
   
           }
   }
   

   Still here?

   This method has been tested and is used by a large news organization to set up the display order on the home page and category pages.

   Log in to Reply

2. Things like this get complex really fast, this is maybe why the OP never returned because organizing live broadcasts using WP is not a great idea ( ad insertion, rotation rules, live changes on the fly, etc). Most of that stuff is better handled by a application that has a specific API based on those needs, and has XML/JSON feeds for throwing the data around if needed (like into WP).

   The Order issue
   This issue of ordering in general and drag and drop ordering seems to be a sore point that has been brought up in the past, with most of the core team agreeing that WordPress should remain chronological. Anything else should be a plugin feature for developers, the problem is there are a lot of people using WordPress as a development platform who run into this, WordPress is a CMS , right;)

   So what does that leave us with?

   Drag and drop is really intuitive so using something like jQuery UI’s .sortable(); with a custom db entry or meta field works well. You can pretty much do the same thing as the default post gallery editor (and the menu editor), where you have an option to drag an drop the order , as well as manually enter a number and sort parameter (ASC, DESC) that you could extend to include name, or any WP Query/WPDB parameter.

   Anything more complex would have to be based on some sort of query rule system which would be pretty specific to the actual requirements at hand.

   There are some plugins I have seen that do this, such as PostMash and Orderly, but nothing I know of that is really comprehensive, thus the need for hacking lots of template files.

   Log in to Reply

3. I know you said “posts & categories”, but I think you’ll do better connecting posts to posts and storing the order as post metadata:

   • create a new post type, show (recommended, but you could use post instead)

   • create a new post type, playlist (recommended, but you could use post instead)

   • create a post/playlist, Tuesday Lineup, and save post metadata
     shows = 8,73,5,68,994
     (an ordered list of show IDs). (The ACF Relationship Field adds a great UI for this but the principle is the same without it.)

   • when displaying each post/playlist, read the value for shows, fetch those posts, and display them in order.

   Log in to Reply

4. To do this in EXTREME psuedo code you way want to try:

   switch($cat_id) {
       case 1:
           $args = "sort rules for cat 1";
           break;
       case 2:
           $args= "sort rules for cat 2";
           break;
       etc...
       default:
           $args= "default sort rules";
           break;
   }
   
   $posts = get_posts($args);
   

   Note: with this method only define the “exceptions” with the case clauses and the “rule” with the default.

   Log in to Reply

5. Simple. PHP for and WordPress get_posts() and a few checks will do the trick.

   You can change $args['orderby'] and $args['order'] to sort the way you need it to sort. View Order & Orderby Parameters

   Haven’t tested, but it should work!

   function get_content($type, $categories){
       for($i = 0; $i < count($categories); $i++){
           // configure posts
           $args = array(
               'numberposts'     => -1,
               'offset'          => 0,
               'category_name'   => $categories[$i],
               'include'         => '',
               'exclude'         => '',
               'meta_key'        => '',
               'meta_value'      => '',
               'post_type'       => $type,
               'post_mime_type'  => '',
               'post_parent'     => '',
               'post_status'     => 'publish' 
           );
           // define different sorting methods depending on category
           if($categories[$i] == 'playlist-1'){
               $args['orderby'] = 'post_date';
               $args['order'] = 'DESC';
           } elseif($categories[$i] == 'playlist-2'){
               $args['orderby'] = 'post_date';
               $args['order'] = 'ASC';
           } elseif($categories[$i] == 'playlist-3'){
               $args['orderby'] = 'post_title';
               $args['order'] = 'ASC';
           } 
           // fetch the posts
           $postData[$categories[$i]] = get_posts($args);
       }
       return $postData;
   }
   
   // Choose the categories to sort
   $categories = array('playlist-1', 'playlist-2', 'playlist-3');
   
   // This will contain all categories and posts inside one array.
   $allPosts = get_content('post', $categories);
   //print_r($allPosts);
   
   // EXAMPLE: To loop through a set of posts, you will need to access it like so
   for($i = 0; $i < count($categories['playlist-1']); $i++){
       echo '<h1>'.$categories['playlist-1'][$i]['post_title'].'</h1>';
       echo apply_filter('the_content', $categories['playlist-1'][$i]['post_content']);
   }
   

   OR

   GET POSTS BY TAXONOMY SORT ORDER

       function get_content_taxonomy($tax, $term){
           $wp_query = new WP_Query();
           $wp_query->query(array('posts_per_page' => -1, 'orderby' => 'title', 'order' => 'ASC', 'tax_query' => array(array('taxonomy' => $tax, 'field' => 'slug', 'terms' => $term))));
           $posts = $wp_query->posts;
           if(!empty($posts)){
               $return = $posts;
           }
           return $return;
       }
   
       // No idea how you have your stuff setup, but it could be like:
       $playlist1 = get_content_taxonomy('playlist', '1');
       // print_r($playlist1);
   

   Log in to Reply