Different post sort order within different categories

Question

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?

Answer 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.

Answer 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.

Answer 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.

Answer 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.

Answer 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);