get_posts: Order by custom wp_posts column

Some developer like to extend the WordPress tables with a custom wp_posts column. To have it as a sortable column for queries like in get_posts you need to extend WordPress a bit. This article shows how to make custom columns sortable. Read the more up to date and famous one afterwards!
WordPress 4.7: Custom bulk actions, Theme inheritance and more
Open in new tab for later

If you find other or even better solutions to sort custom wp_posts columns then please leave a comment below.

The “orderby” clause is limited

Once you added a custom wp_posts column then it is not sortable via the usual queries. The get_posts function and WP_Query class limits us developer to the default columns that come with WordPress as this core code shows:

// Used to filter values.
$allowed_keys = array(
   'post_name', 'post_author', 'post_date', 'post_title', 'post_modified',
   'post_parent', 'post_type', 'name', 'author', 'date', 'title', 'modified',
   'parent', 'type', 'ID', 'menu_order', 'comment_count', 'rand',
);

if ( ! in_array( $orderby, $allowed_keys, true ) ) {
   return false;
}

This code is part of the WP_Query::parse_order_by method. It shows some of the allowed values for “orderby” in post queries. At some point a if-statement asks “Will you comply?” and if not then it just explodes. No filter in between can avoid that so I searched for a later filter to manipulate the query.

Only indirect filter available

After WP_Query::get_posts passed the “orderby” statements it allows some filter to manipulate the SQL-Statement. This is a bit dirty what manipulating generated data always is but works fine.

The even simpler and clean solution is basic OOP: Extending the WP_Query class. I like to show that solution first as it is my favourite and then the common “let-me-filter-that” solution most Plugin developers might use.

How to extend WP_Query for a sortable custom wp_posts column

Extend WP_Query class

As long as WP_Query itself has no direct filter to hook in we can extend the class. This is a very clean and stable solution because in our own class we can change things without hurting other plugins or functionality. See in the example below how the custom wp_posts column has been added to the query as “ORDER BY wp_posts.post_something DESC”.

Source code

SELECT SQL_CALC_FOUND_ROWS  wp_posts.* 
FROM wp_posts  LEFT JOIN wp_inc_term_relationships ON (wp_posts.ID = wp_inc_term_relationships.object_id) 
WHERE 1=1  
AND ( 
  wp_inc_term_relationships.term_taxonomy_id IN (67)
) 
AND wp_posts.post_type = 'post' 
AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_something DESC 
LIMIT 0, 10
<?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 );
  }
}


$get_posts = new Enhanced_Post_Table_Query;

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

This is nothing that can be used by `get_posts` functions or any other WordPress function. But it helps to separate the logic from the WordPress internals.

Overwrite queries via “posts_orderby” filter

Actually there is a filter to hook in which is not as clean as the previous method. As every manipulating filter it needs “supress_filters” disabled, which allows plugins to change the way a query works.

Source code

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

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

It is a bit dirty because it manipulates already parsed data which may harm caching plugins. In return it is a filter so every other plugin is able to use it without any knowledge about our own plugin. This effort is often bigger than its cost and a common way in the WordPress world.

Conclusion

Extending the tables that WordPress brings is not hard but brings pain. It is not meant to do that as the missing filters show. Whatever you like to extend should be done via meta keys. But this is something I don’t like to do as meta-queries or order by meta-fields always comes with a expensive JOIN operation. So I hope that one day the internal tables are easier to extend. Until then you can extend custom wp_posts columns with those two workarounds.

Are there more? Got some other solution? Then please leave a comment below!

Leave a Reply

Your email address will not be published. Required fields are marked *