Skip to:
Content
Pages
Categories
Search
Top
Bottom

Creating a new "order by" filter in Groups Loop with custom SQL

  • @antaresmhd

    Participant

    9 years, 8 months ago

    Hey everyone. Here’s the deal:
    I’m trying to add a new “Order by” clause in the filter section of the Groups loop, but not using metadata from buddypress but another plugin table installed in my site. I’ve been looking at this thread for a while https://buddypress.org/support/topic/adding-new-order-by-on-members-loop/ and also this https://codex.buddypress.org/plugindev/add-custom-filters-to-loops-and-enjoy-them-within-your-plugin/#add-a-custom-filter-to-the-members-groups-and-blogs-loops, but none quite work the way I want to, or maybe I’m just missing something because I’m unable to do it.

    I made this MySQL query:

    SELECT 
	g.id as id
FROM 
	'wp_user_league_prediction' as p,
	'wp_bp_groups_members' as m,
	'wp_bp_groups' as g
WHERE 
	g.id = m.group_id AND m.user_id = p.user_id
GROUP BY
	m.group_id

    Which queries for groups following the criteria I need (groups whose users are also present in that table referenced there) and I want to use it (or something modified for that matter) to search in the ajax of the Groups Loop filter, so I tried to repurpose the code in the first link like this 

    function groups_sortby_league( $object ) {
    global $wpdb, $bp;	
	
    if ( ! in_array( $object->query_vars['type'], array( 'user_league' ) ) ) 
	return;
	

    $field_id = $wpdb->get_var( $wpdb->prepare( "SELECT g.id FROM  {$wpdb->prefix}%s_prediction as p, {$wpdb->prefix}bp_groups_members as m, {$wpdb->prefix}bp_groups as g WHERE g.id = m.group_id AND m.user_id = p.user_id GROUP BY m.group_id", $object->query_vars['type'] ) );
		
     $object->gid_name = 'group_id';
     $object->gid_table = $bp->group->table_name_data;
     $object->gid_clauses['select']  = "SELECT u.{$object->gid_name} as id FROM {$object->gid_table} u";
     $object->gid_clauses['where'] = " WHERE " . $wpdb->prepare( "u.field_id = %d", $field_id ); 
     $object->gid_clauses['orderby'] = "ORDER BY u.value";
     $object->gid_clauses['order']   = "ASC";		
		
}
add_action( 'bp_pre_group_query', 'groups_sortby_user_league' );

    And while the new filter option does show up, it doesn’t seem like it’s doing anything. I thought the group id would be enough for this but clearly there’s something not quite working and I don’t think it’s the query itself. I don’t know what to do with the $object after that, or what does it do when the $field_id is already referenced.

    I really would appreciate some help with this, since this part of buddypress (ajax queries) has always stumped me.

Viewing 4 replies - 1 through 4 (of 4 total)

  • @shanebp

    Moderator

    9 years, 8 months ago

    Where are you getting bp_pre_group_query from?
    You can’t make up hooks :>
    See BP_Groups_Group::get in buddypress\bp-groups\bp-groups-classes.php for more info.

    Try this:

    
function antares_groups_filter_options() {
    echo '<option value="user-league">User League</option>';
}
add_action( 'bp_groups_directory_order_options', 'antares_groups_filter_options' );

function antares_ajax_querystring( $query_string, $object ) {

	if ( 'groups' != $object ) 
		return $query_string;

	if ( ! bp_is_groups_directory() ) 
		return $query_string;
	
	$query_args = wp_parse_args( $query_string, array() );

        $page = $query_args['page'];

	if( isset( $query_args['action'] ) && $query_args['action'] == 'user-league' ) {
	
		$query_args = array();
		$query_args['page'] = $page;
		$query_args['orderby'] = 'name';		
		$query_args['order'] = 'ASC';			
		$query_args['include'] = antares_get_groups(); 
		$query_string = http_build_query( $query_args );

	}

	return $query_string;

}
add_filter( 'bp_ajax_querystring', 'antares_ajax_querystring', 32, 2 );

function antares_get_groups() {

    // put your custom sql here
    // return a csv string of the groups ids
    // for example '1,22,57'

}

    @antaresmhd

    Participant

    9 years, 8 months ago

    Yeah, about that… You’re right, obviously, I just assumed that since bp_pre_user_query existed, a group equivalent did as well, but apparently not! As you can imagine, I tried everything I could think of in order to make it work somehow.

    Anyway, I did the changes you proposed with the added custom query (like this)

    function antares_get_groups() {
global $wpdb;

$sql = "SELECT g.id
FROM 
	{$wpdb->prefix}user_league_prediction as p,
	{$wpdb->prefix}bp_groups_members as m,
	{$wpdb->prefix}bp_groups as g
WHERE 
	g.id = m.group_id AND m.user_id = p.user_id
GROUP BY
	m.group_id";

$buff = array();

$result = $wpdb->get_results( $sql , OBJECT );

		foreach ($result as $row) {
			$buff[]= $row->id ;
		}
		
$query_str= implode (',', $buff);
return $query_str;
}

    And it works! The only issue now is with the pagination, because even though it’s reading the number of groups correctly and displaying them on the list, when I click on the numbers it just doesn’t do anything. I checked the web console and the parameter 2&num is being passed correctly but it doesn’t do a thing. I suspect this is another problem with ajax.

    Either way you’ve been of great help, thank you.

    @shanebp

    Moderator

    9 years, 8 months ago

    Re pagination:
    It’s probably due to my code wiping out the query array and hardcoding page = 1

    There is most likely an easier / better way, but try storing the page in a var before the if( isset conditional:

    $page = $query_args['page'];

    Then put it back into the array in the conditional:

    $query_args['page'] = $page;

    @antaresmhd

    Participant

    9 years, 8 months ago

    Excellent, that did the trick. Thanks a lot for your assistance, this was just what I needed.

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Creating a new "order by" filter in Groups Loop with custom SQL’ is closed to new replies.

Topic Info

Topic Tags

Skip to toolbar