Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I have a custom post type named domicile. Each post (domicile) has an owner (not the author). Owners are users with a custom role. I store the user id as a post meta value (dmb_owner) for the domicile post type.

How do I sort the list table (wp-admin/edit.php?post_type=domicile) by the display names of owners?

The relevant code from my class, setup_columns() is called on init:

/**
 * Manage columns for List Table.
 *
 * @wp-hook init
 * @return void
 */
protected function setup_columns()
{
    add_filter(
        "manage_edit-{$this->post_type}_columns",
        array ( $this, 'set_column_header' )
    );

    add_action(
        "manage_{$this->post_type}_posts_custom_column",
        array ( $this, 'render_columns' ),
        10,
        2
    );

    add_filter(
        "manage_edit-{$this->post_type}_sortable_columns",
        array ( $this, 'set_sortable_columns' )
    );

    add_filter(
        'request',
        array ( $this, 'prepare_orderby' )
    );
}

/**
 * Register column headers.
 *
 * @wp-hook manage_edit-{$this->post_type}_columns
 * @param  array $columns
 * @return array
 */
public function set_column_header( $columns )
{
    unset (
        $columns['author'],
        $columns['comments'],
        $columns['date']
    );
    $columns['owner'] = __( 'Owner', 't5_domicile_manager' );

    return $columns;
}

/**
 * Display cell content.
 *
 * @wp-hook manage_{$this->post_type}_posts_custom_column
 * @param string $column_name
 * @param int $post_id
 * @return void
 */
public function render_columns( $column_name, $post_id = 0 )
{
    if ( 'owner' === $column_name )
    {
        $owner_id = get_post_meta( $post_id, 'dmb_owner', TRUE );
        if ( $owner_id )
        {
            $owner = get_user_by( 'id', $owner_id );
            print $owner ? $owner->display_name : '<i>not set</i>';
        }
    }
}

/**
 * Register sortable columns
 *
 * @wp-hook manage_edit-{$this->post_type}_sortable_columns
 * @param array $columns
 * @return array
 */
public function set_sortable_columns( $columns )
{
    $columns['owner'] = 'owner';
    return $columns;
}

/**
 * Set custom sort order.
 *
 * @wp-hook request
 * @param  array $vars
 * @return array
 */
public function prepare_orderby( $vars )
{
    if ( isset ( $vars['orderby'] ) && 'owner' == $vars['orderby'] )
    {
        $vars = array_merge(
            $vars,
            array (
                'meta_key' => 'dmb_owner',
                'orderby'  => 'meta_value_num'
            )
        );
    }
    return $vars;
}

This … works, but it is obviously wrong, because it sorts by the stored ID. I have to filter the query – but I am not sure how exactly I should do that.

Sorry for the title, but I want to make sure this will be found. I searched very hard and found nothing useful.

share|improve this question

2 Answers 2

up vote 5 down vote accepted

An 'easy' but not very good solution is to store the user's display name as well as ID, and sort by that. Obviously an update of a user's display name would prompt an update of all the domicile that user owns.

Alternatively the following is an outline (untested) of what should work. The idea is to tell WordPress to sort by the meta value still (so that WordPress joins the post meta table) and then its possible to use the post_clauses filter to join the users table and sort by display name:

add_filter('posts_clauses', 'wpse58638_post_clauses',10,2);
function wpse58638_post_clauses( $clauses, $query ) {
    global $wpdb;
    if ( ! $query->is_main_query()
        || ! is_admin()
        || ! $query->get('post_type') == 'domicile'
        || ! $query->get('meta_key') == 'dmb_owner'
        || ! $query->get('orderby') == 'meta_value_num'
    ){
        return $clauses;
    }

    //Get sort order
    $order_dir = $query->get('order');
    $order_dir = ('asc' == $order_dir ? 'ASC' : 'DESC');

    //Join user table onto the postmeta table
    $clauses['join'] .= " LEFT JOIN {$wpdb->users} ON {$wpdb->prefix}postmeta.meta_value={$wpdb->users}.ID";

    //Replace orderby
    $clauses['orderby']  = " {$wpdb->users}.display_name $order_dir";

    return $clauses;
}
share|improve this answer
2  
After some minor changes I got it to work. $query->get('order') had to test for a lower case value, and it is {$wpdb->prefix}postmeta, not $wpdb->postmeta. Thanks! –  toscho Jul 16 '12 at 11:20

You can add the custom field dmb_owner to the query in backend and then you can filter about this with your standard filters.

add_filter( 'query_vars', 'fb_query_vars_admin' );
/**
 * If needed: Allow meta queries in the admin
 */
function fb_query_vars_admin( $query_vars ) {

    // break off, if not in admin area
    if ( ! is_admin() )
        return $query_vars;

    $query_vars[] = 'dmb_owner'; // my key of custom field
    //$query_vars[] = 'meta_value'; // my value of custom field

    return $query_vars;
}

Now is this custom field inside the $query_vars and can usable, like your method prepare_orderby().

share|improve this answer
    
My problem is the alphabetical sorting by display name, not by meta field id. The meta field is just an integer. So I need to fetch the name for the given id before MySQL starts sorting. Changing the query vars doesn’t help here. –  toscho Jul 16 '12 at 8:10
    
But you will sort baout the owner, your custom field. If you add this to this array, then can you filter alphabetical about hte owner. You can add also the meta value and sort about the meta value. If oyu have your data for sorting inside a custom field, then is this not on default in the table view of posts and with this filter is it; usable for custom filters. –  bueltge Jul 16 '12 at 8:16
    
Sorry, I don’t understand. How does that sort by display name? –  toscho Jul 16 '12 at 8:25
    
I have understand, the users name is inside an custom field of your custom posts type, right? The display name is on the userdata. But you can mapped the display name to the authors inside the custom field. The easiest way for filter on an custom field. see this example gist.github.com/3121676 –  bueltge Jul 16 '12 at 9:03
1  
chat? –  toscho Jul 16 '12 at 9:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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