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:
-
The term_taxonomy_ID is sometimes different from the term_ID so you will have to know your term_taxonomy_IDs for each category.
-
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.
-
You will also need a way to query these posts since
term_orderis not part of the WP_Query class.- Use the posts_where filter or write a custom query function.
-
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.