• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

Custom code in Search Form

Hi I have followed the tutorial on creating a search form for a list.

Now we want to extend this by having a custom radius search option in a form.

Contacts in the list have lat and long. I want to use a custom query to populate the list.

Can anyone offer some guidance on this, please. Any help would be highly appreciated

In this certain circumstance, i cant use the radius search.
 
I've done custom radius searching using pre-filters, using a Haversine formula query. Assuming the target lat and lon are passed in on the query string to the list, you can use them as placeholders in a prefilter, as a 'query' type (dependent subquery).

WHERE
field: id
condition: IN
value: SELECT id FROM yourtable WHERE ... Haversine formula here with {your_lat_query_string_arg} and {your_lon_query_string_arg} as placeholders
type: query

If you aren't passing the lat and lon directly, rather doing something like passing an FK (so a join element to another table with the lat and lon) you can do pre-query to fetch the lat and lon ...

SELECT lat, lon FROM othertable WHERE id = '{___your_fk_raw}'

... and then use {$q->lat} and {$q->lon} in the prefilter query.

-- hugh
 
Thanks alot. Hugh. Another question went across my mind.

In my search form i have a field that says postcode. How would I go about and convert that to LAT and LONG before its submitted to search the form (Where would I write the PHP code)


Thanks in advance
Regards
 
Last edited:
In a form submission script, running onBeforeProcess.

Here's a code fragment from a script I use for doing something very similar:

Code:
// include gmaps2 lib
require_once JPATH_SITE . '/plugins/fabrik_cron/geocode/libs/gmaps2.php';

// set the form prefix (leave blank for tableless form)
$formPrefix = '';

// get the API key and verify peer workaround from Fabrik config
$config = JComponentHelper::getParams('com_fabrik');
$apiKey     = $config->get('google_api_key', '');
$verifyPeer = (bool) $config->get('verify_peer', '1');

// get the GeoCode instance
$gmap       = new GeoCode($verifyPeer);

// get the address
$fullAddr = array();
$fullAddr[] = $formModel->formData[$formPrefix . '___street_address'];
$fullAddr[] = $formModel->formData[$formPrefix . '___city'];
$fullAddr[] = $formModel->formData[$formPrefix . '___state'];
$fullAddr[] = $$formModel->formData[$formPrefix . '___zip'];

// create the flat address string, and fire off the request
$fullAddr = implode(',', array_filter($fullAddr));
$fullAddr = urlencode(html_entity_decode($fullAddr, ENT_QUOTES));
$res      = $gmap->getLatLng($fullAddr, 'array', $apiKey);

// check the response
if ($res['status'] == 'OK')
{
   // we got a response, so update the lat and lon element data
   $formModel->updateFormData($formPrefix . '___lat', $res['lat'], true, true);
   $formModel->updateFormData($formPrefix . '___lon', $res['lng'], true, true);

   return true;
}
else
{
   // meh, no response, so fail the submission (fake out a validation error)
   $formModel->errors['foobar'] = array(FText::_('Unable to geocode address!'));
   $formModel->formErrorMsg       = FText::_('We were unable to find your address, please check and try again.');

   return false;
}

Obviously you'll need to tweak the element names in the address. If you are just using a single postcode field, you can do away with all the $fullAddr array creation and imploding, and just do ...

Code:
$fullAddr = $formModel->formData[$formPrefix . '___postcode'];
$fullAddr = urlencode(html_entity_decode($fullAddr, ENT_QUOTES));
$res      = $gmap->getLatLng($fullAddr, 'array', $apiKey);

Also, create the 'lat' and 'lon' elements on your form. Simple fields, hidden.

Then you can use normal element placeholders in your prefilter.

Note that this code is adapted off the top of my head from a more complex variation, so I haven't actually tested that exact code. However, assuming no typos or silly mistakes, it should work. I use that gmaps.php lib a lot.

You can also find example code in the geocode cron plugin.

The $formPrefix is basically just your table name, ie. the part before the ___ in the full element name.

-- hugh
 
BTW, if you are currently using a "tableless" form, you might consider using a normal form with a table for the search form. In every site where I do this kind of stuff, we've found it's useful to record what people actually search for.

-- hugh
 
BTW, the 'verify peer' thing is a workaround you can enable in the global Fabrik options, if you are running in an environment that doesn't correctly handle SSL peer verification when making https connections. That's usually true if you are running a test setup on a Windows XAMPP (or similar) box, where the distribution doesn't include an up to date cert bundle.

So if you get weird errors about not being able to connect to Google's API, you may have to enable that option in the global config.

-- hugh
 
Hello Hugh.

I have tried your method to put it as a Query and i get an error in the front end. I have tried different ways but its the same thing

Please find the image that's attached

