[amandafrench]

Never mind! I’ve found a fabulous solution that has gotten me out of SQL query hell. Again, if anyone else is trying to sort a Members Directory, here’s what I did:

1) Put it in a table, not a list (I had done this anyway, since I wanted a single-page directory that displayed xprofile fields horizontally) — make sure it has a <th> table header row;
2) Install the plugin Table Sorter https://wordpress.org/plugins/table-sorter/ and add the class “tablesorter” to the table.

Et voilà! I commented out the sort type dropdown in members/index.php starting with
<div class="item-list-tabs" id="subnav" aria-label="<?php esc_attr_e( 'Members directory secondary navigation', 'buddypress' ); ?>" role="navigation">
all the way through

<?php
/**
 * Fires inside the members directory member order options.
 *
 * @since 1.2.0
 */
do_action( 'bp_members_directory_order_options' ); ?>
</select>
</li>
</ul>
</div>

and I made sure that the default single page list showed all users alphabetically by default in members-loop.php

<?php if ( bp_get_current_member_type() ) : ?>
	<p class="current-member-type"><?php bp_current_member_type_message() ?></p>
<?php endif; ?>

<?php if ( bp_has_members( bp_ajax_querystring( 'members' ) . '&per_page=500' . '&exclude=1,2,3,4,6' . '&type=alphabetical') ) : ?>

And then all I had to do was add that tablesorter class to the table as required by the tablesorter plugin, and I got a nice dynamic table with sortable columns, which can also be sorted by multiple criteria by holding down the Shift key on the second column. I set the initial sort order with parameters that select the column by number, as described at http://tablesorter.com/docs/example-option-sort-list.html and elsewhere.

<table id="members-list" class="tablesorter {sortlist:[[4,0], [3,0], [2,0], [1,0], [0,0]]}" aria-live="assertive" aria-relevant="all">

Yay.

[amandafrench]

I guess I’m never satisfied, but I do have an additional request / problem, if someone can help (cough @boonebgorges?). Our xprofile fields are geographical: City, State, Country. I’d like to do a sequential sort on them on the Members directory: first ASC by Country (which I’ve now got working, finally, thanks to this post), then second ASC by State, third ASC by City. I’ve mucked around with SQL statements and ORDER BY FIELD and CASE IF THEN, but I can’t get those subsequent sorts to work.

xprofile field_id values: 1 = Name, 8 = Org, 9 = City, 10 = State, 11 = Country

Members loop code:

<?php if ( bp_has_members( bp_ajax_querystring( 'members' ) . '&per_page=500' . '&populate_extras&type=alphabetical' . '&exclude=1,2,3,4,6') ) : ?>

Below code in functions.php works – it sorts members by Country ASC
custom_ids = $wpdb->get_col("SELECT user_id FROM {$wpdb->prefix}bp_xprofile_data WHERE field_id = 11 ORDER BY value ASC");

Below code in functions.php doesn’t work to sort by Country, State, City ASC – returns an unsorted members list. I think it’s sorting randomly. It isn’t by user id, username, last active, or newest registered. When I apply the default member type sorts, though, with the members-order-select dropdown, that works.

$custom_ids = $wpdb->get_col("SELECT user_id FROM {$wpdb->prefix}bp_xprofile_data WHERE field_id IN ('1', '8', '9', '10', '11') ORDER BY FIELD (field_id, '11', '10', '9', '8', '1')");

Any advice for constructing that SQL query? Do I need to look more into bp_parse_args?

[amandafrench]

Thank you SO much, @awesomewebguy!! This worked great for me: I was tearing my hair out trying to figure out how to sort the members directory by an xprofile field. All the good karma to you!