Sort results by distance using Formidable Pro hook WordPress

• REFUNDED

I currently use the Formidable Pro wordpress plugin which is a form builder that allow entries to be shown in a custom view.

I have managed to get address entries which have been geocoded and stored in Formidable Pro to show on a page and have it sorted by distance from a lat/long coordinate. The code below works:


$userlat = $_GET['lat']; //get latitude from url
$userlong = $_GET['long']; //get longitude from url
global $wpdb;
$sql = "SELECT wp_frm_item_metas.item_id, tutorlat.meta_value, tutorlong.meta_value, ( 3959 * acos( cos( radians( $userlat ) ) * cos( radians( tutorlat.meta_value ) ) * cos( radians( tutorlong.meta_value ) - radians( $userlong ) ) + sin( radians( $userlat ) ) * sin( radians( tutorlat.meta_value ) ) ) ) AS distance
FROM wp_frm_item_metas
INNER JOIN wp_frm_item_metas tutorlat
ON wp_frm_item_metas.item_id = tutorlat.item_id
AND tutorlat.field_id = '721'
INNER JOIN wp_frm_item_metas tutorlong
ON wp_frm_item_metas.item_id = tutorlong.item_id
AND tutorlong.field_id = '722'
GROUP BY wp_frm_item_metas.item_id
ORDER BY distance ASC";
$item = $wpdb->get_results($sql);

print("<ul>");
foreach ($item as $post)
{
print('<li>'.$post->item_id.'|'.$post->distance.'|'.$post->tutorlat.meta_value.'<br/>');
print('</li>');
}
print("</ul>");


What I would like to do is have entries sorted by distance in formidable's custom view using their frm_view_order hook (https://formidablepro.com/knowledgebase/frm_view_order/)


Formidable's support staff have suggested the following:
"We started putting our database calls in arrays to more easily prepare the data. The original order query looks like something like this:


$query['select'] = "SELECT it.id FROM wp_frm_items it";
$query['where'] = "WHERE it.form_id=585 AND is_draft=0";
$query['order'] = "ORDER BY it.created_at ASC";


I would recommend setting each of those items in your code so they're all passed to Formidable with return $query"

The final hook would look something like this:

//add_filter('frm_view_order', 'change_view_order', 10, 2);
function change_view_order($query, $args){
if ( $args['display']->ID == 490 ) { //custom view ID
$query['select'] = "SELECT ...";
$query['where'] = "WHERE ...";
$query['order'] = "ORDER ...";
}
return $query;


As I have just started learning php, how do I integrate the first working distance sort code into the frm_view_order hook above?

Thanks.

Answers (2)

2015-06-16

Hariprasad Vijayan answers:

Hi,

Just a thought, have you tried like this?
add_filter('frm_view_order', 'change_view_order', 10, 2);
function change_view_order($query, $args){
if ( $args['display']->ID == 490 ) { //custom view ID
$userlat = $_GET['lat']; //get latitude from url
$userlong = $_GET['long']; //get longitude from url
$query['select'] = "SELECT wp_frm_item_metas.item_id, tutorlat.meta_value, tutorlong.meta_value, ( 3959 * acos( cos( radians( $userlat ) ) * cos( radians( tutorlat.meta_value ) ) * cos( radians( tutorlong.meta_value ) - radians( $userlong ) ) + sin( radians( $userlat ) ) * sin( radians( tutorlat.meta_value ) ) ) ) AS distance
FROM wp_frm_item_metas
INNER JOIN wp_frm_item_metas tutorlat
ON wp_frm_item_metas.item_id = tutorlat.item_id
AND tutorlat.field_id = '721'
INNER JOIN wp_frm_item_metas tutorlong
ON wp_frm_item_metas.item_id = tutorlong.item_id
AND tutorlong.field_id = '722'
GROUP BY wp_frm_item_metas.item_id";

$query['where'] = "";

$query['order'] = "ORDER BY distance ASC";

}
return $query;
}
Not sure about your custom view ID, may be need to change.

---

Rav Pham comments:

Hi, I have tried that and the custom view shows no entries.

Going back to the basic hook, the following code will show all entries.

add_filter('frm_view_order', 'change_view_order', 10, 2);
function change_view_order($query, $args){
if ( $args['display']->ID == 490 ) { //custom view ID

$query['select'] = "SELECT it.* FROM wp_frm_items it";
$query['where'] = "";
$query['order'] = "";
}
return $query;
}



I somehow need to incorporate a SELECT wp_frm_item_metas so that the stored latitudes and longitudes are retrieved. The formidable database schema is shown here: https://formidablepro.com/knowledgebase/database-schema/

---

Hariprasad Vijayan comments:

Hi,

Can you send your database dump to try another sql query?

Regards,
Hariprasad

2015-06-16

MJ answers:

It does seem to me like you would need to include the WHERE clause. It looks like that's how it identifies which form you are talking about and whether it's been published or is a draft. So I would try as Hariprasad says but include:

$query['where'] = "WHERE it.form_id=585 AND is_draft=0";

putting the correct form ID in it.

---

Rav Pham comments:

Hi MJ, without the where clause, the entries will still show up in the required custom view using the following:
if ( $args['display']->ID == 490 ) { //custom view ID

Thanks though.

---

MJ comments:

Hi Rav,

Queries without where clauses can be dangerous if they return large datasets, but let's assume it's fine. I'm a little stumped, perhaps if we narrow it down: try going back to your original hook but adding SELECT id FROM wp_frm_item_metas as your select clause (add also a where clause if it's a big dataset and you're in production).

Does that return anything for your view?

---

Rav Pham comments:

Hi MJ, thanks for your help. I managed to figure this out. The INNER JOIN ON clause was meant to reference the id column in the wp_frm_items table. Thanks for your help.

---

MJ comments:

Perhaps I misunderstood then, because I was under the impression the query you originally posted did what you wanted. But yes, if you need info from more than one table you need to join them on their keys. Glad it's working now.