Below is my Query

SELECT contact_id, ( 3959 * acos( cos( radians(‘{advanced_search___lat}’) ) * cos( radians( geo_code_1 ) ) * cos( radians( geo_code_2) - radians(‘{advanced_search___lon}’) ) + sin( radians(‘{advanced_search___lat}’) ) * sin( radians(geo_code_1) ) ) ) AS distance FROM civicrm_address HAVING distance < 10 ORDER BY distance


Can you please point me what should I be doing instead
 
When you use an IN() clause, with a subquery, which is what that prefilter does, it wraps your query in an IN(), like ...

AND id IN(SELECT ...)

... that subquery can only have a single field selection, otherwise the IN() doesn't know what it's supposed to compare against.

So try ...

Code:
SELECT
    contact_id
FROM
    civicrm_address
WHERE
    (
        3959 * acos(
            cos(
                radians(
                    '{advanced_search___lat}'
                )
            ) * cos(radians(geo_code_1)) * cos(
                radians(geo_code_2) - radians(
                    '{advanced_search___lon}'
                )
            ) + sin(
                radians(
                    '{advanced_search___lat}'
                )
            ) * sin(radians(geo_code_1))
        )
    ) < 10

Also, I don't know if it was just the act of copying and pasting your query, but your placeholders had space padding, and weird quotes. I've fixed that to be just simple single quotes and no spaces.

And I ran it through a pretty printer so I could check the syntax.

-- hugh
 
You are awesome mate. People like you make this forum really helpful. So Thanks, Hugh .

Got a couple of issues.

I have tried this and it works just fine. Below is how I did the prefilter before you post the above reply.

Field: id
Type: Eval
Condition: In

Code:
if(isset($_GET['advanced_search___lat'])) {
   $center_lng = $_GET['advanced_search___lon'];
$center_lat =  $_GET['advanced_search___lat'];
//$center_lat = {advanced_search___lat};
//$center_lng = {advanced_search___lon};
$radius = 10;

$db = JFactory::getDbo();
$db->setQuery(JText::sprintf("SELECT contact_id, ( 3959 * acos( cos( radians('%s') ) * cos( radians(     geo_code_1 ) ) * cos( radians( geo_code_2) - radians('%s') ) + sin( radians('%s') ) * sin( radians(geo_code_1) ) ) ) AS distance FROM civicrm_address HAVING distance < '%s' ORDER BY distance LIMIT 0 , 20", $center_lat,$center_lng,$center_lat,$radius));

$rows = $db->loadObjectList(); 


foreach ($rows as $row) {   

       $cids[] = $row->contact_id;
     
}  


return $cids;
}
else{
$db->setQuery("SELECT id FROM civicrm_contact WHERE (contact_sub_type = 'Care_Home') OR (contact_sub_type = 'Home_Care') OR (contact_sub_type = 'Assisted_Living') OR (contact_sub_type = 'Retirement_village') OR (contact_sub_type = 'Living_Care')");

$rows = $db->loadObjectList(); 
foreach ($rows as $row) {   

$ncids[] = $row->id;  
}  
return $ncids;
}



And also in the list view page, I have the following code to calculate the distance. in default_row.php


