Support » Developing with WordPress » Creating custom columns of users and sorting them

  • Hello,

    My blogger client wants to sort the user columns in his admin area by last name and I thought that would be a great way to get started learning how to customize WordPress and the admin area.

    I understand that to add columns, you have to apply this filter:

    
    apply_filters( "manage_{$post_type}_posts_columns", string[] $post_columns )

    where

    $post_type
        (string) The post type slug.

    The tutorial articles I read either used the standard post type – i.e. ‘manage_posts_columns’ or they added a custom postt – i.e. ‘manage_cake_posts_custom_column’, but what $post_type applies to the Users>All Users section in the dashboard?

    I like to keep one question at a time and go step by step, but I want to ask this as well: Does this “All Users” page get populated from data in the MySql “Users” table? There is no First Name and Last Name column there but only a “display name” that seems to have those names concatenated. That doesn’t seem like the optimal design I would choose. However, we installed admin columns plug in and it allows us to have a first and last name column. This question is not about that plugin or I would be asking this question there, but more generally and theoretically, in order to achieve that functionality, does one have to pull that data and then run a splitting function? I think that’s what they’re doing.

    Thanks for all your help. I’m super curious. I left my job as an accountant because it bored me and in 2 weeks I’m starting a Full time Full Stack bootcamp because it really excites me, though I’m still fairly new (know enough to be dangerous)!

    • This topic was modified 1 month, 4 weeks ago by webbuildermn.
