[wp-trac] [WordPress Trac] #9547: Taxonomy - interesting 'unused' term_order column in table term_relationships.

[WordPress Trac]

#9547: Taxonomy - interesting 'unused' term_order column in table
term_relationships.
----------------------------------------+-----------------------
 Reporter:  michelwppi                  |       Owner:  ryan
     Type:  feature request             |      Status:  reopened
 Priority:  high                        |   Milestone:  3.7
Component:  Taxonomy                    |     Version:  2.8
 Severity:  normal                      |  Resolution:
 Keywords:  has-patch needs-unit-tests  |
----------------------------------------+-----------------------

Comment (by c3mdigital):

 Just adding another use case for the term_order column.  I would love to
 see an additional WP_Query orderby argument 'term_order' to sort the order
 or posts.

 '''Use Case:'''
 Many enterprise publishers using WordPress require a way to set the
 precise order of posts on the home page as well as other areas.
 menu_order is great for the home page but when a post is in multiple
 categories or more than one sort_order is required for a post depending on
 where it is being displayed.  The term_order column is perfect for this
 because anytime you do a tax_query the term_relationships table is already
 INNER JOIN 'd with the posts table.  This works just like menu_order
 except only when the tables are joined via a term archive or new WP_Query

 The flexibility of the current term_relationships table allows us to use
 the term_order column for any object_id the term is assigned to.

 WP_Query extension to allow for ordering posts by term_order:


 {{{
 /**
  * Extends WP_Query with a posts_orderby filter that allows you to order
 posts by the term_relationships.term_order column
  *
  * @class XTeam_Lineup_Query
  */
 class XTeam_Lineup_Query extends WP_Query {

         var $lineup_query;

         function __construct( $args = array() ) {
                 add_filter( 'posts_orderby', array( &$this,
 'posts_orderby' ), 10, 2 );
                 $this->lineup_query = true;

                 parent::query( $args );
         }

         function posts_orderby( $orderby, $query ) {
                 if ( isset( $query->lineup_query ) ) {
                         global $wpdb;
                         return "$wpdb->term_relationships.term_order ASC,
 post_date DESC";
                 }
                 return $orderby;
         }
 }
 }}}
 '''Resulting SQL Query:'''
 `SELECT wpomni_posts.ID FROM wpomni_posts INNER JOIN
 wpomni_term_relationships ON (wpomni_posts.ID =
 wpomni_term_relationships.object_id) WHERE 1=1 AND (
 wpomni_term_relationships.term_taxonomy_id IN (9) ) AND
 wpomni_posts.post_type = 'post' AND (wpomni_posts.post_status = 'publish'
 OR wpomni_posts.post_status = 'private') GROUP BY wpomni_posts.ID ORDER BY
 wpomni_term_relationships.term_order ASC, post_date DESC LIMIT 0, 10
 `

 '''UI for setting the order of posts'''
 Not suggesting a ui for this but wanted to give an example of how the
 posts could be ordered.  Using UI-Sortable we allow the posts to be
 ordered directly on edit.php when the list table is filtered by term.
 Ajax callback bits that set the order:

 {{{
                 $str = str_replace( 'post-', '', $posts );
                 $order = explode( ',', $str );
                 $count = 1;
                 foreach ( $order as $item_id ) {
                         $wpdb->query( $wpdb->prepare(
                                         "UPDATE $wpdb->term_relationships
 SET term_order = %d WHERE object_id = %d AND term_taxonomy_id = %d",
                                         $count,
                                         $item_id,
                                         $term_tax_id
                                         )
                         );
                         $count ++;
 }}}

 I can work on patch or roll this into a plugin if it's something that has
 any traction.

--
Ticket URL: <http://core.trac.wordpress.org/ticket/9547#comment:26>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software