Order taxonomy terms by the frequency of use in the last 30 days

Question

I would like to know how to order the terms in some taxonomy according to their frequency of use in posts in the last 30 days. What I am looking for is the most optimal method how to add "orderby" parameter or apply the array compare on the list of the terms or maybe some smart SQL select. I know this would include some extra columns in table to store a count and a last changed (added) time data. Any suggestions?

Answer 1

First thing's first -- You'll have to add that table.

The way you do that if you were writing this in a plugin is as follows:

function customtaxorder_init() {
    global $wpdb;
    $init_query = $wpdb->query("SHOW COLUMNS FROM $wpdb->terms LIKE 'term_order'");
    if ($init_query == 0) {
        $wpdb->query("ALTER TABLE $wpdb->terms ADD `term_order` INT( 4 ) NULL DEFAULT '0'");
    }
}
register_activation_hook(__FILE__, 'customtaxorder_init');

register_activation_hook allows this to run only when the plugin is activated, and the condition in the function keeps it from ever happening if it doesn't have to. Now, you have a place to keep that term order stored.

If you're doing this in your functions.php file of your theme, you could just attach it via add_action to 'init', run it once, then comment it out. There's many other ways to do it too, but this is just a quick handy-dandy way of getting that field into that table.

Next, we need a way to apply that order.

function customtaxorder_applyorderfilter($orderby, $args) {
    if($args['orderby'] == 'term_order')
        return 't.term_order';
    else
        return $orderby;
}
add_filter('get_terms_orderby', 'customtaxorder_applyorderfilter', 10, 2);

That gives us ability to use 'term_order' in the 'orderby' clause of any taxonomy query.

The final step of this is applying that number to the term in order for things to actually sort.

I figure we should look to update this every time we update a post, so how about the 'save_post' action hook.

function customtaxorder_update( $id ) {
    global $wpdb;
    $post_count = 0;
    $post_type = get_post_type( $id );
    $taxonomies = wp_get_object_taxonomies( $post_type );
    $args = array('fields' => 'ids');
    $terms = wp_get_object_terms( $id, $taxonomies, $args );
    $thirtydaysago = date_i18n('U', strtotime('-30 days') );
    foreach( $terms as $term ) :
        $querystr = "SELECT count FROM $wpdb->term_taxonomy, $wpdb->posts, $wpdb->term_relationships WHERE $wpdb->posts.ID = $wpdb->term_relationships.object_id AND $wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id AND $wpdb->term_taxonomy.term_id = $term AND $wpdb->posts.post_status = 'publish' AND $wpdb->posts.post_date > $thirtydaysago";
        $result = $wpdb->get_var($querystr);
        $post_count = $result;
        $wpdb->query("UPDATE $wpdb->terms SET term_order = '$post_count' WHERE term_id ='$term'");
    endforeach; 
}
add_action('save_post', 'customtaxorder_update');

The action 'save_post' runs the post ID through it, so all we have to do is leverage that in our function to first get it's post type, then all taxonomies associated with it, then all terms which apply to the post for all associated taxonomies. Set wp_get_object_terms to give us an array of ID's and loop throught them, get the post count from the category, and assign it to the term_order field which we created.

This should do it for you. Make sure to 'orderby' => 'term_order' and 'order' => 'DESC' since you'll want high numbers to come first!

Enjoy!

Note: I've added the necessary code to make this get the post counts from the last 30 days.