Different post sort order within different categories

I need to give each post a unique post order for multiple Categories (per post) it is in. I can imagine a solution using custom fields whereby for each Category there is a corresponding custom field for the order.

E.g.

Categories:
Playlist1
Playlist2
Playlist3

Custom fields for the post:
Playlist1_Order
Playlist2_Order
Playlist3_Order

This method is obviously not easily scalable though so I would like to know if anybody has a more elegant solution.

Background

I am setting up a site to accompany a 24/7 broadcast. The playlist for the broadcast is a 12hr loop that is modified once per week. Some shows are added & removed and the order of some shows is re-arranged. I intend to define a custom post type of Shows and custom taxonomy of playlist. Each show will be added to one or more playlists. It must be possible for the show to have a unique order in each playlist.

(I decided to keep the question limited to posts & categories rather than post-types & taxonomies to avoid confusion).

UPDATE

To clarify, consider this example. There’s a post called “Rod’s Radio Roundup.” It’s in the categories “Tuesday Lineup” and “Wednesday Lineup.” On Tuesday it’s the third show and Wednesday it’s the 7th show. Later, it leaves Wednesday but moves to the 1st slot on Thursday and the 5th slot on Saturday. And there are 40 other posts like this.

The essential question: How do you maintain multiple orders, one per category, for a single post?

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.