ORDER BY wp_post custom column name in wp_query

Question

I’ve investigated a bit and your problem is the WP_Query::parse_orderby() function. Everything you like to use as an order is sanitized there which the user Otto liked to point out. Unfortunately it has no filter to hook in. And yes it is better to use some meta_fields if you can afford an expensive JOIN in every get_posts. If not keep in mind that there is the menu_order field can be used for that too as long as you are not dealing with nav_menu_item post types.

Anyway I found two solutions for that and want to share them here. For more details read https://wp-includes.org/296/custom-wp_posts-column-sortable/

Extend WP_Query

The cleanest way is to write an own query class:

<?php

class Enhanced_Post_Table_Query extends \WP_Query {

  /**
   * Extend order clause with own columns.
   *
   * @param string $order_by
   *
   * @return bool|false|string
   */
  protected function parse_orderby( $order_by ) {
    $parent_orderby = parent::parse_orderby( $order_by );

    if ( $parent_orderby ) {
      // WordPress knew what to do => keep it like that
      return $parent_orderby;
    }

    // whitelist some fields we extended
    $additional_allowed = array(
      'something',
    );

    if ( ! in_array( $order_by, $additional_allowed, true ) ) {
      // not allowed column => early exit here
      return false;
    }

    // Default: order by post field.
    global $wpdb;

    return $wpdb->posts . '.post_' . sanitize_key( $order_by );
  }
}

Now you can run queries and sort with the custom field:

$get_posts = new Enhanced_Post_Table_Query;

$get_posts->query(
  array(
    'orderby' => 'something'
  )
);

Late filter to overwrite things

A bit dirty but it works. As there is no direct filter to do that you can choose a later one and manipulate the query (the later the dirty it is):

<?php

/**
 * Add custom wp_posts column for sorting.
 *
 * @param string   $order_clause SQL-Clause for ordering.
 * @param WP_Query $query        Query object.
 *
 * @return string Order clause like "wp_posts.post_foo DESC" or similar.
 */
function custom_column_sort_filter( $order_clause, $query ) {

  // whitelist some fields we extended
  $additional_allowed = array(
    'something',
  );

  if (
    ! in_array(
      $query->get('orderby'),
      $additional_allowed,
      true
    )
  ) {
    // unknown column => keep it as before
    return $order_clause;
  }

  global $wpdb;

  return $wpdb->posts
         . '.post_'
         . sanitize_key( $query->get('orderby') )
       . ' ' . $query->get( 'order' );

}

add_filter( 'posts_orderby', 'custom_column_sort_filter', 10, 2 );

Now almost every call of get_posts is informed about your custom column:

get_posts(
  [
    'orderby'          => 'something',
    // IMPORTANT:
    'suppress_filters' => false,
  ]
);

But only if “suppress_filters” is set. This should be used by every plugin. There are more solutions via preg_replace but those are very late and replacing with REGEXP is always dirty and dangerous.

I hope you can work with that!

Extend WP_Query

Late filter to overwrite things

Related Posts: