Sort custom post type list table by display name of a user id stored as post meta value

3 Views

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?

Read More

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.

Post Views: 3

Related posts

Leave a Reply

2 comments

  1. 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;
}

  2. 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().