[buddypress-trac] [BuddyPress Trac] #7290: 2.7.0-rc2 breaks GeomyWP + Groups Locator groups list
buddypress-trac
noreply at wordpress.org
Sun Nov 6 05:51:25 UTC 2016
#7290: 2.7.0-rc2 breaks GeomyWP + Groups Locator groups list
-------------------------------+-----------------------
Reporter: dreadedhamish | Owner:
Type: defect (bug) | Status: reopened
Priority: highest | Milestone:
Component: Groups | Version:
Severity: normal | Resolution:
Keywords: reporter-feedback |
-------------------------------+-----------------------
Comment (by ninjew):
OK, looks like I got things working again. I am still testing it but so
far it looks good.
At first I tweaked a bit the script above provided by @boonebgorges (
Thank you ). The end results were 2 parts.
Part 1:
{{{#!php
<?php
function filter_group_query( $query, $sql, $r ) {
// abort if no address entered. There is no need for proximity
search.
if ( empty( $this->form['org_address'] ) ) {
return $query;
}
global $wpdb;
$table_name = "{$wpdb->prefix}gmw_groups_locator";
$subquery = $wpdb->prepare( "
SELECT gg.id
FROM `$table_name` gg
WHERE ROUND( %d * acos( cos( radians( %s ) ) * cos(
radians( gg.lat ) ) * cos( radians( gg.lng ) - radians( %s ) ) + sin(
radians( %s ) ) * sin( radians( gg.lat) ) ),1 ) <= %d",
$this->form['units_array']['radius'],
$this->form['your_lat'],
$this->form['your_lng'],
$this->form['your_lat'],
$this->form['radius']
);
$where = "WHERE g.id IN ($subquery)";
// if where clause already exist.
if ( ! empty( $sql['where'] ) ) {
$where .= " AND " . $sql['where'];
}
// return groups query
if ( current_filter() == 'bp_groups_get_paged_groups_sql' ) {
return "{$sql['select']} FROM {$sql['from']} {$where}
{$sql['orderby']} {$sql['pagination']}";
// return groups total query
} else {
return "SELECT COUNT(DISTINCT g.id) FROM {$sql['from']}
$where";
}
}
}}}
This part of the code filters the groups by location and distance when
address entered in the search form.
Note that I didn't use the "parts" method you suggested by using
{{{#!php
<?php
$parts = explode( 'WHERE', $query );
}}}
as it did not work for all scenarios. In the search form created by GEO
my WP there is a "order by" dropdown which allows the user to change the
order of the results. When the results are ordered by either
"Alphabetically" or "Newly created", there is no "WHERE" clause added the
the SQL query which "breaks" the function above ( when using the "parts"
method ). Perhaps having WHERE 1 = 1 instead of omitting the where clause
would be helpful in such scenarios.
The second part of the code is:
{{{#!php
<?php
function add_group_data( $groups ) {
global $wpdb;
$group_ids = wp_list_pluck( $groups['groups'], 'id' );
$group_ids_sql = implode( ',', array_map( 'intval', $group_ids )
);
if ( empty( $group_ids_sql ) ) {
return $groups;
}
// if address entered and doing proximity search
if ( ! empty( $this->form['org_address'] ) ) {
$map_data = $wpdb->get_results(
$wpdb->prepare( "
SELECT gg.id, gg.lat, gg.lng as `long`,
gg.address, gg.formatted_address, gg.map_icon, ROUND( %d * acos( cos(
radians( %s ) ) * cos( radians( gg.lat ) ) * cos( radians( gg.lng ) -
radians( %s ) ) + sin( radians( %s ) ) * sin( radians( gg.lat) ) ),1 ) AS
distance
FROM {$wpdb->prefix}gmw_groups_locator gg
WHERE gg.id IN ( {$group_ids_sql} )",
$this->form['units_array']['radius'],
$this->form['your_lat'],
$this->form['your_lng'],
$this->form['your_lat'],
$this->form['radius']
)
);
//otherwise, just collect data from location table.
} else {
$map_data = $wpdb->get_results( "
SELECT gg.id, gg.lat, gg.lng as `long`,
gg.address, gg.formatted_address, gg.map_icon
FROM {$wpdb->prefix}gmw_groups_locator gg
WHERE gg.id IN ( {$group_ids_sql} )"
);
}
// Add located data to BP's group objects
foreach ( $map_data as $group_data ) {
// you might be able to optimize this so you don't need nested
loops
foreach ( $groups['groups'] as &$group ) {
if ( $group_data->id == $group->id ) {
$group->lat = $group_data->lat;
$group->lng = $group_data->long;
$group->address = $group_data->address;
$group->formatted_address =
$group_data->formatted_address;
$group->map_icon = $group_data->map_icon;
if ( ! empty( $group_data->distance ) ) {
$group->distance = $group_data->distance;
}
// etc
break;
}
}
}
$orderby = ! empty( $_GET['gmw_orderby'] ) ? $_GET['gmw_orderby']
: 'distance';
if ( $orderby == 'distance' ) {
usort( $groups['groups'], array( $this,
'order_results_by_distance' ) );
}
return $groups;
}
}}}
This function collects the geolocation data from the locations table and
add it to each groups object. Note that at the end of the function I used
this:
{{{#!php
<?php
if ( $orderby == 'distance' ) {
usort( $groups['groups'], array( $this, 'order_results_by_distance' )
);
}
}}}
to order the results by the distance ( when choosing "distance" from the
order by dropdown ). The order by also used to happen in the main query
because I was able to SELECT the distance using the ROUND function and
then do ORDER BY distance. But that doesn't seem to be possible now.
I tested the above and it was working well.
However, I then came up with a different method which to me seems more
efficient. I might be wrong and any input would be greatly appreciated.
I first added a new public function:
{{{#!php
<?php
public function get_groups_based_location() {
global $wpdb;
$table_name = "{$wpdb->prefix}gmw_groups_locator";
// if address entered, do a proximity search and get locations
within the radius entered.
if ( ! empty( $this->form['org_address'] ) ) {
$groups = $wpdb->get_results(
$wpdb->prepare( "
SELECT gg.id, gg.lat, gg.lng as `long`,
gg.address, gg.formatted_address, gg.map_icon,
ROUND( %d * acos( cos( radians( %s ) ) *
cos( radians( gg.lat ) ) * cos( radians( gg.lng ) - radians( %s ) ) + sin(
radians( %s ) ) * sin( radians( gg.lat) ) ),1 ) as distance
FROM `$table_name` gg
HAVING distance <= %d OR distance IS
NULL",
$this->form['units_array']['radius'],
$this->form['your_lat'],
$this->form['your_lng'],
$this->form['your_lat'],
$this->form['radius']
)
);
//Otherwise, get all groups location.
} else {
$groups = $wpdb->get_results( "
SELECT gg.id, gg.lat, gg.lng as `long`,
gg.address, gg.formatted_address, gg.map_icon
FROM `$table_name` gg"
);
}
return $groups;
}
}}}
The function will either get the location data of all groups or groups
that are nearby the address entered.
I call the function before the bp_has_groups() function fires:
{{{#!php
<?php
$this->gmw_groups = $this->get_groups_based_location();
}}}
Then the first part of the code above is now:
{{{#!php
<?php
function filter_group_query( $query, $sql, $r ) {
// abort if no address entered
if ( empty( $this->form['org_address'] ) ) {
return $query;
}
global $wpdb;
// get list of groups ID
$group_ids = wp_list_pluck( $this->gmw_groups, 'id' );
$group_ids_sql = implode( ',', array_map( 'intval', $group_ids )
);
// Filter the main query based on the location groups ID
// if groups with location exist
if ( ! empty( $group_ids_sql ) ) {
$where = "WHERE g.id IN ( {$group_ids_sql} )";
// otherwise show no results
} else {
$where = "WHERE 1 = 0";
}
if ( ! empty( $sql['where'] ) ) {
$where .= " AND " . $sql['where'];
}
// return groups query
if ( current_filter() == 'bp_groups_get_paged_groups_sql' ) {
return "{$sql['select']} FROM {$sql['from']} {$where}
{$sql['orderby']} {$sql['pagination']}";
// return total groups query
} else {
return "SELECT COUNT(DISTINCT g.id) FROM {$sql['from']}
$where";
}
}
}}}
And the second part of the code is now:
{{{#!php
<?php
function add_group_data( $groups ) {
global $wpdb;
// Add located data to BP's group objects
foreach ( $this->gmw_groups as $group_data ) {
// you might be able to optimize this so you don't need nested
loops
foreach ( $groups['groups'] as &$group ) {
if ( $group_data->id == $group->id ) {
$group->lat =
$group_data->lat;
$group->lng =
$group_data->long;
$group->address = $group_data->address;
$group->formatted_address =
$group_data->formatted_address;
$group->map_icon = $group_data->map_icon;
if ( ! empty( $group_data->distance ) ) {
$group->distance = $group_data->distance;
}
// etc
break;
}
}
}
$orderby = ! empty( $_GET['gmw_orderby'] ) ? $_GET['gmw_orderby']
: 'distance';
if ( $orderby == 'distance' ) {
usort( $groups['groups'], array( $this,
'order_results_by_distance' ) );
}
return $groups;
}
}}}
The new method does the proximity SQL Query only once using the
get_groups_based_location() function instead of having it 3 times for the
`filter_group_query`, `filter_group_query` and `add_group_data` filters.
Which I believe will improve performance.
I don't like the nested loops and the orderby distance function in the
groups_data function. But it seems to be working and I will probably
release it as an update for now.
I am going to look and see if it will be possible to have a better method
if a hook will be added to the populate() functions.
Any feedback regarding the above is greatly appreciated.
Sorry for the long post.
--
Ticket URL: <https://buddypress.trac.wordpress.org/ticket/7290#comment:27>
BuddyPress Trac <http://buddypress.org/>
BuddyPress Trac
More information about the buddypress-trac
mailing list