Viewing 8 replies - 1 through 8 (of 8 total)
  • Moderator bcworkz

    (@bcworkz)

    The users list table uses a different object type than for posts, so most admin table tutorials focusing on post types will not be that helpful in the specifics. The general concept of adding a column and using an action hook to produce output of each column element is the same. You’ll find the tables in this reference useful for other object types. Good information in the article above the #hooks section too.

    User names come from the usermeta table. For output of column elements, you’d use WP functions like get_userdata() or get_user_meta(), so the table structure isn’t that important. However, if you want your column to be sortable, you’ll need to look at the actual SQL query used in order to know if and how to sort by a particular user property. You can get it from WP_User_Query::request once the object has been instantiated. The usermeta table should be joined into the query, so sorting by meta data should be possible.

    Wow that is very helpful. Thank you, I’ll look into all of this.

    Thanks again. I read the article. I think it’s exactly what I was looking for. Maybe tomorrow I will try put it into practice. As far as SQL queries, I’m not sure, but our AdminColumns already runs a query I suppose, and gives us First Name and Last name. Maybe we can just hitch a ride on that – if the name of the column would already be in the array. I don’t know 100% what I’m saying, but we’ll see.

    I succeeded on a local instance of the site to create a column with “post id: ” . $post_ID displayed. I could try practice sorting that but I got it made. I just put the code in the functions.php file of the theme(child) but I made my first plugin today so I could try it there as it’s supposed to be better.

    Long story short, I think this article was what I needed, a lot to play with and test.

    1. I’m close to my goal but stuck near the end. There are two ways I can get to the end zone but I may need some help, if only a link or a point in the right direction.

    2. I’ve been using this reference to make sortable columns, taking it step by step. All my code is below. It is all inputted into the functions.php file of the theme editor. Edit: It is now in a custom plugin.

    3. I succeeded in adding custom column headers into my users table (Nova, Brilliant.org, “First Name” and “Last Name”. I could not get any data to be populated though, not when I used a variable $post_id or even when I just used echo. This same code worked in the post table yesterday. I got it to display echo 'post ID: '. $post_ID ; perfectly, but here in this table it’s giving me trouble.

    4. I also was able to make these columns sortable because it now shows FirstName as sortable link with the triangle button, even though there’s no data to sort.

    Where it goes wrong: I can’t get the first name or last name data. I don’t know how to do the SQL run.

    Alternatively, I had this plugin, AdminColumns that already has two columns populated, “First Name” and “Last Name”. If I could sort that, that would be the other way. What is interesting is I can’t even get a custom column to show up when that plugin is activated, so I have to deactivate it to get my code below to even work as expected. How/why does this occur?

    So those are my two ways- get the AdminColumns of “First Name” and “Last Name” to sort, or get my columns “FirstName” and “LastName” to get populated. I made them without spaces to differentiate the two.

    Honestly I’m surprised I got this far!! Appreciate all your inputs or even just pointers and links.

    
    // Experimental Sort Users //
    // 
    function add_custom_misha_columns($columns){
    	$columns["Nova"] = "Nova";
    	$columns["Brilliant.org"] = "Brilliant.org";
     	$columns["FirstName"] = "FirstName";
    	$columns["LastName"] = "LastName";
    	return $columns;
    }
    add_filter('manage_users_columns', 'add_custom_misha_columns');
    
    // // Populate the column
    function add_content_to_mishas_column($column_name, $post_id){ // edit: $user_id not $post_id
    	if ($column_name == "FirstName"){
    		echo "Romeo";
    	}
    	if ($column_name == "LastName"){
    		echo "Montague";
    	}
    }
    add_action('manage_users_custom_column', 'add_content_to_mishas_column', 10, 2);
    
    function sort_mishas_user_columns ($columns){
    	$columns["FirstName"] = "FirstName";
    	return $columns;
    }
    
    // Add sortable column to Column Array
    add_filter('manage_users_sortable_columns', 'sort_mishas_user_columns');
    
    
    • This reply was modified 1 month, 3 weeks ago by webbuildermn.
    • This reply was modified 1 month, 3 weeks ago by webbuildermn.

    I found something. See code at bottom. I got it to do what I intended in terms of static row population. Now we have to make this dynamic, first with a variable like user_id and then with SQL query. This is actually pretty fun.

    I guess the $column_Name occupies the second argument in the callback function, which is why it wasn’t finding anything before. I don’t know what the first argument holds. It seems to hold null. When I try to return it, nothing. I can return the $column_name and then every cell is the same as the header. This is logical to me.

    I don’t know how this:
    apply_filters( 'manage_users_custom_column', string $output, string $column_name, int $user_id )

    pertains and maps to my callback function because I get that the $output is the callback function. When I put 10, 2 it seems to work. When I try do anything else I get argument count errors or I don’t know hwo to connect them but my goal was to a) figure out what is argument one, i.e. $what_is, and b) get the $user_id info so I can echo it and also maybe it will be necessary for SQL query. I’ve been tryin got follow this code signature.

    Anyway here’s the code I got to statically populate the table!

    // // Populate the column - this works, statically!!
    function add_content_to_mishas_column($what_is, $column_name){
    	if ($column_name == "FirstName"){
    		return "Romeo";
    	}
    	if ($column_name == "LastName"){
    		return "Montague";
      }
    }
    add_filter('manage_users_custom_column', 'add_content_to_mishas_column', 10, 2);

    I think I will need this soon as well.

    • This reply was modified 1 month, 3 weeks ago by webbuildermn.

    Great Progress: The code below inputs my user_id. As you all know, the Action hook requires the number of parameters. I didn’t realize I was limiting it to 2. You can tell it it is greater than it is, but not less, or you’ll lose functionality. You also can’t pass more arguments to your callback function than it is expected to have according to the hook- in this case “manage_users_custom_column”. This is what I am intuiting. So when I change the add_action to 3 parameters, and add a parameter- “$user_id”, which is what it is, but I could call it whatever I want- $Waldo- then I have access to the User ID. The first parameter is a string and starts Null of course. You’d not be unwise to name it $Value, and then you can grab whatever value you want- for instance the returned value from a SQL query, which is what I have to do.

    So, I have the user ID and there are two candidate tables we can get the info from- Users and User_Meta. I already know it’s User_Meta from the discussion prior. Looking at the DB table User_meta, user_id is not a unique key, so that presents an extra challenge.

    There is a get_user_data($user_ID) which gets a user data object, which might work for populating the table but I am not sure it has sorting functionality as that might be linked to SQL order_by. I could play around with that to see if I can populate the table but I think I’ll pass.

    There it is, in all its glory- a wp_usermeta table with a non unique user_id and associated meta_key of first_name and one of last_name.

    So how to find that? I’ll do some studying and if I find anything I’ll share.

    Great progress.

    
    // // Populate the column
    function add_content_to_mishas_column($value, $column_name, $user_id){
      $value = $user_id;
    	if ($column_name == "FirstName"){
    		return "Romeo";
    	}
    	if ($column_name == "LastName"){
    		return "Montague";
      }
    
      if ($column_name == "Brilliant.org"){
        return $user_id;
      }
      if ($column_name == "Nova"){
        return $value;
      }
    }
    
    add_action('manage_users_custom_column', 'add_content_to_mishas_column', 10, 3);

    Edit: This is the SQL command I need. Now how to embed or deliver it and receive the return:

    SELECT meta_value
    FROM <code>wp_usermeta</code>
    where user_ID = 98   // or whatever the dynamic user_id is. 
    and 
    meta_key = "first_name";  // and again for last_name

    This returns the right value I need. Now just delivering the query and receiving output. This is fun.

    • This reply was modified 1 month, 3 weeks ago by webbuildermn.

    Wow, I have come a long way.

    I’m getting snagged at the end. I have all but completed my plug-in- entire code below.

    1. I get the first names and last names to show, pulled straight from the database, in their separate columns.

    2. These columns are sortable

    3. The problem is they sort based on the user name, not on their own column

    4. I read two articles about this, with different code to solve it. They each use different hooks. I tried them both, perhaps imperfectly.
    a) add_filter(‘request’, ‘bcw_sort_metabox’);
    b) add_action( ‘pre_get_posts’, ‘smashing_posts_orderby’ );

    I got very far, almost to the end but I can only sort by the first column, not by the column clicked.

    An idea is that I know this relates to querying the database differently and clicking the triangle is basically toggling ASC and DESC in SQL. However, my query selects only one cell each time, so I wonder if that plays a role.

    Here’s the code

    <?php
    /**
     * Plugin Name: j-w-m
     * Plugin URI: http://www.journeywithmisha.com/j-w-m
     * Description: The very first plugin that I have ever created.
     * Version: 1.0
     * Author: Gerald Ryan
     * Author URI: http://www.webbuildermn.com
     */
    
    // Experimental Sort Users //
    // 
    function add_custom_misha_columns($columns){
    	$columns["Nova"] = "Nova";
    	$columns["Brilliant.org"] = "Brilliant.org";
     	$columns["FirstName"] = "FirstName";
    	$columns["LastName"] = "LastName";
    	return $columns;
    }
    add_filter('manage_users_columns', 'add_custom_misha_columns');
    
    // // Populate the column
    function add_content_to_mishas_column($value, $column_name, $user_id){
      global $wpdb;
      $fname = $wpdb->get_var("SELECT meta_value FROM $wpdb->usermeta WHERE user_ID = $user_id AND meta_key = 'first_name' "  );
      $lname = $wpdb->get_var("SELECT meta_value FROM $wpdb->usermeta WHERE user_ID = $user_id AND meta_key = 'last_name' "  );
    	if ($column_name == "FirstName"){
        return $fname;
        // return "Romeo";
    	}
    	if ($column_name == "LastName"){
        return $lname;
        // return "Montague";
      }
    
      if ($column_name == "Brilliant.org"){
        return $user_id;
      }
      if ($column_name == "Nova"){
        return $value;
      }
    }
    
    add_action('manage_users_custom_column', 'add_content_to_mishas_column', 10, 3);
    
    function sort_mishas_user_columns ($columns){
      $columns["FirstName"] = "FirstName";
      $columns["LastName"] = "LastName";
    	return $columns;
    }
    
    // Add sortable column to Column Array
    add_filter('manage_users_sortable_columns', 'sort_mishas_user_columns');
    
    // // make our columns sortable based on right factor
    // add_action( 'pre_get_posts', 'user_az_orderby' );
    // function user_az_orderby( $query ) {
    //   if ( 'FirstName' === $query->get( 'orderby') ) {
    //     $query->set( 'orderby', 'meta_value' );
    //     $query->set( 'meta_key', 'FirstName' );
    //   }
    // }
    
    function user_az_orderby($vars) { 
      if(array_key_exists('orderby', $vars)) { 
        if('LastName' == $vars['orderby']) { 
          $vars['orderby'] = 'LastName'; 
          // $vars['meta_key'] = 'LastName'; 
        } 
      } 
      return $vars; 
    } 
    add_filter('request', 'user_az_orderby');
    Moderator bcworkz

    (@bcworkz)

    I’m glad you’re enjoying the learning of new things. That attitude will serve you well.

    In your last reply’s statement 4, either hook will work (except “pre_get_users”, not “pre_get_posts”). You are looking for and resetting the same “orderby” query var either way. “request” is more generally applicable than “pre_get_*” When you click the column head, not only is the order direction toggled, your column name is set as the “orderby” value. The problem is WP_User_Query has no idea how to orderby your column name. But your callback can recognize the name and reset the orderby query var to a useful value.

    You can only set “orderby” to a value accepted by WP_User_Query. To order by meta values, set “orderby” to 'meta_value' for any non-numeric meta value. So SQL knows what meta key value to sort by, you need to set “meta_key” query var to the correct meta key value. This could be a problem if the query is supposed to involve the “meta_query” query var instead of “meta_key”, but it doesn’t appear to be the case here.

    You might consider getting user meta with get_user_meta() instead of making a separate SQL query for each value. While that will work, that’s an awful lot of queries for one page. get_user_meta() first tries to get data from a cache before making a SQL query, which may or may not be helpful. You have much to gain and little to lose.

Viewing 8 replies - 1 through 8 (of 8 total)
  • You must be logged in to reply to this topic.