1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

From BuddyPress Real Names, http://wordpress.org/extend/plugins/buddypress-real-names/
how to filter members alphabetically using another field that the default one (Name) or multiple field.

##

//add filter on the members query
add_action( 'bp_pre_user_query', filter_query_members_alphabetical');

        function filter_query_members_alphabetical($query){
            global $bp;
            
            //check this is an alphabetical search
            if($query->query_vars['type']!='alphabetical') return $query;

            //get fields ids used to sort the members
            $fields_ids=array(...); // array of ids, eg. array(1,2,3,4);
            
            if(!$fields_ids) return $query;

            
            if(count($fields_ids)==1){//ORDER USING UNIQUE FIELD

                
                if ($fields_ids[0]==1) return $query;//default field - acts like core, escape filter
                
                $clauses['select']  = "SELECT DISTINCT u.user_id as id FROM {$bp->profile->table_name_data} u";
                $clauses['where'][] = "u.field_id = {$fields_ids[0]}";
                $clauses['where'][] = "u.value IS NOT NULL";
                $clauses['orderby'] = "ORDER BY u.value";
            
                
            }else{ //ORDER USING MULTIPLE FIELDS
                
                $select[]="SELECT u.user_id as id";

                //emulate fields
                foreach((array)$fields_ids as $field_id){
                    $dummy_name = 'dummy_field_'.$field_id;
                    $select_max[]="MAX(CASE WHEN field_id = {$field_id} THEN value ELSE null END) AS {$dummy_name}";
                    //check the field is not empty
                    $having[]="{$dummy_name} IS NOT NULL";
                    $orderby[]=$dummy_name;
                }

                if($select_max)$select[]=', '.implode(', ',$select_max);
                $select[]="FROM {$bp->profile->table_name_data} u";
                $select[]="GROUP BY u.user_id";
                
                
                if($having)$select[]="HAVING ".implode(" AND ",$having);


                $clauses['select']=implode(" ",$select);


                if($orderby)$clauses['orderby'] ="ORDER BY ".implode(',',$orderby);
                
            }
            
            $clauses['where']   = ! empty( $clauses['where'] ) ? 'WHERE ' . implode( ' AND ', $clauses['where'] ) : '';
  
            $query->uid_clauses = wp_parse_args($clauses,$query->uid_clauses);

            return $query;
        }