0

I'm trying to create sortable column headers for a CPT list, but I'm stuck on one piece. When clicking on an "Assignee" column head, I want the list to display alphabetically by the login name of the user.

The following in pre_get_posts sorts by the user ID (saved as the meta_value via Advanced Custom Fields):

$query->set('meta_key', 'assignee' );
$query->set('orderby', 'meta_value' );

However, I'd like to order not by ID but the user_login it corresponds to.

I've successfully used code from this article (http://scribu.net/wordpress/sortable-taxonomy-columns.html) for taxonomy sorting (in a different column), but I can't figure out how to apply the same technique to the user table.

I've tried variations of something like the below in the posts_orderby filter, but I'm way off.

SELECT GROUP_CONCAT(user_login ORDER BY user_login ASC)
        FROM $wpdb->users
        INNER JOIN $wpdb->postmeta ON $wpdb->postmeta.meta_value = ID
        INNER JOIN $wpdb->posts ON $wpdb->postmeta.post_id = $wpdb->posts.ID
        WHERE $wpdb->postmeta.meta_key = 'assignee'
        GROUP BY user_login

I really appreciate your help!

0

Ok, solved this. I had to use the posts_clausesfilter and the following code:

function assigned_to_orderby( $clauses, $wp_query ) {
    global $wpdb;

    $clauses['join'] .= "
    LEFT OUTER JOIN {$wpdb->postmeta} ON {$wpdb->posts}.ID={$wpdb->postmeta}.post_id
    LEFT OUTER JOIN {$wpdb->users} ON {$wpdb->users}.ID={$wpdb->postmeta}.meta_value
    ";

    $clauses['where'] .= " AND ({$wpdb->postmeta}.meta_key = 'assignee')";
    $clauses['groupby'] = "{$wpdb->postmeta}.post_id";
    $clauses['orderby']  = "GROUP_CONCAT({$wpdb->users}.user_login ORDER BY {$wpdb->users}.user_login ASC) ";
    $clauses['orderby'] .= ( 'DESC' == strtoupper( $_GET['order'] ) ) ? 'DESC' : 'ASC';

    return $clauses;
}

Adapted from http://scribu.net/wordpress/sortable-taxonomy-columns.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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