Code:
        if(isset($_GET['advanced_search___lat'])) {
                     $center_lng = $_GET['advanced_search___lon'];
                     $center_lat =  $_GET['advanced_search___lat'];
//$center_lat = {advanced_search___lat};
//$center_lng = {advanced_search___lon};
                    $radius = 10;
                   
                    $db->setQuery("SELECT geo_code_1, geo_code_2 FROM civicrm_address WHERE contact_id = '$id'");
                    $res = $db->loadRow();
                    //print_r($res);
                    $lng = $res[0];
                   $lat = $res[1];

                 $db->setQuery(JText::sprintf("SELECT contact_id, ( 3959 * acos( cos( radians('%s') ) * cos( radians('$lng') ) * cos( radians('$lat') - radians('%s') ) + sin( radians('%s') ) * sin( radians('$lng') ) ) )
                              AS distance FROM civicrm_address", $center_lat,$center_lng,$center_lat));

                $rows = $db->loadObjectList(); 


                foreach ($rows as $row) {   

                    $distance = $row->distance;  

                }  
   

                echo $distance;
           
           
                }


Now what I need to do is order the list by distance. When I dump the first prefilter it provides the list according to the order but when the actual results come in they are according to the table id.


Can you guide me on the right path to order this list according to the distance? Please let me know if its not clear


Thanks alot
 
Hmmm. You'd have to insert the distance into the rendered data ($this->rows), then order that data yourself, before the main ...

Code:
foreach ($this->rows as $groupedBy => $group) :

... loop.

-- hugh
 
I've had to reread the thread to remind myself what you are doing.

The way you are inserting the distance atm is in default_row.php. But that won't allow you to affect the ordering, because that code is called after the ordering has already been done, in the main loop.

So you'd have to insert the distance and reorder the data before that point in the code.

I think the best place to do it would be in a list 'php_events' plugin, running your code 'onLoadData'.

Your list data will be in $args[0]->data, which will be an array of arrays.

Code:
$app = JFactory::getApplication();
$lat = $app->input->get('advanced_search___lat', '');
$lon = $app->input->get('advanced_search___lon', '');

if (!empty($lat) && !empty($lon)) {
   foreach ($args[0]->data as $group) {
      foreach ($group as $row) {
         // calculate the $distance here, and insert it into the empty 'distance' element ...
         $row->distance = $distance;
         $row->distance_raw = $distance;
      }
      usort($group, function($a, $b) {
         return $a->distance > $b->distance;
      });
   }
}

That's the basic shape of what you need. Next step is the "calculate $distance here". It will look something like the code you alreayd have in your default_row.php, but I'm not 100% sure what list you are actually displaying here. Is it civicrm_address, or is it some other table like 'contacts'?

-- hugh
 
Thanks alot. Regarding calculate distance here I can extract the Geolocation of the organization from the $row to calculate the distance.


Thanks again
 
Yup.

Paste your code here if you want, I'll sanity check it (some of the stuff in your last post was a bit strange, where you seem to be looking up every row in the table).

-- hugh
 
Yup.

Paste your code here if you want, I'll sanity check it (some of the stuff in your last post was a bit strange, where you seem to be looking up every row in the table).

-- hugh

I will do. Yeah, I changed that code coz it was taking so much time to load data. At the moment I have this in the prefilter which does the Job. I will try the method you suggested as well

Thanks again


Code:
if(isset($_GET['advanced_search___lat'])) {
   $center_lng = $_GET['advanced_search___lon'];
   $center_lat =  $_GET['advanced_search___lat'];
   $advanced_type = $_GET['advanced_search___send_type'];
   $radius = $_GET['advanced_search___radius'];

$db = JFactory::getDbo();
$db->setQuery(JText::sprintf("SELECT contact_id, ( 3959 * acos( cos( radians('%s') ) * cos( radians(     geo_code_1 ) ) * cos( radians( geo_code_2) - radians('%s') ) + sin( radians('%s') ) * sin( radians(geo_code_1) ) ) ) AS distance FROM civicrm_address HAVING distance <'%s'", $center_lat,$center_lng,$center_lat,$radius));

$results = $db->loadObjectList();
foreach ($results as $row) {  

             $dist = $row->distance;
             $id = $row->contact_id;

//Simple function to add distance to database
              InsertDistance($dist, $id);
 
You could probably make it more efficient by doing a single "INSERT INTO ... ON DUPLICATE KEY UPDATE ..." query for all the results, rather than looping round the results and doing a separate update for each one. As you are using the contact_id returned by your distance query, it would all be updates, you wouldn't be inserting any data.

The code to build the query would be a little more complex, but if you are updating a lot of rows, it would potentially be MUCH faster.

See the second answer on this page:

https://stackoverflow.com/questions...s-with-different-values-in-one-query/25674827

-- hugh
 
Although tbh I wouldn't write to the database at all. The problem with doing that is that if two people are accessing the list at the same time, user 1's prefilter runs, updates the table, user 2's prefilter runs, updates the table, user 1's display code runs and shows user 2's results.

That's why I suggested doing this all in the onLoadData hook, doing the distance query (again) there, inserting that distance directly into the in-memory data structure and sorting it. That way multiple users won't stomp on each other's data.

So you'd basically take the code you have for the distance query, run it before the foreach() loop in the code I gave you, and build an array of distance keyed by contact_id. Then in that code I gave you, use $yourDistances[$row->contact_id] as $distance.

-- hugh
 
Although tbh I wouldn't write to the database at all. The problem with doing that is that if two people are accessing the list at the same time, user 1's prefilter runs, updates the table, user 2's prefilter runs, updates the table, user 1's display code runs and shows user 2's results.

That's why I suggested doing this all in the onLoadData hook, doing the distance query (again) there, inserting that distance directly into the in-memory data structure and sorting it. That way multiple users won't stomp on each other's data.

So you'd basically take the code you have for the distance query, run it before the foreach() loop in the code I gave you, and build an array of distance keyed by contact_id. Then in that code I gave you, use $yourDistances[$row->contact_id] as $distance.

-- hugh

I was thinking about this scenario and I think it makes perfect sense NOT to write it to the DB. I will try to amend this as you suggested. I will post my findings later today

Thanks again
Bastiyan
 
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top