plugin development - ORDER BY wp_post custom column name in wp_query

admin管理员组

文章数量:1122832

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"

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"

• plugin-development
• wp-query
• templates

Share Improve this question edited Sep 18, 2016 at 18:44 bravokeyl 3,37766 gold badges2727 silver badges3333 bronze badges asked Sep 18, 2016 at 17:04 Rupesh AroraRupesh Arora 11133 bronze badges 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 Commented Sep 18, 2016 at 18:02
• Actually post_priority is column in wp_post that I create. Leave this I've changed the logic – Rupesh Arora Commented Sep 19, 2016 at 5:49

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 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.

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!

本文标签： plugin developmentORDER BY wppost custom column name in wpquery