1

I've alreday done lots of R&D on this question but not get a suitable answer any where. This is very little task for me if , I need to make a mysql query. But though , I' not good hand on experience in WP so getting much frustrated by the solution of question.

How to sort a wp_query by custom column that I added in wp_posts table.

    $args = array(
    /*'base'               => '%_%',
    'format'             => '?paged=%#%',
    'total'              => 1,
    'current'            => 0,
    'show_all'           => false,
    'end_size'           => 1,
    'mid_size'           => 2,
    'prev_next'          => true,
    'prev_text'          => __('« Previous'),
    'next_text'          => __('Next »'),
    'type'               => 'plain',
    'add_args'           => false,
    'add_fragment'       => '',
    'before_page_number' => '',
    'after_page_number'  => ''*/
    'posts_per_page' => 10,
    'post_type' => array('food', 'buffets'),
    'meta_key'  => 'post_priority',
    'orderby'   => 'meta_value_num',    
    'order'  => 'ASC'

);

When I'm trying with this one actually order by wp_postmeta table wp_postmeta.meta_value+0 Means instead of wp_post table it is ordering by postmeta table Post_priority is my custom column in wp_post table

Here is the result of the query that I got:

"SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts 
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) 
WHERE 1=1 AND ( \n wp_postmeta.meta_key = 'post_priority'\n) 
AND wp_posts.post_type IN ('food', 'buffets') 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_author = 2 
AND wp_posts.post_status = 'private')
GROUP BY wp_posts.ID 
ORDER BY wp_postmeta.meta_value+0 
ASC LIMIT 0, 10","posts"
2
  • The query is behaving as it should behave, nothing unusual (I.e. sorting using meta_key which is post_priority). Can you clarify, for which column in wp_posts table you want sorting?
    – Deepak jha
    Sep 18, 2016 at 18:02
  • Actually post_priority is column in wp_post that I create. Leave this I've changed the logic Sep 19, 2016 at 5:49

2 Answers 2

1

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!

1
  • Thanks MIKE it is a good answer but I feel it make my search slow. Sep 19, 2016 at 5:35
0

You told it to order by meta_value, so it's ordering by the meta value. There is no "post_priority" column in the wp-posts table, so it can't order by it. The allowed values for "orderby" are not unlimited, they are a fixed set.

You can't modify core tables and expect the code to know about it. The whole reason for the postmeta table is to allow you to insert arbitrary metadata instead of modifying the tables to have columns that the code doesn't know about.

Remove your extra column and store your data as postmeta instead. Set the meta_key to "priority" and meta_value to the priority value. Then your query will work.

1
  • Thanks otto for your suggestion. Now , I think from this way me job can be done. Firstly , I think it is not good way but now , I think this is also a good way... Sep 19, 2016 at 5:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.